Page 1 of 1
executing a bunch of SQL Statements
Posted: Tue May 29, 2007 5:42 pm
by mystuff
I need to execute a bunch of SQL Statements.
(There are some truncate statements and some delete statments)
target is Oracle db.
what is the best way to execute the bunch of sql statements?
what i tried is:
DummySeq_File-->X-->DRS_Stage
one column i mentioned as a dummy value
and update action:User defined SQL
and used bunch of sql statements.
but its throwing exception:column name invalid identifier.
thanks
Thanks
Posted: Tue May 29, 2007 6:48 pm
by ArndW
mystuff - could you post the SQL that is causing the error message you listed?
Posted: Tue May 29, 2007 7:12 pm
by chulett
There's no way you can execute "a bunch" of different sql statements from a single link, you won't be able to bind everything properly to the columns in the stage. Which is exactly what your error says happened.
For something like that, I'd suggest a script via sqlplus. Or put them in a proc.
Posted: Wed May 30, 2007 1:47 am
by Sreenivasulu
chulett wrote:There's no way you can execute "a bunch" of different sql statements from a single link, you won't be able to bind everything properly to the columns in the stage. Which is exactly what your error say ...
As Chulett says you cannot execute multiple sql statements in user-defined sql. User-defined sql to write only a single query big or small.
Regards
Sreeni
Posted: Wed May 30, 2007 4:34 am
by Sreedhar
MyStuff,
Why don't you try a shall script for your requirement. That should help you run as many SQL as possible, as long as you have resources to attend the task.
Regards,
Sm
Posted: Thu May 31, 2007 3:37 pm
by chucksmith
If your SQL statements really have nothing to do with the row you are passing to the DRS stage, then do not pass any rows. Set the output constraint to @FALSE, and put you SQL statements on the after SQL tab.
Posted: Thu Jun 14, 2007 6:16 pm
by mystuff
my intention is just to execute a bunch of sql statements.
i dont need to perform anything within the job, other than execution of sql statements.
what is the most optimistic desgin of the job?
i mean which is best to use,(eg:odbc or oci or oracle plugin)
thanks
Posted: Thu Jun 14, 2007 6:35 pm
by chulett
No stages. Scripts.
DSExecute a sqlplus script with your 'bunch' in it from the Job Control tab. Or run them one at a time so that each can log their individual runtimes plus success or failure into the job's log. You can even wrapper them with DSLogInfo calls to be more verbose...
Running script 1...
Script 1 execute successfully.
Running script 2...
Etc.