I created a procedure that recieves an integer and returns an Integer.
I'm trying to call the procedure vie stored procedure job and write the output into an Oracle table.
It keeps failing with the message of :'Second output column must be a Procedure return message'
What am I doing wrong ?
I have defined the 'Stored Procedure' as transform and gave each parameter and assigned target column.
I created a procedure that recieves an integer and returns an Integer.
I'm trying to call the procedure vie stored procedure job and write the output into an Oracle table.
It keeps failing with the message of :'Second output column must be a Procedure return message'
What am I doing wrong ?
I have defined the 'Stored Procedure' as transform and gave each parameter and assigned target column.
Any suggestions ?
Just to confirm are you actually using the stored procedure stage or have you written a job which gets data from a stored procedure.
Just to confirm are you actually using the stored procedure stage or have you written a job which gets data from a stored procedure.[/quote]
I'm Using a Stored procedure stage,
But if you have any suggestions on how to implement the stored procedure into a regular job and getting the necessary results that would help as well.
Well to be accurate, I'm not sure what exactly you are reffering to.
I open anew server job and drug'n'drop the stored procedure ICON from the pallete --> I create a transformation --> and a target table.
I map the procedure results( I think ) to the proper target column.
Hi Lando,
The error message said that 'Second output column must be a Procedure return message'. You did not have a "ProcMess" on the output column of your STP stage. Please read the STP pdf file that will save you a lot time trying to figure out how to use Oracle's stored procedures.
I created a procedure that recieves an integer and returns an Integer.
I'm trying to call the procedure vie stored procedure job and write the output into an Oracle table.
From what you say, this looks very much like a lookup. You supply the key, it returns a value. If so, why not create an Oracle function which you can then call from an OCI stage using a simple "select fn(:1) from dual" and use the OCI stage as a lookup.
If you must use a stored procedure for some reason, why can't you just do the insert within the stored procedure again using an OCI stage with a User-defined SQL.
jzparad wrote:
From what you say, this looks very much like a lookup. You supply the key, it returns a value. If so, why not create an Oracle function which you can then call from an OCI stage using a simple "select fn(:1) from dual" and use the OCI stage as a lookup.
Thanks,
I didn't think of that.
I altered the procedure to act like a function and it works. :D
