Oracle Stored Procedures
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Data Dictionary Objects
ARGUMENT$ CDB_OBJECT_SIZE ERROR$
ALL_ARGUMENTS CDB_PROCEDURES SOURCE$
ALL_ERRORS_AE CDB_SOURCE_AE USER_ARGUMENTS
ALL_OBJECT_SIZE DBA_ARGUMENTS USER_ERRORS_AE
ALL_PROCEDURES DBA_ERRORS_AE USER_OBJECT_SIZE
ALL_SOURCE_AE DBA_OBJECT_SIZE USER_PROCEDURES
CDB_ARGUMENTS DBA_PROCEDURES USER_SOURCE_AE
CDB_ERRORS_AE DBA_SOURCE_AE  
Object Privileges Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID.

GRANT execute ON <object_name> TO <user_name>;
conn uwclass/uwclass@orabase

CREATE OR REPLACE PROCEDURE test AUTHID DEFINER IS
BEGIN
  NULL;
END test;
/

GRANT execute ON testproc TO scott;
System Privileges
ALTER ANY PROCEDURE CREATE PROCEDURE DROP ANY PROCEDURE
CREATE ANY PROCEDURE DEBUG ANY PROCEDURE EXECUTE ANY PROCEDURE
Page Sections
ALTER PROCEDURE DROP PROCEDURE PARAMETER DEFAULTS
CREATE PROCEDURE AUTHID PARAMETER NOTATIONS
NOCOPY COMPILER HINT   Demos
 
Create 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 IN 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;
/
ROWTYPE% Parameter CREATE OR REPLACE PROCEDURE rtproc(rt_in IN servers%ROWTYPE) AUTHID DEFINER IS
BEGIN
  dbms_output.put_line('Server ID: '  || TO_CHAR(rt_in.srvr_id));
  dbms_output.put_line('Network ID: ' || TO_CHAR(rt_in.network_id));
END rtproc;
/

DECLARE
 srvr_rt servers%ROWTYPE;
BEGIN
  srvr_rt.srvr_id := 100;
  srvr_rt.network_id := 100;
  rtproc(srvr_rt);
END rowtype_proc;
/
User Defined Data Type desc servers

CREATE OR REPLACE TYPE
uw_sel_row AUTHID DEFINER AS OBJECT (
srvr_id NUMBER(10), netaddress VARCHAR2(15));
/

CREATE OR REPLACE PROCEDURE rtproc(rt_in IN uw_sel_row) AUTHID DEFINER IS
BEGIN
  dbms_output.put_line('Server ID:  ' || TO_CHAR(rt_in.srvr_id));
  dbms_output.put_line('Network ID: ' || TO_CHAR(rt_in.netaddress));
END rtproc;
/

DECLARE
 l_data uw_sel_row := uw_sel_row(NULL, NULL);
BEGIN
  l_data.srvr_id := 100;
  l_data.netaddress := '192.168.1.101';
  rtproc(l_data);
END rowtype_proc;
/
 
Parameter DEFAULTs
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_ae 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 Parameter Defaults 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 IN PLS_INTEGER DEFAULT 100,
max_nr IN 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, 500000);
Named Notation exec <procedure_name> (<parameter_name> => <parameter>);
exec positional;

exec positional(min_nr => 1);

exec positional(max_nr => 500);

exec positional(max_nr => 5000);

exec positional(max_nr => 10000);

exec positional(max_nr => 50000);

exec positional(max_nr => 999999);
Mixed Notation exec <procedure_name> (<parameter>, <parameter_name> => <parameter>);
exec positional(10, max_nr => 1000);

exec dbms_stats(USER, cascade=>TRUE);
 
Parameter NOCOPY
Note: NOCOPY is a hint ... not a directive ... and may be ignored CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> NOCOPY <data_type> <default_value>) IS
BEGIN
  <code>
END;
/
-- Note: This first demo shows an example of NOCOPY not working

conn / as sysdba

GRANT execute ON dbms_crypto TO uwclass;

conn uwclass/uwclass

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

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

CREATE OR REPLACE PROCEDURE nocopy_test (reps IN PLS_INTEGER) AUTHID DEFINER 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 default_io(x IN OUT dbms_sql.varchar2s) AUTHID DEFINER AS
BEGIN
  FOR i IN 1 .. 2000000 LOOP
    x(i) := RPAD('*', 255, '*');
  END LOOP;
  RAISE PROGRAM_ERROR;
END default_io;
/

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

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;
  default_io(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;
  nocopy_io(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)
AUTHID DEFINER IS
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)
AUTHID DEFINER AS
BEGIN
  IF p_Raise THEN
    RAISE DUP_VAL_ON_INDEX;
  ELSE
    p_ParameterA := 999;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END raise_error;
/

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

DECLARE
  p_B BOOLEAN := TRUE;
  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 DEFINER (default) stored procedures are executed in environment equivalent to the one you get after SET ROLE NONE. In other words, roles are disabled for PL/SQL and any privileges granted via roles do not apply unless you created the procedure with AUTHID CURRENT_USER, in which case role privileges do apply (but executing such procedures is a bit more expensive because Oracle has to evaluate the privileges on every call).
AUTHID Demo -- run the procedure with the rights of the procedure's owner.
AUTHID DEFINER (the default)

-- run the procedure with the rights of the executing schema.
AUTHID CURRENT_USER
conn dbadmin/dbadmin@oradev

CREATE USER abc
IDENTIFIED BY abc
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;

GRANT create session, create table, create procedure to abc;

conn abc/abc

CREATE TABLE t1 (
mycol VARCHAR2(20));

CREATE TABLE t2 (
yourcol NUMBER(10,2));

CREATE TABLE t3 (
ourcol DATE);

conn uwclass/uwclass

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 abc;
GRANT execute on cu_test TO abc;

conn abc/abc

set serveroutput on

exec uwclass.definer_test;

exec uwclass.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 uwclass;
GRANT execute on cu_test TO uwclass;

conn uwclass/uwclass

set serveroutput on

exec abc.definer_test;

exec abc.cu_test;
AUTHID Affect On Role Granted Object Rights /// NEED TO WRITE THIS Demo
 
ALTER Procedure
Compile ALTER PROCEDURE <procedure_name> COMPILE [REUSE SETTINGS];
ALTER PROCEDURE testproc COMPILE REUSE SETTINGS;
Compile Debug ALTER PROCEDURE <procedure_name> COMPILE DEBUG;
create procedure debugproc AUTHID DEFINER IS
BEGIN
  NULL;
END debugproc;
/

col plsql_debug format a30

SELECT plsql_debug
FROM dba_plsql_object_settings
WHERe name = 'DEBUGPROC';

ALTER PROCEDURE debugproc COMPILE DEBUG;

SELECT plsql_debug
FROM dba_plsql_object_settings
WHERe name = 'DEBUGPROC';
Editionable (new 12.1) ALTER PROCEDURE <procedure_name> <EDITIONABLE | NONEDITIONABLE>;
CREATE OR REPLACE NONEDITIONABLE PROCEDURE morgan AUTHID DEFINER IS
BEGIN
  NULL;
END morgan;
/

CREATE OR REPLACE EDITIONABLE PROCEDURE morgan AUTHID DEFINER IS
BEGIN
  NULL;
END morgan;
/
CREATE OR REPLACE PROCEDURE morgan AUTHID DEFINER IS
*
ERROR at line 1:
ORA_038824: A CREATE OR REPLACE comand may not change the EDITIONABLE property of an existing object.



ALTER PROCEDURE morgan EDITIONABLE;
 
Drop Procedure
Drop Procedure DROP PROCEDURE <procedure_name>;
DROP PROCEDURE testproc;
 
Procedure Demos
Calculate Business Days conn uwclass/uwclass@oradev

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
Accessible By Clause
ALTER SESSION DISABLE COMMIT IN PROCEDURE
Array Processing
Anonymous Blocks
Autonomous Transactions
Bulk Collection & FORALL
Control Structures
Cursor Loops
DBMS_METADATA
Exception Handling
Functions
Nested Loops
Packages
Pipelined Table Functions
PL/SQL Object Settings
PL/SQL_Warnings
Pragma Inline
Predefined Inquiry Directives

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved