Stored Procedures and Open SQL
Stored procedures and PL/SQL block can be directly executed on an
RWDBOSql object, just like an SQL statement. For cursor parameters, the proper binding type is
RWDBNativeType1. Here is an example:
myOpenSqlObject.statement(“BEGIN myStoredProc(:id, :cursor); END;”,
RWDBOSql::Procedure);
In this example,
myStoredProc is the name of the stored procedure,
:id and
:cursor are the placeholders for the parameters to be passed to the stored procedure, and
myOpenSqlObject is an
RWDBOSql instance. Note the hint provided as
Procedure.
RWDBOSql myOpenSqlObject("BEGIN :ret := myStoredFunc(:id, :cursor); END;",
RWDBOSql::Procedure);
In this example,
myStoredFunc is a stored function that returns a value. An additional placeholder,
:ret, is used to receive the return value as an
OUT parameter. Please see
“An Open SQL Stored Procedure Example” for an example that uses
RWDBOSql with stored procedures.
NOTE >> Stored procedure execution is not supported with the data callback classes.
Sequence of Fetching Result Sets and Out Parameters
Oracle stored procedures return result sets in the form of reference cursor parameters. Hence, there is no requirement regarding the sequence in which result sets and OUT parameters are fetched, as both are essentially OUT parameters. Method fetchReturnParams() could be called any time after the stored procedure execution to retrieve the OUT parameters. However, to make your application portable across different access modules, it is recommended to fetch OUT parameters after completely processing the result sets.