Store procedure in DataStage

peppinodicapri · Post by **peppinodicapri** » Tue Feb 01, 2005 2:56 am

Hi,
I would like to know, how to use the store procedure (Oracle) with Data Stage.

thank you

Peppinodicapri

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Feb 01, 2005 2:57 am

Use the 'Stored Procedure' stage from v7 onwards.

peppinodicapri · Post by **peppinodicapri** » Tue Feb 01, 2005 3:04 am

Sainath.Srinivasan wrote:Use the 'Stored Procedure' stage from v7 onwards.

can you be most specify?
thank

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Feb 01, 2005 3:16 am

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.

throbinson · Post by **throbinson** » Tue Feb 01, 2005 9:36 am

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.