SYMPTOM:
It is common to have stored procedures with output parameters, frequently these parameters are used for error handling purposes and have information on why a particular procedure failed. An example of a stored procedure with a return parameter is shown below:
create or replace PROCEDURE emp_search
(pmtr1 IN INT, err OUT INT, cResults IN OUT types.cursorType) AS
Begin
.
.
.
End;
In the example shown above the procedure has an input parameter, an output parameter and the cursor that will point to the result set. Information on how to use a stored procedure can be found in
KB14959 .
A call to the procedure without providing an output parameter will generate an error stating that the procedure could not be found or that an incorrect number of arguments were provided:
PLS-00306: wrong number or types of arguments in call to 'EMP_SEARCH'
CAUSE:From the Strategy perspective there is no practical use for the output parameters. Strategy does not have error handling routines nor it will expect error parameters from a procedure. The error shown above was not caused by Strategy, it indicates that the syntax used to call the procedure is incorrect.
ACTION:In the example shown above the syntax is incorrect as the call executed did not include the full list of necessary parameters. The output parameter needs a placeholder. which can be created by adding an extra variable to the same package in which the cursor resides, as shown below:
create or replace PACKAGE types AS
TYPE cursorType IS REF CURSOR;
errorx INT;
END;
The "errorx" variable can now be used in the procedure call to act as a placeholder for the return parameters. An example call to the procedure is shown below:
{call emp_search(5, types.errorx)} Strategy Technical Support does not provide customized SQL queries. The syntax and the error messages shown in this document can vary depending on the database. Please contact your database administrator to obtain the correct syntax to call a stored procedure using an ODBC driver.
