Page 1 of 1
Store procedure in DataStage
Posted: Tue Feb 01, 2005 2:56 am
by peppinodicapri
Hi,
I would like to know, how to use the store procedure (Oracle) with Data Stage.
thank you
Peppinodicapri
Posted: Tue Feb 01, 2005 2:57 am
by Sainath.Srinivasan
Use the 'Stored Procedure' stage from v7 onwards.
Posted: Tue Feb 01, 2005 3:04 am
by peppinodicapri
Sainath.Srinivasan wrote:Use the 'Stored Procedure' stage from v7 onwards.
can you be most specify?
thank
Posted: Tue Feb 01, 2005 3:16 am
by Sainath.Srinivasan
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.
Posted: Tue Feb 01, 2005 9:36 am
by throbinson
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.