Page 1 of 1
ORA-01036: illegal variable name/number
Posted: Thu Jul 16, 2009 11:57 pm
by shukla_krishna
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.
Posted: Fri Jul 17, 2009 12:38 am
by ray.wurlod
What ever happened to :1 ?
Posted: Fri Jul 17, 2009 1:59 am
by Sainath.Srinivasan
I think you can skip :1 if you are not using it.
But why do you have a comma before WHERE ?
Posted: Fri Jul 17, 2009 6:28 am
by chulett
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.
Posted: Fri Jul 17, 2009 7:30 am
by Sainath.Srinivasan
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.
Posted: Fri Jul 17, 2009 8:35 am
by chulett
The ODBC stage is the only stage that allows that, AFAIK.
I have Resolved by using :1 in query
Posted: Sat Jul 18, 2009 2:27 am
by shukla_krishna
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
Posted: Sat Jul 18, 2009 7:28 am
by chulett
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.