| 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;
/ |
