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
Calling PL/SQL procedure from a Data Stage job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 66
- Joined: Tue Dec 07, 2004 12:48 pm
-
- Participant
- Posts: 1
- Joined: Tue Dec 14, 2004 3:35 am
- Location: Singapore
Re: Calling PL/SQL procedure from a Data Stage job
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.
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.
sathyanveshi wrote: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
Venkatesh
-
- Participant
- Posts: 66
- Joined: Tue Dec 07, 2004 12:48 pm
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.
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.