Stored Procedure Stage - how does it work?

StefL · Post by **StefL** » Thu Mar 31, 2005 9:19 am

I have a situation where I believe I would benefit from using a Stored Procedure Stage, but I find the documentation to lack any detailed description of this stage. The online help file STPgui.HLP is missing in my installation and in the pdf files there's not much either.

What I need to do is use a stored procedure to input data rows to a table; this is desired for consistency reasons, and it seems logical to use a Stored Procedure Stage to do this.

I get an Ora Error message as follows:

<JobName>..<StageName>: ORA-06550: line 1, column 7: PLS-00201: identifier '<SCHEMA>.<PACKAGE>' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

where <SCHEMA>.<PACKAGE> is the package where the procedure is defined. Do I need to put the procedure directly in the schema as a 'free' procedure not in a package, or is this a sign of something else being weird?

And what is the difference between the Data Source and the Database Name properties in the STP compared to the single Database Source Name property in a regular Oracle stage?

roy · Post by **roy** » Thu Mar 31, 2005 9:28 am

Hi,
look in the installation CD it's there.
(under DataStage Packages\Plugin Stages\DOCS\ENU)
also as I recall it's usable for Oracle 9 and above (other DBs may not yet be supported).
also I think you need to import the procedure definition before using it.

you actually must use it only if you need output parameters, if not yu can invoke SPs in a user defined or before/aftersql.

IHTH,

StefL · Post by **StefL** » Thu Mar 31, 2005 9:41 am

Found it - thanks!
(Didn't realise it was regarded a plugin since the stage existed within Designer...)

However I'm still not sure how to import the procedure definition. Import->Plugin meta data->Oracle 9 alternative only appears to load tables and views. And the import->stored procedures appears to use ODBC (DSN) instead of the standard Oracle interface, but that is perhaps normal...

roy · Post by **roy** » Thu Mar 31, 2005 9:45 am

Alas I have Oracle 8 so can't fully test it till we upgrade to 10g in a few months.
I think it is only ODBC importable.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 31, 2005 4:15 pm

It's a plug-in that you plugged in when you selected the plug-in stages when installing the server.

Almost all server job stage types are plug-ins; only the eight (?) that came with version 1.0 aren't.