Oracle PL/SQL - Passing Variables without Copying


Passing records as parameters is too big in term of memory usage.

You can pass parameter by using NOCOPY.

You are only passing a pointer to the variable rather than copying the values, increasing performance, and decreasing memory usage.

NOCOPY is particularly useful when passing record variables.

The following code shows how you can pass variables without copying them.


SQL> drop table emp;

Table dropped.--  w w  w  .ja  v  a  2 s  .  co  m

SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.

SQL> insert into emp values(7369, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> create or replace procedure p_generateNewEmp(io_emp in out nocopy emp%ROWTYPE)
  2   is
  3  begin
  4         select max(empNo)+1
  5           into io_emp.empNo
  6           from emp;
  7         io_emp.eName:='Emp#'||io_emp.empNo;
  8  end;
  9  /

Procedure created.

SQL> declare
  2        v_emp_rec emp%ROWTYPE;
  3  begin          --13
  4        v_emp_rec.deptNo:=10;
  5        p_generateNewEmp(v_emp_rec);
  6        DBMS_OUTPUT.put_line('Generated:'||v_emp_rec.empNo||' '||v_emp_rec.eName);
  7  end;
  8  /
Generated:7699 Emp#7699

PL/SQL procedure successfully completed.

Related Topic