Hi,
I need to execute Stored Procedure on a Table, in Data Stage
scenario is:
OCI ---> Transformer-->ODBC Stage
Here i am copying data from OCI Stage(EMP) to ODBC Stage( EMP_NEW)
in the mean time, I need to execute a StoredProcedure on table "EMP_NEW"
I already created storedprocedure on Table EMP_NEW in Database.
should i need to add one more stage for calling stored Procedure?
I am using data stage version 7.1.
Thanks
Regards
sunshine
Problem in using StoredProcedure
Moderators: chulett, rschirm, roy
Some can be executed before/after SQL using 'call' but some will insist on being 'executed' in an anonymous block, which can't be done there.
If you have 7.5.x then you have the Stored Procedure stage which would be the prefered methodology. It does allow you to wrap it in an anonymous block and (mostly) properly handles input and output parameters, so it can be used as either a source or a target in a job. I believe that I read that it can be used as a lookup as well, but don't quote me on that.
You should also be able to use the ODBC stage directly, as long as your SP conforms to its restrictions which are documented in the plug-in's pdf in your Docs folder. Import the metadata from the SP. One of the choices of Update Action in the ODBC stage is 'Call stored procedure'. It will generate SQL to 'CALL' your procedure and any columns defined in the stage will become input parameters.
One of those ways should work for you.
If you have 7.5.x then you have the Stored Procedure stage which would be the prefered methodology. It does allow you to wrap it in an anonymous block and (mostly) properly handles input and output parameters, so it can be used as either a source or a target in a job. I believe that I read that it can be used as a lookup as well, but don't quote me on that.
You should also be able to use the ODBC stage directly, as long as your SP conforms to its restrictions which are documented in the plug-in's pdf in your Docs folder. Import the metadata from the SP. One of the choices of Update Action in the ODBC stage is 'Call stored procedure'. It will generate SQL to 'CALL' your procedure and any columns defined in the stage will become input parameters.
One of those ways should work for you.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
What does your stored procedure do?
As said by Craig, you have Stored Procedure stage to call a stored procedure. The stage would require inputs and if you give an output from that stage you will need to provide the output columns. So that depends on the functionality of your stored proc.
If you dont have any output columns, then just pass a dummy value to the SP stage and let it execute it.
You also have the options to pass parameters .
As said by Craig, you have Stored Procedure stage to call a stored procedure. The stage would require inputs and if you give an output from that stage you will need to provide the output columns. So that depends on the functionality of your stored proc.
If you dont have any output columns, then just pass a dummy value to the SP stage and let it execute it.
You also have the options to pass parameters .
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Hi,
As far as I can see poster is using 7.1 ruling out the stored procedure stage, plus what DB is used as target?, not Oracle or ODBC wouldn't have been used from my common scence (but I may be wrong).
So even if you had DV version that has the stage you need to make shure you use a DB that this stage supports.
I think you should be able to invoke using user defined sql the procedure you need, but baring in mind that ODBC doesn't have before/sfter sql parts you might need to invoke it in a seperate job before you run this one.
IHTH,
As far as I can see poster is using 7.1 ruling out the stored procedure stage, plus what DB is used as target?, not Oracle or ODBC wouldn't have been used from my common scence (but I may be wrong).
So even if you had DV version that has the stage you need to make shure you use a DB that this stage supports.
I think you should be able to invoke using user defined sql the procedure you need, but baring in mind that ODBC doesn't have before/sfter sql parts you might need to invoke it in a seperate job before you run this one.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org