Oracle PL/SQL - Passing Variables without Copying

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

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.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   ww  w  .  j  a va 2 s .c o  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>
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>
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>
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.
SQL>

Related Topic