CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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, 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.

NOCOPY Performance Demo
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) 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
  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
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 / as sysdba

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;
 
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
Control Structures
DBMS_METADATA
DBMS_WARNINGS
Functions
Nested Loops
Packages
Pipelined Table Functions
Pragma Inline
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [154 users online]    © 2010 psoug.org