Oracle PL/SQL/Stored Procedure Function/Call

CALL statement.

   <source lang="sql">
 

SQL> SQL> CREATE OR REPLACE PROCEDURE CallProc2(p1 IN OUT VARCHAR2) AS

 2  BEGIN
 3    DBMS_OUTPUT.PUT_LINE("CallProc2 called with " || p1);
 4    p1 := p1 || " returned!";
 5  END CallProc2;
 6  /

Procedure created. SQL> SQL> set serveroutput on SQL> SQL> CALL CallProc2(:v_Output); CallProc2 called with Hello! Call completed. SQL> PRINT v_Output V_OUTPUT

---

Hello! returned! SQL> SQL> SQL>

 </source>
   
  

demonstrates using DBMS_SQL to execute CALL statement.

   <source lang="sql">
 

SQL> SQL> CREATE TABLE MyTable (

 2    num_col    NUMBER,
 3    char_col   VARCHAR2(60)
 4    );

Table created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE DynamicCall AS

 2    PROCEDURE DoInsert(p_NumCol IN MyTable.num_col%TYPE,
 3                       p_CharCol IN MyTable.char_col%TYPE);
 4
 5    PROCEDURE Go;
 6  END DynamicCall;
 7  /

Package created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY DynamicCall AS

 2    PROCEDURE DoInsert(p_NumCol IN MyTable.num_col%TYPE,
 3                       p_CharCol IN MyTable.char_col%TYPE) IS
 4    BEGIN
 5      INSERT INTO MyTable (num_col, char_col)
 6        VALUES (p_NumCol, p_CharCol);
 7    END DoInsert;
 8
 9    PROCEDURE Go IS
10      v_CallStmt VARCHAR2(100);
11      v_CursorID INTEGER;
12      v_NumCol MyTable.num_col%TYPE;
13      v_CharCol MyTable.char_col%TYPE;
14      v_Dummy INTEGER;
15    BEGIN
16      -- Open the cursor
17      v_CursorID := DBMS_SQL.OPEN_CURSOR;
18
19      v_CallStmt := "CALL DynamicCall.DoInsert(:num_col, :char_col)";
20      DBMS_SQL.PARSE(v_CursorID, v_CallStmt, DBMS_SQL.NATIVE);
21
22      v_NumCol := 123;
23      v_CharCol := "I like CALL statements!";
24      DBMS_SQL.BIND_VARIABLE(v_CursorID, ":num_col", v_NumCol);
25      DBMS_SQL.BIND_VARIABLE(v_CursorID, ":char_col", v_CharCol);
26      v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
27      DBMS_SQL.CLOSE_CURSOR(v_CursorID);
28  EXCEPTION
29    WHEN OTHERS THEN
30      -- Close the cursor, then raise the error again.
31      DBMS_SQL.CLOSE_CURSOR(v_CursorID);
32      RAISE;
33
34    END Go;
35  END DynamicCall;
36  /

Package body created. SQL> show errors No errors. SQL> SQL> SQL> drop table mytable; Table dropped. SQL>

 </source>
   
  

EXECUTE IMMEDIATE dynamic statement and return value

   <source lang="sql">
 

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> DECLARE

 2    myResult VARCHAR2(50);
 3  BEGIN
 4    EXECUTE IMMEDIATE
 5      "CALL CallFunc(""Hello from PL/SQL"") INTO :myResult"
 6      USING OUT myResult;
 7  END;
 8  /

CallFunc called with Hello from PL/SQL PL/SQL procedure successfully completed. SQL> SQL>

 </source>
   
  

illegal procedure call

   <source lang="sql">
 

SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS

 2  BEGIN
 3    DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
 4  END CallProc1;
 5  /

Procedure created. SQL> BEGIN

 2    CALL CallProc1();
 3  END;
 4  /
 CALL CallProc1();
      *

ERROR at line 2: ORA-06550: line 2, column 8: PLS-00103: Encountered the symbol "CALLPROC1" when expecting one of the following:

= . ( @ % ;

The symbol ":=" was substituted for "CALLPROC1" to continue.

 </source>
   
  

This script demonstrates the use of the CALL statement.

   <source lang="sql">
   

SQL> SQL> CREATE OR REPLACE PROCEDURE myProcedure(p1 IN VARCHAR2 := NULL) AS

 2   BEGIN
 3     DBMS_OUTPUT.PUT_LINE("myProcedure called with " || p1);
 4   END myProcedure;
 5   /

Procedure created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE myProcedure2(p1 IN OUT VARCHAR2) AS

 2   BEGIN
 3     DBMS_OUTPUT.PUT_LINE("myProcedure2 called with " || p1);
 4     p1 := p1 || " returned!";
 5   END myProcedure2;
 6   /

Procedure created. SQL> 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> SQL> set serveroutput on SQL> SQL> -- Some valid calls direct from SQL. SQL> CALL myProcedure("Hello!"); myProcedure called with Hello! Call completed. SQL> SQL> Call completed.

 2

SQL> CALL myProcedure(); myProcedure called with Call completed. SQL> SQL> Call completed.

 2

SQL> VARIABLE v_Output VARCHAR2(50); SQL> SQL> SQL> Call completed.

 2

SQL> PRINT v_Output V_OUTPUT

---

SQL> SQL> CALL myProcedure2(:v_Output); myProcedure2 called with Call completed. SQL> SQL> Call completed.

 2

SQL> PRINT v_Output V_OUTPUT

---

returned!

SQL> SQL>

 </source>
   
  

Using call statement in a dynamic statement is legal

   <source lang="sql">
 

SQL> SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS

 2  BEGIN
 3    DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
 4  END CallProc1;
 5  /

Procedure created. SQL> SQL> DECLARE

 2    myResult VARCHAR2(50);
 3  BEGIN
 4    EXECUTE IMMEDIATE "CALL CallProc1(""Hello from PL/SQL"")";
 5  END;
 6  /

CallProc1 called with Hello from PL/SQL PL/SQL procedure successfully completed. SQL> SQL>

 </source>