This script demonstrates the use of the CALL statement. : Call « Stored Procedure Function « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Stored Procedure Function
  3. Call

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>








Related examples in the same category

1.CALL statement.
2.demonstrates using DBMS_SQL to execute CALL statement.
3.illegal procedure call
4.Using call statement in a dynamic statement is legal
5.EXECUTE IMMEDIATE dynamic statement and return value