Calling Stored Procedure in OCI Stage
Moderators: chulett, rschirm, roy
Calling Stored Procedure in OCI Stage
ALL,
I tried to call my oracle stored procedure in before SQL in OCI stage by using the following syntax
CALL <procedure name> ;;
but its giving error saying ORA-06576: not a valid function or procedure name.
Is there any other way to call stored procedure in oracle.?
Thanks.
I tried to call my oracle stored procedure in before SQL in OCI stage by using the following syntax
CALL <procedure name> ;;
but its giving error saying ORA-06576: not a valid function or procedure name.
Is there any other way to call stored procedure in oracle.?
Thanks.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Before you can call a stored procedure from DataStage you must first import the stored procedure definition (so that arguments may be checked).
Verify the syntax for CALL - sometimes curly braces are required.
Verify the syntax for CALL - sometimes curly braces are required.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I did try to import this stored procedure its saying that stored procedure has failed to report a result set (no parameters for this procedure).
I got the following error.
DSR.MetaGeta(GET.PROC.BY.EXEC.COLUMNS)(SQLExecDirect( '{CALL "TRUNCATE_SUBSCRIBER_IMP"}' )): BCI Error:
SQLSTATE=S1000,CODE=6550,[DataStage][SQL Client][ODBC][INTERSOLV][ODBC Oracle 8 driver][Oracle 8]ORA-06550: line 1, column 8:
PLS-00201: identifier 'TRUNCATE_SUBSCRIBER_IMP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored.
Note: this procedure works perfectly in sql prompt.
I got the following error.
DSR.MetaGeta(GET.PROC.BY.EXEC.COLUMNS)(SQLExecDirect( '{CALL "TRUNCATE_SUBSCRIBER_IMP"}' )): BCI Error:
SQLSTATE=S1000,CODE=6550,[DataStage][SQL Client][ODBC][INTERSOLV][ODBC Oracle 8 driver][Oracle 8]ORA-06550: line 1, column 8:
PLS-00201: identifier 'TRUNCATE_SUBSCRIBER_IMP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored.
Note: this procedure works perfectly in sql prompt.
To be honest, I didn't notice the version number. I know the 'call' syntax works in 7.x but perhaps it doesn't in that version.
Verify the format by searching the forum, there should be several conversations on the subject from what I recall.
You don't want to go down the routine route. You'd have to use BCI and that only would work for 30 days before forcing you to pay for a copy of the drivers, as they are only licensed for use from inside a job.
Verify the format by searching the forum, there should be several conversations on the subject from what I recall.
You don't want to go down the routine route. You'd have to use BCI and that only would work for 30 days before forcing you to pay for a copy of the drivers, as they are only licensed for use from inside a job.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 38
- Joined: Sat Jan 17, 2004 12:29 am
- Location: Hamburg, Germany
Hey,
I worked with STORED PROCEDURE by using Argument(s). In fact, I don't need argument in my STORED PROCEDURE, but I got the same error so that tried and worked successfully.
Example: OCI Stage --> Output "tab" --> SQL "tab" --> Before "tab" --> CALL SP_ONE(DUMMY_ARG)
Hope this will work.
Regards,
Venkat
I worked with STORED PROCEDURE by using Argument(s). In fact, I don't need argument in my STORED PROCEDURE, but I got the same error so that tried and worked successfully.
Example: OCI Stage --> Output "tab" --> SQL "tab" --> Before "tab" --> CALL SP_ONE(DUMMY_ARG)
Hope this will work.
Regards,
Venkat