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