Oracle Update Query problem

[gilrobalopes]

Hi all,
I have a job that is supposed to do something like this:

File -> Transform -> Update Oracle Table

The problem resides on the "where clause" of my Custom Update Query:

update xpto set field1=:1 where field2 in (:2)

It seems that the "in" part of the query is completely ignored and no update is done.

Help! Anyone?
Thanks

[ketfos]

Hi Girrobalopes,

I was facing a similiar problem.

Where do you define your Custom Update Query.
Is it defined in the target Oracle table stage.
I was getting the error message
"ORA-01036: illegal variable name/number" when I defined the user defined sql in target Oracle table stage and input is flat file stage.

Ketfos

[gilrobalopes]

Hi,
I had that same problem too, but it had to do with the column names in the transoformer that didn't match the table layout. After correcting these, I was able to run successfully the job, however th update was not done. My guess is that the field with the multiple vaues for the "in" restriction is being messed up by DS...

[ogmios]

Hi,
I had that same problem too, but it had to do with the column names in the transoformer that didn't match the table layout. After correcting these, I was able to run successfully the job, however th update was not done. My guess is that the field with the multiple vaues for the "in" restriction is being messed up by DS...

It's not DataStage, you can't bind a list of values in a query without some special Oracle tricks. It's an actual bind, it's not like the :2 will be replaced by the string in your second column

Ogmios

[ray.wurlod]

If a parameter marker is to be used in the WHERE clause, it must be marked as a Key column in the DataStage column definitions. It is irrelevant whether it is actually a key in the database; the concept here (on a reference input link) is that of a "search key".

You are permitted to lie about "keys" in this case.

[vinaymanchinila]

Hi ,
I had a Oracle stage in server job which does update else insert, now if it was updating I did not want to update one of the columns, and it gives the same error

ORA-01036: illegal variable name/number

Can we not have different number of columns for update and insert statements in the same stage?

I did try with array size 1.

Thanks,

[chulett]

No, you cannot. If you need different column sets for insert versus update, you'll need two links.

[alisoviejo]

Hi Craig,
I can use a lookup to bifurcate into 2 links , one for update and one for insert but the target has close to 10 million rows and I am sure its not a easy lookup.

Basically we have record_dreate_date and record_update_date, I do not want th estage to update record_create_date when it is really updating an existing row!

Thanks,

[ray.wurlod]

Pre-populate a hashed file with only the key of each row in the target table. Suddenly it's an easy lookup, one that can probably be cached in memory.

[nkln@you]

No, you cannot. If you need different column sets for insert versus update, you'll need two links. ...

You can have different no. of columns in the Update and Insert Clause in a job. i tried and the job ran successfully. In my job i didnt want to update some of the columns But while inserting i wanted it to hardcoded to some value. so i never brought these columns in the Output stage and in the insert clause i added extra columns along with the hardcoded value. And it worked!!

Avinash

[chulett]

Yes, you can force it to work in a single link by using custom / user-defined sql and hard-coding any 'missing' values as you've found. I, for one, would never take that approach but if others are comfortable with it...

You are much better off using two links and specifically handling inserts versus update rather than the 'crutch' of the dual-purpose update actions of insert-then-update or update-then-insert. IMHO, of course. At least then you'll know how many of each type were performed and not worry that, for the second action to happen the first action must be attempted and fail. Yerk.