DSXchange

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

Semi-colons.

If you want to use the upsert option then it means records should be existing for the update operation. To refresh the table with upsert option used either table can be cleared as before job subroutine or as a seperate job.

Craig,

when i have placed semi colons I get the below error
oes_Table1: Update prepare failed:
update is: DELETE FROM Table1;
COMMIT;
INSERT INTO Table1
(col1, col2, col3, col4)
VALUES
( :col1, :col2, :col3, :col4);
sqlcode is: -911
esql complaint: ORA-00911: invalid character


Thanks

Does the delete and commit happen? I don't believe your bind parameters in the insert sql are correct for PX.

If I execute just the Insert statement(with out delete and commit) than it works perfectly well.
So the Insert statement is working well .

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

Hi

Can you one think of syntax for the below insert statement?

INSERT INTO Table1
(col1, col2, col3, col4)
VALUES
( :col1, :col2, :col3, :col4);


Correct this statement....

regards,

Put the delete in the Open command property??

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

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.

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

Using a single job will not work. Infact, the best and cleanest way is to do a separate job that deletes all records from your table.