Call Stored Procedure from OCI Stage - DS v7.0 (server jobs)

[nandu]

hi,
I'm working on Datastage v7.0 and want to call a stored procedure from OCI Stage.

some background:

- DataStage 7.0
- Oracle 9i
- Unix Server
- Use Native driver for Oracle and there is no ODBC connection
- I don't have knowledge on unix scripts and routines.


I have gone through the forum and tried to call the procedure using OCI stage as follows:

1. in the user defined query i called the procedure as :

call prc_get_batch_gen()

doesn't work..

2. called as :

call prc_get_batch_details(variable name)

doesn't work..

can somebody tell us the steps required for calling stored procedure using OCI stage.

the other doubts are:
1. do i need to define the metadata of all the columns that are used in the stored procedure.
2. i donot want any data getting extracted into datastage as stored procedure takes input values internally from another table and outputs the value into a target table ...everything happens within the database.
so should i extract a dummy row and pass it on to say a sequential file..so that datastage is satisfied.

Thanks ,

Nandu

[kcbland]

If your stored procedure is a "do something" procedure, as opposed to a "process with streaming data" procedure, then you're better off using a shell command to execute sqlplus.

If you don't want that route, you will have to create a job like OCI --> XFM --> SEQ that will return at least a single row with 1 column. You can map it to a junkfile, but you will need metadata and can import it from the stored procedure. This seems kind of hokey but that's because the stored procedure is meant to be used as the streaming data kind, not the "do something" kind.

The best solution is to execute sqlplus running the stored procedure and checking the exit code. You can do this with either a Batch job with DS BASIC logic, or a Sequencer Command stage (but that has issues with job parameters in the path to the command). This accomplishes what you want in the most seamless manner.

[trobinson]

We use it as a "do something" OCI stage on an output link. The next link constraint interrogates the OCISPlink.REJECTEDCODE to see if the stored procedure did the "do something" without an error. If not, follow the error link. We encapsulate units of database work in this way that would be too hairy to do in DataStage. I should mention it is a real-time app and I would seriously question doing it as "streaming" design as Mr. Bland alluded to because who wants to incur the overhead of invoking a stored procedure 6 million times, once per row?
100,000 times per day as we do is more manageable. We use this methodology to slam MQ Series XML into our ODS at a peak rate of a whopping 12 rows per second. Luckily the peak lasts for maybe ten minutes in the morning when we receive a bunch of messages in batch mode and the rest of the day in real-time.

Here's the SQL format for the procedure call that goes in the User Defined OCI stage;
call <STORED_PROCEDURENAME>(:1,:2,:3,:4,:5)

The 1-5 parms are the column definitions that act as INPUT paramaters to the stored procedure. You CANNOT get any values other than the REJECTEDCODE returned. If you want values returned, you'll have to use a function instead which is also very doable.

It may be the case that you have NO INPUT parms. Well, you gonna need at least one for the link to hang its hat on otherwise it will not work. It can be a dummy placeholder.

If this doesn't get you going, post the actual error and we'll take it from there.

[nandu]

"Here's the SQL format for the procedure call that goes in the User Defined OCI stage;
call <STORED_PROCEDURENAME>(:1,:2,:3,:4,:5) "

Could you pls elaborate User Defined OCI Stage - Do you mean User defined SQL Query ??

When i use call <Stored Procedure (int a)
a: is output parameter

It thorows error in this line
Error says : Oracle_OCI_8_8: exec prc_get_batch_gen(int_outcome)

[trobinson]

You cannot pass a value to a stored procedure AND get a value back (output parm) at the same time. DataStage doesn't work that way. I've never done it but you could have a stored procedure that takes no input and simply returns a stream of data to your job. It would be the driving input stream to the job but I would question that design. You could also encapsulate your stored procedure in a function and embed the function in SQL. Again why? To answer your other question, yes I'm talking about putting the line of code with the call statement in it in the OCI Stage in the textbox where User defined SQL usually goes.

[Sainath.Srinivasan]

I think the simplest method may be to upgrade to DataStage v7.5 which comes with inbuilt Stored Procedure stage.

[mfavero]

It took me a while to decifer what the solution was but I finally got it. I thought I'd summarize:
We wanted to call a stored procedure to TRUNCATE A TABLE prior to inserting new rows. I wanted to put it in the Before SQL tab of the Oracle OCI - sorry no way. So I read and re-read these posts.

I finally managed to write a generic multi-instance job which uses a job parameter for the TABLE NAME and using no input - sends one row with one column to the OCI output stage which uses the one column as the TABLE NAME for the stored procedure call.

XFM >> OCI

dummy stage variable - so no input stage required
one column on output link - derivation is TABLENAME job parm
In OCI stage - select User Defined SQL and enter:

call Stored_Procedure_Name (:1)


works pretty slick


:D

[Mike]

Is there a reason why you couldn't use the OCI stage's truncate then insert option?

Mike

[kcbland]

Is there a reason why you couldn't use the OCI stage's truncate then insert option?

Mike

Truncate requires a high level of authority, you actually have to have alter table permissions. Some DBAs are unwilling to give out that access so the native TRUNCATE statement can't be used. You can force your totalitarian DBA team to give you a stored procedure that can authenticate you to truncate the table, and then perform that action.

[Mike]

Thanks Ken. I guess I haven't had the pleasure of working in such a hostile environment yet.

Mike

[snassimr]

Thank you all . And let me know when MS SP supported....

[elavenil]

This topic was covered earlier. Pls search in the forum and hope you would get an idea how you can call the stored procedure from OCI stage.

Regards
Saravanan