Oracle PL/SQL/Stored Procedure Function/Call

CALL statement.

  
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>

demonstrates using DBMS_SQL to execute CALL statement.

  
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>

EXECUTE IMMEDIATE dynamic statement and return value

  
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>

illegal procedure call

  
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.

This script demonstrates the use of the CALL statement.

    
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>

Using call statement in a dynamic statement is legal

  
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>