Like predicate not working with Oracle -

I have a VB6 SP4 project connecting via OLE DB to an Oracle 8i EE database.

We are trying to retrieve all of the records from a table that meet a certain criteria. When our SQL statement reads:

A) select ACTIVITY, CONTROLNO, RIGNO from FAILURES where ACTIVITY='XYZ';

we get a resultset, but that resultset is not what we really want. The records that we really want are the ones where the CONTROLNO values start with a certain string (the Activity name). My first approach was:

B) select ACTIVITY, CONTROLNO, RIGNO from FAILURES where controlno like 'XYZ%';

This results in no records when executed in VB. However, copying this SQL statement to SQL Plus and it results, correctly, in 4 records.

Does anyone have a clue? I even tried using the underscores (there are 6 trailing characters), but it also worked in SQL Plus while not working in VB.

TIA

You might be having problems with case sensitivity....does your controlno start XYZ or xyz????

doing this for a DoD project, everything is capitalized. I am actually writing the sql statement to the debug window using the debug.print command, and pasting it into the SQL Plus editor and getting 4 rows there, while 0 records in VB...

Are you able to get any records returned when passing your statement through VB? Could it be that the error isn't in your where statement but in your connection? I would test this by doing a simple select and see if you get the expected results first.

The DBA has incorporated row-level security, and while testing via the app, I was logged in as a user, who, do it her illogical implementation, is unable to 'see' the records. When I was logged into SQL Plus, I was logged in as Admin, and thus, had privilege to see those records.


Aaaaaaarrrrrrrrgggggggggghhhhhhhh!!!!!!!!

Thanks for the help - I really do appreciate it. These problems are often solved by adding some eyes. I think I'm gonna start participating here on a regular basis.

Glad to hear it!!!

It's nice to know when you've helped someone...it makes you want to answer the next person's questions. Often I see answers posted and don't know whether it is the correct answer because the person doesn't respond one way or the other....


(Katie gets down off her soapbox and goes out for a beer).