Hi,
I would like to know, how to use the store procedure (Oracle) with Data Stage.
thank you
Peppinodicapri
Store procedure in DataStage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 5
- Joined: Thu Jan 27, 2005 8:34 am
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 5
- Joined: Thu Jan 27, 2005 8:34 am
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Prior to DS V7, you will have to call your stored procedure via ODBC - such as "call {procedurename}" or call it from a function referred in your SQL statement
Otherwise, call a shell command such as
'sqlplus ..... execute procname'
From DS V7 onwards, you have a stored procedure stage inbuilt with DataStage which provides this functionality.
Otherwise, call a shell command such as
'sqlplus ..... execute procname'
From DS V7 onwards, you have a stored procedure stage inbuilt with DataStage which provides this functionality.
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
You can call an Oracle Stored Procedure via the OCI stage if you want. Here's an example that would be defined as User defined SQL;
call PKG_BETASECPREPROCESS.PR_DELETESECURITY(:1,:2,:3,:4,:5)
You'll get nothing returned except the return code. We use this methodology to encapsulate units of work that would be very painful to try and code all in DataStage.
call PKG_BETASECPREPROCESS.PR_DELETESECURITY(:1,:2,:3,:4,:5)
You'll get nothing returned except the return code. We use this methodology to encapsulate units of work that would be very painful to try and code all in DataStage.