Page 1 of 1
Calling Stored Procedure in OCI Stage
Posted: Mon Dec 05, 2005 1:30 pm
by edward_m
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.
Posted: Mon Dec 05, 2005 1:44 pm
by ray.wurlod
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.
Posted: Mon Dec 05, 2005 2:02 pm
by edward_m
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.
Posted: Mon Dec 05, 2005 6:38 pm
by trokosz
If you have DS 7.5 and above use the Stored Procedure Stage or you can write a Routine. I don't believe the OCI Stage supports.
Posted: Mon Dec 05, 2005 6:48 pm
by chulett
It 'supports' and you don't need to import the metadata in order to call from the before or after sql tab.
Try it without the trailing semi-colons.
Posted: Tue Dec 06, 2005 8:22 am
by edward_m
Craig,
I tried without trailing semicolons,still its giving ORA-06576: not a valid function or procedure name error.
Could u suggest me how to call stored procedure in a routine,i am using Datastage 5.2 on windows server.
Thanks.
Posted: Tue Dec 06, 2005 8:26 am
by chulett
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.
Posted: Tue Dec 06, 2005 8:31 am
by edward_m
Thanks for your quick response.
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.
How do i get BCI..
Thanks.
Posted: Tue Dec 06, 2005 8:32 am
by nvkuriseti
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
Posted: Tue Dec 06, 2005 9:10 am
by edward_m
Hi Venkat,
I did pass dummy argument to mu SP and i am able to call this Stored procedure in my DS job.
Thanks a lot Venkat.
Thanks for All replies.