executing a bunch of SQL Statements

mystuff · Post by **mystuff** » Tue May 29, 2007 5:42 pm

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

ArndW · Post by **ArndW** » Tue May 29, 2007 6:48 pm

mystuff - could you post the SQL that is causing the error message you listed?

chulett · Post by **chulett** » Tue May 29, 2007 7:12 pm

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.

Sreenivasulu · Post by **Sreenivasulu** » Wed May 30, 2007 1:47 am

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

Sreedhar · Post by **Sreedhar** » Wed May 30, 2007 4:34 am

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

chucksmith · Post by **chucksmith** » Thu May 31, 2007 3:37 pm

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.

mystuff · Post by **mystuff** » Thu Jun 14, 2007 6:16 pm

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

chulett · Post by **chulett** » Thu Jun 14, 2007 6:35 pm

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.