Hi All,
We are using a STP stage to call a pl/sql function which requires multiple input parameters. My requirement is to pass same input column to two of the parameters in the function. But when I map same column in the STP stage to those two parameters the job fails with error message
"Stored_Procedure_6: ORA-01008: not all variables bound".
Is there a way I can pass the same input column to two different pl/sql function parameters?
Any help will be appreciated.
Regards
Vijay
Unable to pass same column value to two params in STP stage
Moderators: chulett, rschirm, roy
Hi Craig,
When I split the Input column with a different name and pass it to the STP stage, the function works fine(So essentially I would be duplicating the input column with a different name). Since the column names are different the function in the STP stage is accepting the parameters.
The issue comes up when we try to pass same column name to 2 different parameters in the STP stage function. The function works fine when we run it in our oracle database after hard coding the input value.
I will try to post the job design that we had developed for this.
Regards
Vijay
When I split the Input column with a different name and pass it to the STP stage, the function works fine(So essentially I would be duplicating the input column with a different name). Since the column names are different the function in the STP stage is accepting the parameters.
The issue comes up when we try to pass same column name to 2 different parameters in the STP stage function. The function works fine when we run it in our oracle database after hard coding the input value.
I will try to post the job design that we had developed for this.
Regards
Vijay
Specifically wondering about the syntax in the SP stage, the anonymous block it generates and what parameter markers are being used.
Don't recall, but if that stage uses the Oracle numbered parameter markers, you should be able to use the same column more than once easily. If it uses the '?' positional parameters, then you need to have as many columns as question marks and thus would need to split / repeat your column in the stage for them to line up and bind properly in the stage.
Don't recall, but if that stage uses the Oracle numbered parameter markers, you should be able to use the same column more than once easily. If it uses the '?' positional parameters, then you need to have as many columns as question marks and thus would need to split / repeat your column in the stage for them to line up and bind properly in the stage.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Craig,
Thank you for the hint. By default it is giving the syntax as below in the STP stage.
BEGIN :3 := exch_test(:1, :2); END;
It had the Generate Procedure Call tab checked which is by default. When I uncheck it and modify the syntax as below it worked like a charm.
BEGIN :3 := exch_test(:1, :1); END;
Thanks for your help.
Vijay
Thank you for the hint. By default it is giving the syntax as below in the STP stage.
BEGIN :3 := exch_test(:1, :2); END;
It had the Generate Procedure Call tab checked which is by default. When I uncheck it and modify the syntax as below it worked like a charm.
BEGIN :3 := exch_test(:1, :1); END;
Thanks for your help.
Vijay