Page 1 of 1
ORacle table lookup
Posted: Wed Feb 01, 2006 5:53 am
by srekant
Hi,
I am doing direct lookup to an oracle table (having userdefined Query with multiple unions) when i run the job with out giving bind variable "the first row value of lookup is repeated for the remaining rows".
when i use a bind variable i am getting the following error
Code: Select all
"ORA-01036: illegal variable name/number"
Posted: Wed Feb 01, 2006 7:53 am
by chulett
Post your query.
Re: ORacle table lookup
Posted: Wed Feb 01, 2006 5:40 pm
by rwierdsm
I've seen this error when the input key value does not line up with ':1" value in the query, i.e. you are passing the bind variable in the 4th port, but have defined your variable as the first port (:1). Make sure they all line up, better still, ensure your key values are at the top of the list.
e.g.
Code: Select all
select x, y, z from table_1
where a = [b]:1[/b]
where the list of columns into the sql are:
column 1 (non key)
column 2 (non key)
column 3 (non key)
column 4 (key)
In this simplistic example, the SQL will be modified to be
Code: Select all
select x, y, z from table_1
where a = [b]:4[/b]
Hope this is clear?!?!?
Rob W
Posted: Wed Feb 01, 2006 5:49 pm
by chulett
Was gonna get there when the query was posted.
Biggest thing is to ensure that all columns are 'bound', that there is a parameter marker in the query for each column defined in the stage. Also, fields marked as Keys are expected to be used in the 'where' clause but I'm not sure if that is enforced when using User Defined SQL in a reference lookup.