Calling PL/SQL procedure from a Data Stage job

[sathyanveshi]

Hi,

I have a PL/SQL procedure which has 5 cursors to select the data from the source systems and loads the data into a target table. The PL/SQL is robust and is working fine. I have a requirement where I'm asked to use the same PL/SQL. So, I have to call the procedure using Data Stage. Also, I need to get the load statistics like no. of rows read, no. of rows inserted, time taken etc. If I'm using the stages to select and load the data then I can get the statistics easily. But when I call the procedure from a DS job then how can I get the statistics?

First of all, I need to know whether calling a PL/SQL procedure from a DS job is possible. If yes, then how can I get the load statistics of the procedure using Data Stage job.

Cheers,
Mohan

[venkateskg@yahoo.com]

Hi Sathyan,

What DS version u r working with. If its DS7.5 then you can find the Stored Procedure stage. Here you can call the procedure.

Hi,

I have a PL/SQL procedure which has 5 cursors to select the data from the source systems and loads the data into a target table. The PL/SQL is robust and is working fine. I have a requirement where I'm asked to use the same PL/SQL. So, I have to call the procedure using Data Stage. Also, I need to get the load statistics like no. of rows read, no. of rows inserted, time taken etc. If I'm using the stages to select and load the data then I can get the statistics easily. But when I call the procedure from a DS job then how can I get the statistics?

First of all, I need to know whether calling a PL/SQL procedure from a DS job is possible. If yes, then how can I get the load statistics of the procedure using Data Stage job.

Cheers,
Mohan

[sathyanveshi]

Hi,

I'm using DataStage 7.1r1. And if at all I could call the PL/SQL procedure, how can I get the statistics of rows read and rows loaded by the procedure using a DS job?

Cheers,
Mohan

[chulett]

You can't... if the procedure is doing all of the work, you'll need to have the procedure capture and log its own stats. DataStage won't have a clue what it did.

[mandyli]

hi

u can possible call any SP from datastage but load stat info you can't get or you need to write new routine for load stat..

THANKS
MAN

[T42]

If you can call the PL/SQL procedure within SQL, you can call it within DataStage. (See: Using calls within SELECT statements).

There is also an Oracle Stored Procedure stage (and soon-to-be DB2 if that's not out already).

Just don't expect to be able to control it very much like you do in SQL Navigator (or whatever tool you use.) For plain straight execution, with a single column return, the first option will work.