call oracle stored procedure in datastage server job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3
- Joined: Tue Feb 28, 2012 4:18 am
call oracle stored procedure in datastage server job
I have one long oracle procedure which I want to execute via datastage server job. It is not taking any parameters and not returning anything. It is extracting data from some mainframe tables (via db links) and loading data in oracle target tables.
It is a long procedure that has got cursors, execute immediate statements, delete stmt, truncate stmt and many more.
It is internally calling some functions to dump data in log tables also.
The procedure is doing fine when we are executing this through unix script or through oracle only.
I want to know how to execute this procedure in datastage server job. Just want to call it and execute it in datastage without passing any parameter.
It is a long procedure that has got cursors, execute immediate statements, delete stmt, truncate stmt and many more.
It is internally calling some functions to dump data in log tables also.
The procedure is doing fine when we are executing this through unix script or through oracle only.
I want to know how to execute this procedure in datastage server job. Just want to call it and execute it in datastage without passing any parameter.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3
- Joined: Tue Feb 28, 2012 4:18 am
-
- Participant
- Posts: 3
- Joined: Tue Feb 28, 2012 4:18 am
I tried it using CALL ProcedureName, but it is giving me error: ORA-06576: not a valid function or procedure name. This procedure is working fine at oracle side. I even tried all the suggestions menioned on here.
Any other siggestion?
Any other siggestion?
Re: call oracle stored procedure in datastage server job
So what's the rationale for running this stand-alone stored procedure from a server job?AmarpreetKaur wrote:It is not taking any parameters and not returning anything.
Why not just run it from a job sequence if you simply want its execution controlled by DataStage?
Mike
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 4
- Joined: Tue Sep 18, 2007 3:40 pm
- Location: Riyadh, Saudi Arabia