Page 1 of 1
Routine to delete a table
Posted: Wed Jul 30, 2008 12:05 pm
by 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
Posted: Wed Jul 30, 2008 12:21 pm
by chulett
Why not just script a sqlplus session? Then ExecSH before job will get the job done.
Posted: Wed Jul 30, 2008 12:36 pm
by sheema
Craig,
I have never done it,Can you let me know how do it.
Thanks
Posted: Wed Jul 30, 2008 1:03 pm
by chulett
Best to sit with your DBA or someone there that's done that kind of thing and have them walk you through it.
Posted: Wed Jul 30, 2008 1:05 pm
by 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
Posted: Wed Jul 30, 2008 1:09 pm
by sheema
Thank you.
Posted: Wed Jul 30, 2008 1:13 pm
by chulett
Pretty bare bones, that. Your DataStage job will have no clue if that sql succeeds or fails.
Posted: Wed Jul 30, 2008 1:41 pm
by 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