Oracle PL/SQL/Stored Procedure Function/Parameter OUT

Behavior of OUT variables and raised exceptions

   
SQL> CREATE OR REPLACE PROCEDURE RaiseError (p_Raise IN BOOLEAN,pA OUT NUMBER) AS
  2   BEGIN
  3     pA := 7;
  4
  5     IF p_Raise THEN
  6       RAISE DUP_VAL_ON_INDEX;
  7     ELSE
  8       RETURN;
  9     END IF;
 10   END RaiseError;
 11   /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> set serveroutput on
SQL>
SQL> 
SQL> DECLARE
  2     v_Num NUMBER := 1;
  3   BEGIN
  4     DBMS_OUTPUT.PUT_LINE("Value before first call: " || v_Num);
  5     RaiseError(FALSE, v_Num);
  6     DBMS_OUTPUT.PUT_LINE("Value after successful call: " || v_Num);
  7     DBMS_OUTPUT.PUT_LINE("");
  8
  9     v_Num := 2;
 10     DBMS_OUTPUT.PUT_LINE("Value before second call: " || v_Num);
 11     RaiseError(TRUE, v_Num);
 12   EXCEPTION
 13     WHEN OTHERS THEN
 14        DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " || v_Num);
 15   END;
 16   /
Value before first call: 1
Value after successful call: 7
Value before second call: 2
Value after unsuccessful call: 2
PL/SQL procedure successfully completed.
SQL>

behavior of unhandled exceptions and OUT variables.

    
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
  2    p_Raise IN BOOLEAN,
  3    p_ParameterA OUT NUMBER) AS
  4  BEGIN
  5    p_ParameterA := 7;
  6
  7    IF p_Raise THEN
  8      RAISE DUP_VAL_ON_INDEX;
  9    ELSE
 10      RETURN;
 11    END IF;
 12  END RaiseError;
 13  /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> DECLARE
  2    v_TempVar NUMBER := 1;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("Initial value: " || v_TempVar);
  5    RaiseError(FALSE, v_TempVar);
  6    DBMS_OUTPUT.PUT_LINE("Value after successful call: " ||
  7                         v_TempVar);
  8
  9    v_TempVar := 2;
 10    DBMS_OUTPUT.PUT_LINE("Value before 2nd call: " || v_TempVar);
 11    RaiseError(TRUE, v_TempVar);
 12  EXCEPTION
 13    WHEN OTHERS THEN
 14      DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " ||
 15                           v_TempVar);
 16  END;
 17  /
Initial value: 1
Value after successful call: 7
Value before 2nd call: 2
Value after unsuccessful call: 2
PL/SQL procedure successfully completed.
SQL>
SQL>

Define "out" parameters

   
SQL>
SQL> create or replace
  2  procedure swap( p_parm1 in out number, p_parm2 in out number ) as
  3  --
  4    l_temp number;
  5  begin
  6    l_temp := p_parm1;
  7    p_parm1 := p_parm2;
  8    p_parm2 := l_temp;
  9  end swap;
 10  /
SQL>
SQL>
SQL>

Out parameter is assignable

    
SQL>
SQL>
SQL> CREATE TABLE myTable3 (
  2      a INTEGER,
  3      b INTEGER
  4  );
Table created.
SQL>
SQL> CREATE or replace PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)
  2  AS
  3  BEGIN
  4      b := 4;
  5      INSERT INTO myTable3 VALUES(a, b);
  6  END;
  7  /
Procedure created.
SQL>
SQL> DECLARE
  2      v NUMBER;
  3  BEGIN
  4      addtuple3(10, v);
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myTable3;
Table dropped.

Out with NOCOPY modifier.

    
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
  2    p_Raise IN BOOLEAN,
  3    p_ParameterA OUT NOCOPY NUMBER) AS
  4  BEGIN
  5    p_ParameterA := 7;
  6    IF p_Raise THEN
  7      RAISE DUP_VAL_ON_INDEX;
  8    ELSE
  9      RETURN;
 10    END IF;
 11  END RaiseError;
 12  /
Procedure created.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    v_TempVar NUMBER := 1;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("Initial value: " || v_TempVar);
  5    RaiseError(FALSE, v_TempVar);
  6    DBMS_OUTPUT.PUT_LINE("Value after successful call: " ||
  7                         v_TempVar);
  8
  9    v_TempVar := 2;
 10    DBMS_OUTPUT.PUT_LINE("Value before 2nd call: " || v_TempVar);
 11    RaiseError(TRUE, v_TempVar);
 12  EXCEPTION
 13    WHEN OTHERS THEN
 14      DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " ||
 15                           v_TempVar);
 16  END;
 17  /
Initial value: 1
Value after successful call: 7
Value before 2nd call: 2
Value after unsuccessful call: 7
PL/SQL procedure successfully completed.
SQL>
SQL>

Parameter Modes

    
SQL> CREATE OR REPLACE PROCEDURE ModeTest (
  2    p_InParameter    IN NUMBER,
  3    p_OutParameter   OUT NUMBER,
  4    p_InOutParameter IN OUT NUMBER) IS
  5
  6    v_LocalVariable  NUMBER;
  7  BEGIN
  8    v_LocalVariable := p_InParameter;  -- Legal
  9
 10    p_OutParameter := 7;  -- Legal
 11
 12    v_LocalVariable := p_InOutParameter;  -- Legal
 13
 14    p_InOutParameter := 7;  -- Legal
 15  END ModeTest;
 16  /
Procedure created.
SQL>
SQL>

This procedure takes a single OUT. Out parameter is assignable

    
SQL> CREATE OR REPLACE PROCEDURE ModeOut (p_Out OUT NUMBER) AS
  2     v_LocalVariable NUMBER := 0;
  3   BEGIN
  4     IF (p_Out IS NULL) THEN
  5       DBMS_OUTPUT.PUT_LINE("p_Out is NULL");
  6     ELSE
  7       DBMS_OUTPUT.PUT_LINE("p_Out = " || p_Out);
  8     END IF;
  9
 10     p_Out := 7;
 11
 12     v_LocalVariable := p_Out;
 13
 14      DBMS_OUTPUT.PUT("At end of ModeOut: ");
 15     IF (p_Out IS NULL) THEN
 16       DBMS_OUTPUT.PUT_LINE("p_Out is NULL");
 17     ELSE
 18       DBMS_OUTPUT.PUT_LINE("p_Out = " || p_Out);
 19     END IF;
 20   END ModeOut;
 21   /
Procedure created.
SQL>
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> DECLARE
  2     v_Out NUMBER := 1;
  3   BEGIN
  4     
  5     DBMS_OUTPUT.PUT_LINE("Before calling ModeOut, v_Out = " || v_Out);
  6     ModeOut(v_Out);
  7     DBMS_OUTPUT.PUT_LINE("After calling ModeOut, v_Out = " || v_Out);
  8   END;
  9   /
Before calling ModeOut, v_Out = 1
p_Out is NULL
At end of ModeOut: p_Out = 7
After calling ModeOut, v_Out = 7
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Unhandled exceptions and OUT variables

    
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
  2    p_Raise IN BOOLEAN,
  3    p_ParameterA OUT NUMBER) AS
  4  BEGIN
  5    p_ParameterA := 7;
  6
  7    IF p_Raise THEN
  8      RAISE DUP_VAL_ON_INDEX;
  9    ELSE
 10      RETURN;
 11    END IF;
 12  END RaiseError;
 13  /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> DECLARE
  2    v_TempVar NUMBER := 1;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("Initial value: " || v_TempVar);
  5    RaiseError(FALSE, v_TempVar);
  6    DBMS_OUTPUT.PUT_LINE("Value after successful call: " || v_TempVar);
  7
  8    v_TempVar := 2;
  9    DBMS_OUTPUT.PUT_LINE("Value before 2nd call: " || v_TempVar);
 10    RaiseError(TRUE, v_TempVar);
 11  EXCEPTION
 12    WHEN OTHERS THEN
 13      DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " || v_TempVar);
 14  END;
 15  /
Initial value: 1
Value after successful call: 7
Value before 2nd call: 2
Value after unsuccessful call: 2
PL/SQL procedure successfully completed.
SQL>
SQL>

Use out parameter to get value out

    
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH         CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN         SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD          SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES         MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN        SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE         MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK         MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT         ANALYST         7566 09-DEC-82       3000                    20
      7839 KING          PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER        SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS         CLERK           7788 12-JAN-83       1100                    20
     EMPNO Employee Name JOB              MGR HIREDATE      Salary       COMM     DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES         CLERK           7698 03-DEC-81        950                    30
      7902 FORD          ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER        CLERK           7782 23-JAN-82       1300                    10
14 rows selected.
SQL>
SQL>
SQL> create or replace procedure emp_lookup(
  2    p_empno in     number,
  3    o_ename    out emp.ename%type,
  4    o_sal      out emp.sal%type ) as
  5  begin
  6    select ename, sal
  7      into o_ename, o_sal
  8      from emp
  9        where empno = p_empno;
 10  exception
 11    when NO_DATA_FOUND then
 12      o_ename := "NULL";
 13      o_sal := -1;
 14    end emp_lookup;
 15  /
Procedure created.
SQL>
SQL> set serverout on
SQL>
SQL> declare
  2   l_ename emp.ename%type;
  3   l_sal emp.sal%type;
  4  begin
  5   emp_lookup( 7782, l_ename, l_sal );
  6   dbms_output.put_line( "Ename = " || l_ename );
  7   dbms_output.put_line( "Sal = " || l_sal );
  8  end;
  9  /
Ename = CLARK
Sal = 2450
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.

Using out parameter

    
SQL>
SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
  2    RETURN VARCHAR2 AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE("CallFunc called with " || p1);
  5    RETURN p1;
  6  END CallFunc;
  7  /
Function created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2    myResult VARCHAR2(50);
  3  BEGIN
  4    EXECUTE IMMEDIATE "CALL CallFunc(""Hello from PL/SQL"") INTO :myResult"
  5    USING OUT myResult;
  6  END;
  7  /
CallFunc called with Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Using Output Parameters

    
SQL>
SQL> CREATE TABLE emp (
  2     empID INT NOT NULL PRIMARY KEY,
  3     Name      VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor");
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE GetempName(i_empID IN INT,o_empName OUT VARCHAR)
  2  AS
  3  BEGIN
  4     SELECT Name INTO o_empName FROM emp
  5     WHERE empID = i_empID;
  6  END;
  7  /
SP2-0804: Procedure created with compilation warnings
SQL> SET SERVEROUT ON
SQL> DECLARE
  2     empName VARCHAR(50);
  3  BEGIN
  4     GetempName(3, empName);
  5     dbms_output.put_line(empName);
  6  END;
  7  /
Mary
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>