Calling Stored Procedure in OCI Stage

[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.

[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.

[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.

[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.

[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.

[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.

[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.

[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.

[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

[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.