Oracle9i: Filter Expression Fails to Exclude Data at Runtime

Question

I have a relatively simple select statement in a VB6 program that I have to maintain. (Suppress your natural tendency to shudder; I inherited the thing, I didn't write it.)

The statement is straightforward (reformatted for clarity):

select distinct 
   b.ip_address 
from 
   code_table a, 
   location b 
where 
   a.code_item = b.which_id and 
   a.location_type_code = '15' and 
   a.code_status = 'R'

The table in question returns a list of IP addresses from the database. The key column in question is code_status. Some time ago, we realized that one of the IP addresses was no longer valid, so we changed its status to I (invalid) to exclude it from appearing in the query's results.

When you execute the query above in SQL Plus, or in SQL Developer, everything is fine. But when you execute it from VB6, the check against code_status is ignored, and the invalid IP address appears in the result set.

My first guess was that the results were cached somewhere. But, not being an Oracle expert, I have no idea where to look.

This is ancient VB6 code. The SQL is embedded in the application. At the moment, I don't have time to rewrite it as a stored procedure. (I will some day, given the chance.) But, I need to know what would cause this disparity in behavior and how to eliminate it. If it's happening here, it's likely happening somewhere else.

If anyone can suggest a good place to look, I'd be very appreciative.

Answer 1

Some random ideas:

• Are you sure you committed the changes that invalidate the ip-address? Can someone else (using another db connection / user) see the changed code_status?

• Are you sure that the results are not modified after they are returned from the database?

• Are you sure that you are using the "same" database connection in SQLPlus as in the code (database, user etc.)?

• Are you sure that that is indeed the SQL sent to the database? (You may check by tracing on the Oracle server or by debugging the VB code). Reformatting may have changed "something".

Off the top of my head I can't think of any "caching" that might "re-insert" the unwanted ip. Hope something from the above gives you some ideas on where to look at.

Answer 2

In addition to the suggestions that IronGoofy has made, have you tried swapping round the last two clauses?

where
   a.code_item = b.wich_id and
   a.code_status = 'R' and
   a.location_type_code = '15'

If you get a different set of results then this might point to some sort of wrangling going on that results in dodgy SQL actually be sent to the database.

Answer 3

There are Oracle bugs that result in incorrect answers. This surely isn't one of those times. Usually they involve some bizarre combination of views and functions and dblinks and lunar phases...

It's not cached anywhere. Oracle doesn't cache results until 11 and even then it knows to change the cache when the answer may change.

I would guess this is a data issue. You have a DISTINCT on the IP address in the query, why? If there's no unique constraint, there may be more than one copy of your IP address and you only fixed one of them.

And your Code_status is in a completely different table from your IP addresses. You set the status to "I" in the code table and you get the list of IPs from the Location table.

Stop thinking zebras and start thinking horses. This is almost certainly just data you do not fully understand.

Run this

select 
   a.location_type_code, 
   a.code_status 
from 
   code_table a, 
   location b 
where 
   a.code_item = b.which_id and 
   b.ip_address = <the one you think you fixed>

I bet you get one row with an 'I' and another row with an 'R'

Answer 4

I'd suggest you have a look at the V$SQL system view to confirm that the query you believe the VB6 code is running is actually the query it is running.

Something along the lines of

select sql_text, fetches
where sql_text like '%ip_address%'

Verify that the SQL_TEXT is the one you expect and that the FETCHES count goes up as you execute the code.