Routine to delete a table

[sheema]

I have a need where data in a oracle staging table has to be deleted before the job runs. I do not want to use the open /close command in Oracle Enterprise stage.
Can we create a before/after job sub routine to delete data in oracle table.

Can some one help me in this regard.


Thanks

[chulett]

Why not just script a sqlplus session? Then ExecSH before job will get the job done.

[sheema]

Craig,
I have never done it,Can you let me know how do it.

Thanks

[chulett]

Best to sit with your DBA or someone there that's done that kind of thing and have them walk you through it.

[mystuff]

Create a job control using the following- You can call this in before sub-routine

Code: Select all

     UnixCmd = ScriptName : Agr1 :" ": Agr2 :" ": Agr3

     Call DSU.ExecSH(UnixCmd, ErrCode)

In the Unix Script :ScriptName call sqlplus

Code: Select all

sqlplus -s $UserId/$Password@$OracleDb <<-EOF   

DELETE FROM <your staging table>;
EOF

If you are thinking just to delete that particular staging table so that you can load in it , select option truncate (or) delete rows before inserting values in the plugin

[sheema]

Thank you.

[chulett]

Pretty bare bones, that. Your DataStage job will have no clue if that sql succeeds or fails.

[mystuff]

we might have to remove the -s option and forward the output to a file, then may be grep and indicate error based on it

sqlplus $UserId/$Password@$OracleDb <<-EOF > $File
DELETE FROM <your staging table>;
EOF