ORA-01036: illegal variable name/number

shukla_krishna · Post by **shukla_krishna** » Thu Jul 16, 2009 11:57 pm

I have query in oracle enterprise stage like

UPDATE <Table NAME> SET < Column name> = :2, WHERE <column name > = < Value>

:2 is out put of transformer stage which is out put of routine and routine return the jobs fatal errors for given any other job.

Getting error

ORA-01036: illegal variable name/number

I tried hard coding of :2 then also getting same eror.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 17, 2009 12:38 am

What ever happened to :1 ?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 17, 2009 1:59 am

I think you can skip :1 if you are not using it.

But why do you have a comma before WHERE ?

chulett · Post by **chulett** » Fri Jul 17, 2009 6:28 am

Sainath.Srinivasan wrote:I think you can skip :1 if you are not using it.

No, you cannot, which is why Ray asked about it and why the error is being generated. All columns must be bound into the DML.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 17, 2009 7:30 am

Craig,

You are right.

Tried a test job and gives me the exact error message.

But wonder what I did in Server jobs to avoid using all columns. Must try to remember.

chulett · Post by **chulett** » Fri Jul 17, 2009 8:35 am

The ODBC stage is the only stage that allows that, AFAIK.

shukla_krishna · Post by **shukla_krishna** » Sat Jul 18, 2009 2:27 am

chulett wrote:The ODBC stage is the only stage that allows that, AFAIK. ...

but this is strange, because there is no need to update :1 and you are forced to do that

chulett · Post by **chulett** » Sat Jul 18, 2009 7:28 am

You're not forced to update it but you do need to use it, typically in the 'wher'e clause if nothing else.

If you don't need a column in the DML, don't send it to the stage.

DSXchange

ORA-01036: illegal variable name/number

ORA-01036: illegal variable name/number

I have Resolved by using :1 in query