Problem in using StoredProcedure

sunshine · Post by **sunshine** » Sat Mar 11, 2006 10:06 pm

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

bmadhav · Post by **bmadhav** » Sat Mar 11, 2006 10:28 pm

U can execute it in the Before SQL tab of the same stage.
u don't need a seperate stage for this.
I have done this within a DRS stage, i haven't tried it with the ODBC stage.

chulett · Post by **chulett** » Sun Mar 12, 2006 7:50 am

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.

DSguru2B · Post by **DSguru2B** » Mon Mar 13, 2006 10:33 am

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 .

roy · Post by **roy** » Mon Mar 13, 2006 4:36 pm

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,