Oracle Stored Procedures Cursor Loop AUTHID INVOKER DEFINER NOCOPY CURRENT

Oracle Procedures

Version 11.1

General

Related Data Dictionary Objects

error$	source$
DBA	ALL	USER
dba_arguments	all_arguments	user_arguments
dba_errors	all_errors	user_errors
dba_object_size	all_object_size	user_object_size
dba_procedures	all_procedures	user_procedures
dba_source	all_source	user_source

System Privileges Related To Procedures

create procedure	alter any procedure
	create any procedure
	debug any procedure
	drop any procedure
	execute any procedure

Object Privileges

GRANT execute ON <procedure_name>;

Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID.

GRANT execute ON testproc TO uwclass;

Stored Procedure

No Parameters

CREATE OR REPLACE PROCEDURE <procedure_name> IS

BEGIN
<code here>
END <procedure_name>;
/

CREATE OR REPLACE PROCEDURE no_param IS

BEGIN
dbms_output.put_line('No Params');
END no_param;
/

set serveroutput on

exec no_param;

Single IN Parameter

CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> IN <data_type>)
IS

BEGIN
<code here>
END <procedure_name>;
/

CREATE OR REPLACE PROCEDURE in_param (mesg VARCHAR2) IS

BEGIN
dbms_output.put_line(mesg);
END in_param;
/

set serveroutput on

exec in_param('Single IN Parameter');

OUT Parameter

CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> OUT <data_type>)
IS

BEGIN
<code here>
END <procedure_name>;
/

CREATE OR REPLACE PROCEDURE out_param(mesg OUT VARCHAR2) IS

BEGIN
  mesg := 'Single OUT Parameter';
END out_param;
/

set serveroutput on

DECLARE
s VARCHAR2(50);
BEGIN
  out_param(s);
  dbms_output.put_line(s);
END;
/

CREATE OR REPLACE PROCEDURE out_param (mesg OUT VARCHAR2) IS

BEGIN
mesg := 'Single OUT Parameter';
END out_param;
/

SQL> var x VARCHAR2(30)
SQL> exec out_param(:x)
SQL> print x

IN OUT Parameter

CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> IN OUT <data_type>)
IS

BEGIN
<code here>
END <procedure_name>;
/

CREATE OR REPLACE PROCEDURE inout_param (mesg IN OUT VARCHAR2) IS

BEGIN
mesg := mesg || ' an IN OUT Parameter';
END inout_param;
/

set serveroutput on

DECLARE
s VARCHAR2(50) := 'This procedure uses';
BEGIN
inout_param(s);
dbms_output.put_line(s);
END;
/

Multiple Parameters

CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> IN <data_type>,
<parameter_name> OUT <data_type>,
<parameter_name> IN OUT <data_type>)
IS

BEGIN
<code here>
END <procedure_name>;
/

CREATE OR REPLACE PROCEDURE many_params (
mesg1 IN VARCHAR2,
mesg2 OUT VARCHAR2,
mesg3 IN OUT VARCHAR2) IS

BEGIN
mesg2 := mesg1 || 'Parameter As The OUT';
mesg3 := mesg3 || 'Returned';
END many_params;
/

set serveroutput on

DECLARE
iparm VARCHAR2(50) := 'This is the IN ';
oparm VARCHAR2(50);
ioparm VARCHAR2(50) := 'And This is the IN OUT ';
BEGIN
many_params(iparm, oparm, ioparm);
dbms_output.put_line(oparm || ' ' || ioparm);
END;
/

Parameter DEFAULT

Procedure Without Default

CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> <data_type> <default_value>) IS

BEGIN
<code>
END;
/

CREATE OR REPLACE PROCEDURE no_default(num_rows PLS_INTEGER) IS

BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < num_rows+1)
LOOP
dbms_output.put_line(r.object_name);
END LOOP;
END no_default;
/

set serveroutput on

exec no_default

exec no_default(5);

Procedure With Default

CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> <data_type> DEFAULT <default_value>) IS

BEGIN
<code>
END;
/

CREATE OR REPLACE PROCEDURE with_default (
num_rows PLS_INTEGER DEFAULT 20) IS

BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < num_rows+1)
LOOP
dbms_output.put_line(r.object_name);
END LOOP;
END with_default;
/

set serveroutput on

exec with_default

exec with_default(5);

Parameter Notations

Positional Notation

exec <procedure_name> (<parameter>,<parameter>);

CREATE OR REPLACE PROCEDURE positional (
min_nr PLS_INTEGER DEFAULT 100,
max_nr PLS_INTEGER DEFAULT 1000) IS

BEGIN
FOR r IN (SELECT table_name FROM user_tables
WHERE num_rows BETWEEN min_nr AND max_nr)
LOOP
dbms_output.put_line(r.table_name);
END LOOP;
END positional;
/

set serveroutput on

exec positional;

exec positional(1);

exec positional(1000, 99999);

Named Notation

exec <procedure_name> (<parameter_name> => <parameter>);

set serveroutput on

exec positional;

exec positional(min_nr => 1);

exec positional(max_nr => 500);

exec positional(max_nr => 1000);

exec positional(max_nr => 20000);

exec positional(max_nr => 99999);

Mixed Notation

exec <procedure_name> (<parameter>, <parameter_name> => <parameter>);

set serveroutput on

exec positional(10, max_nr => 1000);

Parameter NOCOPY

Note: NOCOPY is a hint ... not a directive ... and may be ignored.

NOCOPY Performance Demo

CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> NOCOPY <data_type> <default_value>) IS

BEGIN
<code>
END;
/

GRANT execute ON dbms_crypto TO uwclass;

CREATE OR REPLACE PROCEDURE default_out (retval OUT VARCHAR2) AS
BEGIN
retval := dbms_crypto.randombytes(32);
END default_out;
/

CREATE OR REPLACE PROCEDURE nocopy_out (retval OUT NOCOPY VARCHAR2) AS
BEGIN
retval := dbms_crypto.randombytes(32);
END nocopy_out;
/

CREATE OR REPLACE PROCEDURE nocopy_test (reps IN PLS_INTEGER) AS
bt1 TIMESTAMP(9);
et1 TIMESTAMP(9);

bt2 TIMESTAMP(9);
et2 TIMESTAMP(9);

outval VARCHAR2(64);
BEGIN
bt1 := SYSTIMESTAMP;
FOR i IN 1..reps LOOP
default_out(outval);
END LOOP;
et1 := SYSTIMESTAMP;

bt2 := SYSTIMESTAMP;
FOR i IN 1..reps LOOP
nocopy_out(outval);
END LOOP;
et2 := SYSTIMESTAMP;

dbms_output.put_line('Default: ' || TO_CHAR(et1-bt1));
dbms_output.put_line('No Copy: ' || TO_CHAR(et2-bt2));
END nocopy_test;
/

-- Note: Tried this with REF CURSORS returning 40K rows with, again,
-- no measurable difference. But now watch what happens when used in a
-- demo received from Tom Kyte on 2/9/2007.

CREATE OR REPLACE PROCEDURE p3(x IN OUT dbms_sql.varchar2s) AS
BEGIN
FOR i IN 1 .. 2000000 LOOP
    x(i) := RPAD('*', 255, '*');
END LOOP;
RAISE PROGRAM_ERROR;
END;
/

CREATE OR REPLACE PROCEDURE p4(x IN OUT NOCOPY dbms_sql.varchar2s) AS
BEGIN
FOR i IN 1 .. 2000000 LOOP
    x(i) := RPAD( '*', 255, '*' );
END LOOP;
RAISE PROGRAM_ERROR;
END;
/

set serveroutput on
set timing on

DECLARE
l_x dbms_sql.varchar2s;
BEGIN
FOR i IN 1 .. 1000000 LOOP
    l_x(i) := RPAD('*', 250, '*');
END LOOP;
p3(l_x);
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line('count = ' || l_x.count);
END;
/

DECLARE
l_x dbms_sql.varchar2s;
BEGIN
FOR i IN 1 .. 1000000 LOOP
    l_x(i) := RPAD( '*', 250, '*' );
END LOOP;
p4(l_x);
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line('count = ' || l_x.count);
END;
/

Note: If a subprogram exists with an unhandled exception, the value assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, the changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."

Error Handling With NOCOPY

CREATE OR REPLACE PROCEDURE raise_error (
p_Raise BOOLEAN,
p_ParameterA OUT NOCOPY NUMBER) AS

BEGIN
p_ParameterA := 7;
IF p_Raise THEN
    RAISE DUP_VAL_ON_INDEX;
ELSE
    RETURN;
END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN;
END raise_error;
/

set serveroutput on

DECLARE
p_B BOOLEAN := TRUE;
n NUMBER;
BEGIN
raise_error(p_B, n);
dbms_output.put_line(n);
END;
/

CREATE OR REPLACE PROCEDURE raise_error (
p_Raise BOOLEAN,
p_ParameterA IN OUT NOCOPY NUMBER) AS

BEGIN
p_ParameterA := n;
IF p_Raise THEN
    RAISE DUP_VAL_ON_INDEX;
ELSE
    RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
    RETURN;
END raise_error;
/

DECLARE
p_B BOOLEAN := FALSE;
n NUMBER := 100;
BEGIN
raise_error(p_B, n);
dbms_output.put_line(n);
END;
/

AUTHID

Note: For DEFINER RIGHTS objects execute privileges must be granted directly to the user; not to a role. With CURRENT USER rights can be granted to a role.

AUTHID Demo

AUTHID DEFINER (the default)
run the procedure with the rights of the procedure's owner.

AUTHID CURRENT_USER
run the procedure with the rights of the executing schema.

conn abc/abc

CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS
BEGIN
FOR rec IN (SELECT table_name FROM user_tables)
LOOP
    dbms_output.put_line(rec.table_name);
END LOOP;
END definer_test;
/

CREATE OR REPLACE PROCEDURE cu_test AUTHID CURRENT_USER IS
BEGIN
FOR rec IN (SELECT table_name FROM user_tables)
LOOP
    dbms_output.put_line(rec.table_name);
END LOOP;
END cu_test;
/

set serveroutput on

exec definer_test;

exec cu_test;

GRANT execute on definer_test TO abu;
GRANT execute on cu_test TO abu;

conn abu/abu

set serveroutput on

exec abc.definer_test;

exec abc.cu_test;

--======================================

conn abc/abc

CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS

BEGIN
FOR rec IN (SELECT srvr_id FROM servers)
LOOP
    dbms_output.put_line(rec.srvr_id);
END LOOP;
END definer_test;
/

CREATE OR REPLACE PROCEDURE cu_test AUTHID CURRENT_USER IS

BEGIN
FOR rec IN (SELECT srvr_id FROM servers)
LOOP
    dbms_output.put_line(rec.srvr_id);
END LOOP;
END cu_test;
/

set serveroutput on

exec definer_test;

exec cu_test;

GRANT execute on definer_test TO abu;
GRANT execute on cu_test TO abu;

conn abu/abu

set serveroutput on

exec abc.definer_test;

exec abc.cu_test;

Procedure Demos

Calculate Business Days

CREATE TABLE daterange (
beg_date DATE,
end_date DATE,
biz_days NUMBER(5));

INSERT INTO daterange VALUES (SYSDATE-10, SYSDATE+10, NULL);
INSERT INTO daterange VALUES (SYSDATE-17, SYSDATE+10, NULL);
INSERT INTO daterange VALUES (SYSDATE-22, SYSDATE+12, NULL);

CREATE OR REPLACE PROCEDURE bizdays AUTHID CURRENT_USER IS

CURSOR bd_cur IS
SELECT beg_date, end_date, 0
FROM daterange;

TYPE bDate_tab IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
bdate bDate_tab;

TYPE eDate_tab IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
edate eDate_tab;

TYPE bDay_tab IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
bDays bDay_tab;

NumDays PLS_INTEGER;
TestDate DATE;

BEGIN
OPEN bd_cur;
LOOP
    FETCH bd_cur BULK COLLECT INTO bDate, eDate, bDays LIMIT 100;
    EXIT WHEN bd_cur%NOTFOUND;

    FOR i IN bdate.FIRST .. bdate.LAST LOOP
      TestDate := bdate(i);
      NumDays := 0;

      FOR j IN 1 .. (edate(i) - bdate(i) + 1) LOOP
        IF TO_CHAR(TestDate, 'D') BETWEEN '2' AND '6' THEN
          NumDays := NumDays+1;
        END IF;

        TestDate := TestDate + 1;
      END LOOP;

      bDays(i) := NumDays;

      UPDATE daterange
      SET biz_days = bDays(i)
      WHERE beg_date = bDate(i)
      AND end_date = eDate(i);
    END LOOP;
END LOOP;
COMMIT;
CLOSE bd_cur;
END bizdays;
/

Related Topics

Anonymous Blocks

Autonomous Transactions

Bulk Collection & FORALL

Pipelined Table Functions

Pragma Inline