Help Center > > Developer Guide> Stored Procedures> Dynamic Statements> Dynamically Calling Stored Procedures

Dynamically Calling Stored Procedures

Updated at: Jul 15, 2020 GMT+08:00

This section describes how to dynamically call store procedures. You must use anonymous statement blocks to package stored procedures or statement blocks and append IN and OUT behind the EXECUTE IMMEDIATE...USING statement to input and output parameters.

Syntax

Figure 1 shows the syntax diagram.

Figure 1 call_procedure::=

Figure 2 shows the syntax diagram for using_clause.

Figure 2 using_clause::=

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 numbers of the placeholders and the parameters are the same.
  • 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)';
   --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;

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel