Dynamically Calling Stored Procedures

Syntax

Figure 1 shows the syntax diagram.

Figure 1 call_procedure::=

Figure 2 shows the syntax diagram for using_clause.

Figure 2 using_clause-3

The above syntax diagram is explained as follows:

• CALL procedure_name: calls the stored procedure.
• [:placeholder1,:placeholder2, ...]: specifies the placeholder list of the stored procedure parameters. The number of placeholders is the same as the number of parameters. A placeholder name starts with a colon (:) or dollar sign ($). The colon (:) can be followed by digits, characters, or character strings (excluding digits, characters, or character strings with quotation marks). The dollar sign ($) can be followed only by digits. A placeholder can correspond to only one bind_argument in the USING clause.
• USING [IN|OUT|IN OUT]bind_argument: specifies where the variable passed to the stored procedure parameter value is stored. The modifiers in front of bind_argument and of the corresponding parameter are the same.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

--Create the stored procedure proc_add.
CREATE OR REPLACE PROCEDURE proc_add
(
    param1    in   INTEGER,
    param2    out  INTEGER,
    param3    in   INTEGER
)
AS
BEGIN
   param2:= param1 + param3;
END;
/

DECLARE
    input1 INTEGER:=1;
    input2 INTEGER:=2;
    statement  VARCHAR2(200);
    param2     INTEGER;
BEGIN
   --Declare the call statement.
    statement := 'call proc_add(:col_1, :col_2, :col_3)';(or statement := 'call proc_add($1, $2, $3)';)
   --Execute the statement.
    EXECUTE IMMEDIATE statement
        USING IN input1, OUT param2, IN input2;
    dbms_output.put_line('result is: '||to_char(param2));
END;
/

--Delete the stored procedure.
DROP PROCEDURE proc_add;