Problems with variables in generated insert/update

[michaelsarsnorum]

I have a target OCI stage that is set to "Update existing, Insert...". The SQL of this stage is generated from the column list. And gives med the following:

Code: Select all

UPDATE ANSATT /* PDB - AnsattFraStage */  SET BANKKODE=:2,LOGON_ID=:3,ANSATT_FOM=TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),ANSATT_TOM=TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'),FORNAVN=:6,ETTERNAVN=:7,INITIALER=:8,ANSATTROLLE=:9 WHERE ANSATT_ID=:1;
INSERT INTO ANSATT /* PDB - AnsattFraStage */  (ANSATT_ID,BANKKODE,LOGON_ID,ANSATT_FOM,ANSATT_TOM,FORNAVN,ETTERNAVN,INITIALER,ANSATTROLLE) VALUES (:1,:2,:3,TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:5, 'YYYY-MM-DD HH24:MI:SS'),:6,:7,:8,:9)

When I try to run the job I get the following error message:

Code: Select all

ORA-01036: illegal variable name/number

There is no mismatch between the colums definition and the DB-table.

I tried searching the forum for related issues and found a lot of posts, none which actually gave me an answer to my problem.

Anyone have any ideas on this? Could it be that the date format contains : and the MM and SS parts therefore are interpreted as variables? I have nine columns and use nine numbered variables so I can't se where the problem is.

Any help is apreciated.
m.

[kcbland]

I'm a little curious about the /* PDB - AnsattFraStage */ comment in the table name. You should remove that, otherwise the SQL looks correct for the Oracle stage.

It might be the data, consider if NULL values are being passed in, or a mismatch with the column definitions, typical of user-defined SQL. Since you're using generated, that shouldn't be an issue. That comment after the tablename is the only thing that really stands out.

[ray.wurlod]

Is any of the columns a non-numeric data type, that would require quotes around its value in the SET clause?

[michaelsarsnorum]

I strongly doubt that it's the comment causing this error. We use this kind of commenting in a lot of our server jobs and none of the others fail.

When it comes to non-numeric data colums no. 3 and 6-9 are char or varchar. However since this is a generated SQL statement I can't add quotes without changing the stage to use User Defined SQL.

The fist thing that struck me was that it could be something with the date formating. Does oracle interpret :MM and :SS as variables?

[chulett]

The fist thing that struck me was that it could be something with the date formating. Does oracle interpret :MI and :SS as variables?

No, not when they are quoted and in a TO_DATE like that. This smells like a bug to me. I'd suggest you delete that OCI stage from the job and add it back in from scratch, see if that changes the behaviour at all.