demonstrates using DBMS_SQL to execute CALL statement. : Call « Stored Procedure Function « Oracle PL / 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>

   
  








Related examples in the same category

1.CALL statement.
2.illegal procedure call
3.Using call statement in a dynamic statement is legal
4.EXECUTE IMMEDIATE dynamic statement and return value
5.This script demonstrates the use of the CALL statement.