Running an Oracle SQL script

bennylag · Post by **bennylag** » Thu Oct 29, 2009 11:57 am

Hello,

I am using Datastage 8.1 running on an AIX 5.3 server. My job is basically loading a table from an Oracle database (10g) into another Oracle database. After the load is complete I need to run a series of SQL commands (drop, delete, create index, etc..). Besides using the "Close command" of the Oracle stage or the after job routine of the job is there any way that I can pass a list of SQL commands seperated by a semicolon (;) to an Oracle database? This is simply using a SQL step in SSIS and was looking for the Datastage equilavent.

Any help or suggestions would be greatly appreciated,

Benny

chulett · Post by **chulett** » Thu Oct 29, 2009 12:08 pm

Doesn't the CLOSE command support that?

There's no direct equivalent to the 'SQL step' you mentioned, in my world this would be a scripted call to sqlplus that executed all of those commands and checked for issues, called as a separate job / step so it can be restarted without being tied to the load job.

bennylag · Post by **bennylag** » Thu Oct 29, 2009 12:12 pm

chulett wrote:Doesn't the CLOSE command support that?

There's no direct equivalent to the 'SQL step' you mentioned, in my world this would be a scripted call to sqlplus that executed all of those commands and checked for issues, called as a separate job / step so it can be restarted without being tied to the load job.

The close command is limited to just 1 command. I need to run a series of SQL commands.

Thanks

Benny

chulett · Post by **chulett** » Thu Oct 29, 2009 12:22 pm

Sorry, thought it supported multiple commands much like the before/after sql tabs do in other stages.

bennylag · Post by **bennylag** » Thu Oct 29, 2009 2:56 pm

chulett wrote:Sorry, thought it supported multiple commands much like the before/after sql tabs do in other stages.

I also thought that would be the solution but unfotrunately not.

I developed an alternate method in which to do this. I created a "server" job that reads from oracle and writes to a sequantial file. The SQL for the Oracle stage is a simple "select 1 from dual" and the job writes to a dummy file in the temp directory. The job has 2 parameters SQL1 (Before SQL) and SQL2 (After SQL) that can be executed via the ORACLE read stage. This job can be used dynamically by passing the SQL command(s) to the job via the parameters. The "server" job also allows the ignoring of SQL commands if it is not necessary to cause the job to abort.

Thanks for your help,

Benny

chulett · Post by **chulett** » Thu Oct 29, 2009 3:23 pm

Interesting solution. So, no issues with quotes disappearing yet? It is notorious for stripping quotes from parameters, which usually means sql snippets are affected the most.

bennylag · Post by **bennylag** » Fri Oct 30, 2009 7:14 am

chulett wrote:Interesting solution. So, no issues with quotes disappearing yet? It is notorious for stripping quotes from parameters, which usually means sql snippets are affected the most. ...

Good point! I have not had to pass special characters or quotes in this job yet. I guess when that is a requirement I will have to use a sqlplus script.

Thanks for your help,

Benny

bennylag · Post by **bennylag** » Fri Jan 22, 2010 11:18 am

I've discovered that the new Oracle Connector stage available in Datastage version 8.1 allows for multiple commands in the close command. It also allows for partition reads and the option to execute a close command by stage or by node.

kbsuryadev · Post by **kbsuryadev** » Tue Jan 26, 2010 8:26 am