DSXchange

Hi,

I have to convert a oracle stored procedure to DataStage job. In procedure
their is merge statement.

I have tried with oracle connector stage (with insert and update mode) but the job failed with error " is of incorrect type. The required statement type is: INSERT .........."

Is there any direct way to execute that merge statement in oracle connector stage or enterprise stage. Or I have to convert it to upsert statement.

DataStage - 9.1, Oracle 10g

Regards
Manoj

Do the MERGE 'After SQL' once the work table has been loaded.

Thanks Craig,

I think I can not add the merge to the after SQL statement, because this my first statement in the procedure.

Regards

Meaning what, the table has already been loaded? Then just build a job with a single row into the Connector that does nothing and then run the MERGE after SQL. What else does the proc do besides that?

Hi,

I have asked some what similar question to my client,
I am looking for a dummy table where i can insert a record (which does not have any business impact) and as you have suggested I will execute the merge in the After SQl part and in the actual SQL i will execute the insert into dummy table.

Regards
Manoj

You can use any table, just craft some SQL that "does nothing"... say, update a record "where 1 = ?" and then pass in a 2 as the value. Select the 2 from DUAL.

I think merge statement does work in Oracle enterprise stage, seen that in earlier version, you can give that a shot as well.