Calling stored procedures with supported databases / Stored procedure call with Oracle |
Oracle stored procedures or stored functions must be called with the input and output parameters specification in the USING clause of the EXECUTE, OPEN or FOREACH instruction. As in normal dynamic SQL, parameters must correspond by position, and the IN/OUT/INOUT options must match the parameter definition of the stored procedure.
PREPARE stmt FROM "begin proc1(?,?,?); end;"
Remark: Oracle stored procedures do not specify the size of number and character parameters. The size of output values (especially character strings) are defined by the calling context (i.e. the data type of the variable used when calling the procedure). When you pass a CHAR(10) to the procedure, the returning value will be filled with blanks to reach a size of 10 bytes.
Note that for technical reasons, the Oracle driver uses dynamic binding with OCIBindDynamic(). The Oracle Call Interface does not support stored procedures parameters with the CHAR data type when using dynamic binding. You must use VARCHAR2 instead of CHAR to define character string parameters for stored procedures.
MAIN DEFINE n INTEGER DEFINE d DECIMAL(6,2) DEFINE c VARCHAR(200) DATABASE test1 EXECUTE IMMEDIATE "create procedure proc1(" || " p1 in int," || " p2 in out number," || " p3 in out varchar2" || " )" || " is begin" || " p2:= p1 + 0.23;" || " p3:= 'Value = ' || to_char(p1);" || "end;" PREPARE stmt FROM "begin proc1(?,?,?); end;" LET n = 111 EXECUTE stmt USING n IN, d INOUT, c INOUT DISPLAY d DISPLAY c END MAIN