Page 1 of 1
connecting to oracle
Posted: Fri Apr 24, 2009 3:43 am
by dr.murthy
hi all,
ihave an reqirement with some destructive tables(data which is not incremental),so each and every time i need to truncate the data in table and then it should be loaded.i dont have replace privilages wit in the datastage,so far i create three jobs
1)delete data into table
2)load data as per reqirement
3)sequence to call above two jobs
now what i plan to do,i want to prepare one sql script for truncate table data and then load the freshdata.
i design the sequence job somehing like
Code: Select all
routineactivity(to call sequel script and job activity(call load job)
my question is how to run sql script with in datastage.
Re: connecting to oracle
Posted: Fri Apr 24, 2009 4:00 am
by Pagadrai
Hi,
One advice.
Instead of 2 jobs and a sequence, you can use the Open command Option in the Oracle Entreprise stage to delete the rows before inserting them.
There are lot of posts in this forum about Open Command Option.
Re: connecting to oracle
Posted: Fri Apr 24, 2009 4:40 am
by dr.murthy
Pagadrai wrote:Hi,
One advice.
Instead of 2 jobs and a sequence, you can use the Open command Option in the Oracle Entreprise stage to delete the rows before inserting them.
There are lot of posts in this forum about Open Command Option.
thanks for responding ,
could you please tell me where can i use this open command exactly
Re: connecting to oracle
Posted: Fri Apr 24, 2009 4:51 am
by Pagadrai
From Manual:- " Open Command: This is an optional property and only appears for the Load Write Method. Use it to specify a command, in single quotes, to be parsed and executed by the Oracle database on all processing nodes before the Oracle table is opened. You can specify a job parameter if required. "
OPEN/CLOSE commands are executed before or after the sql.
Enterprise stage --> properties tab --> Options Category
Re: connecting to oracle
Posted: Fri Apr 24, 2009 5:06 am
by dr.murthy
h thanks,
could you please tell me is there any syntax to follow with open command
Re: connecting to oracle
Posted: Fri Apr 24, 2009 5:22 am
by Pagadrai
No specific syntax I believe.
As the manual suggests, you can type in the delete command sql you want to run in the Open command option.
You should enclose the sql in single quotes.
Try and let us know.
Re: connecting to oracle
Posted: Fri Apr 24, 2009 5:30 am
by dr.murthy
Pagadrai wrote:No specific syntax I believe.
As the manual suggests, you can type in the delete command sql you want to run in the Open command option.
You should enclose the sql in single quotes.
Try and let us know.
HI,
I tried the sql statement in single codes but its throwing error like
Oracle_Enterprise_25,0: Oracle call failed; sqlcode = -900; message: ORA-00900: invalid SQL statement
.
Re: connecting to oracle
Posted: Fri Apr 24, 2009 5:35 am
by Pagadrai
Posted: Fri Apr 24, 2009 7:47 am
by chulett
Don't quote it.
