Running an Oracle SQL script
Moderators: chulett, rschirm, roy
Running an Oracle SQL script
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
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The close command is limited to just 1 command. I need to run a series of SQL commands.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.
Thanks
Benny
I also thought that would be the solution but unfotrunately not.chulett wrote:Sorry, thought it supported multiple commands much like the before/after sql tabs do in other stages.
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
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.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. ...
Thanks for your help,
Benny