Source Look up in Oracle DB

[pguddera21]

I am trying to do a source look up to create a new column from col 1 utilizing 

source_lookup(10000,'Schema','Table1','Col1','VNDRID=:1', $VNDRID)

Both tables have the VNDRID column and this is in Oracle DB. Both tables have the same data type of 

VARCHAR2(8 CHAR) in oracle DB.

The issue is when I run this source lookup and the task finishes. Only the col1 where VNDRID = 'N/A' match. The rest return as NULL for col1.

We have valid VNDRID that I check and match in both tables however, those are not getting pulled.

Is there any reason on why those would get ignored? We have no filters on this job.

[Heinvandenheuvel]

It sure looks like you are doing the right things.

The two 'classical' reasons for this to fail is 1) trailing spaces and 2) character-set discrepancies.

It's interesting to see how 'N/A' appears to work - which would suggest it is NOT a trailing spaces issue. Still, I would run some test with the lookups being 8 chars, and toss is some test values with 8 uppercase characters [A-Z]. I might also run a test without a bind variable just using single quoted text which you'll have double-single quote to make to parse.

Personally I'd go in with the big guns: Oracle Trace Event 10046. Google for "Oracle SQL trace with bind variables". Maybe you can just set that event for the Oracle Session(s) associated with the right repctl task instance.

fwiw  - possibly interesting reading: "difference between varchar2(10) and varchar2(10 char) in oracle" -> 

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532325800346614530

Good luck, and let us know!

Hein

[Dana_Baldwin]

Hi @pguddera21 

If one of the replies helped to resolve the issue, please mark it as a solution & like it to make it easier for others to find.
Thanks in advance!

[pguddera21 · Accepted solution]

It looks like the N/A results showed up before the valid entries. When I filtered those out in the lookup, it matched correctly.