Oracle Enterprise stage
Moderators: chulett, rschirm, roy
Oracle Enterprise stage
Hi,
I have a job where i read data from a sequential file and load the data into a Oracle table.I have been using Upsert Write Method to get this done(since SQL loader is not configured we are not using the Load Write Method). Now I need to delete the staging table before i insert rows into the staging table. Since we do not have options Open command and close command in Upsert Write method,I am trying to write the following commands in User defined update sql window
Delete from Table1
Commit
Insert into Table1(col1,col2,col3,col4) values (ORCHESTRATE.col1,ORCHESTRATE.col2,ORCHESTRATE.col3,ORCHESTRATE.col4)
But I get the below warning
oes_Table1: Update prepare failed:
update is: Delete from Table1
Commit
Insert into Table1(col1,col2,col3,col4) values (ORCHESTRATE.col1,ORCHESTRATE.col2,ORCHESTRATE.col3,ORCHESTRATE.col4)
sqlcode is: -933
esql complaint: ORA-00933: SQL command not properly ended.
Can any one help me in this matter.
Thanks
I have a job where i read data from a sequential file and load the data into a Oracle table.I have been using Upsert Write Method to get this done(since SQL loader is not configured we are not using the Load Write Method). Now I need to delete the staging table before i insert rows into the staging table. Since we do not have options Open command and close command in Upsert Write method,I am trying to write the following commands in User defined update sql window
Delete from Table1
Commit
Insert into Table1(col1,col2,col3,col4) values (ORCHESTRATE.col1,ORCHESTRATE.col2,ORCHESTRATE.col3,ORCHESTRATE.col4)
But I get the below warning
oes_Table1: Update prepare failed:
update is: Delete from Table1
Commit
Insert into Table1(col1,col2,col3,col4) values (ORCHESTRATE.col1,ORCHESTRATE.col2,ORCHESTRATE.col3,ORCHESTRATE.col4)
sqlcode is: -933
esql complaint: ORA-00933: SQL command not properly ended.
Can any one help me in this matter.
Thanks
Do you think there is any better way to implement the deletes
and than the Inserts.Is it safe to use a single job and have 2 links from the transformer
1st link I select the Delete Rows (Write Method) and the 2nd link I insert the rows
using the Upsert(Write Method).
In the Link ordering first the Deletes and then the Inserts.
Thanks
and than the Inserts.Is it safe to use a single job and have 2 links from the transformer
1st link I select the Delete Rows (Write Method) and the 2nd link I insert the rows
using the Upsert(Write Method).
In the Link ordering first the Deletes and then the Inserts.
Thanks
My insert statement is below in the Oracle Enterprise stage
Insert into Table1(col1,col2,col3,col4) values (ORCHESTRATE.col1,ORCHESTRATE.col2,ORCHESTRATE.col3,ORCHESTRATE.col4)
I don't see anything wrong with it,as i stated earlier the insert statement works fine if i run just the insert statement with out Delete statement and Commit.So I think the problem is not with the Insert statement.
we do not have Open command option for 'Upsert' Write Method.
Thanks
Insert into Table1(col1,col2,col3,col4) values (ORCHESTRATE.col1,ORCHESTRATE.col2,ORCHESTRATE.col3,ORCHESTRATE.col4)
I don't see anything wrong with it,as i stated earlier the insert statement works fine if i run just the insert statement with out Delete statement and Commit.So I think the problem is not with the Insert statement.
we do not have Open command option for 'Upsert' Write Method.
Thanks
There's a trick way of doing this. In the job, do a read from the same database where the table to be deleted exists and do a 'select 1 from dual' and write your table truncate/delete copmmand in the open command there and then device a dummy lookup with the actual stream of data on this dummy column which will always have '1' as the data.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
Do you think there is any better way to implement the deletes
and than the Inserts.Is it safe to use a single job and have 2 links from the transformer
1st link I select the Delete Rows (Write Method) and the 2nd link I insert the rows
using the Upsert(Write Method).
In the Link ordering first the Deletes and then the Inserts.
Thanks
and than the Inserts.Is it safe to use a single job and have 2 links from the transformer
1st link I select the Delete Rows (Write Method) and the 2nd link I insert the rows
using the Upsert(Write Method).
In the Link ordering first the Deletes and then the Inserts.
Thanks