Re: Select problem

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Tue, 01 Mar 2005 09:30:43 -0800
Message-ID: <1109698056.409527@yasure>

blue wrote:

> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1109629866.798897_at_yasure...
>

>>blue wrote:
>>
>>
>>>We have a customer using our software with an Oracle 9.2.0.4 database. 
>>>Select statements do not return any records where at least one column has 
>>>a NULL value.  We're talking about basic selects to such as SELECT * FROM 
>>>batch_schedule  WHERE status = 'A'.  If I populate all the columns in 
>>>that record with at least empty string, then the record is returned.  The 
>>>customer also informed me that he's had this trouble with other software 
>>>running against his Oracle DB as well, but didn't know why.
>>>
>>>Running the same code in our test environment here with an Oracle 9.2.0.1 
>>>database, all works as planned.
>>>
>>>Is there some kind of Oracle setting for this?
>>>
>>>Thanks for your help.
>>>Frank
>>
>>You are not working with Oracle if you can enter an empty string and get a 
>>behaviour different from NULL. Please post the appropriate DDL and DML
>>so that we can see what it is you are doing.

>
>
> We ARE working with Oracle as our backend database. Our software is written
> in Java and we use a JDBC driver to connect to an Oracle 9.2.0.4 database.
> If I create a record using this statement:
>
> Insert into batch_session
> (batch_id,group_id,environment,batch_name,curr_status) values
> ('DevBatch','admin','Development','DevBatch','I');
>
> And then run "SELECT * FROM batch_session WHERE environment =
> 'Development'", this record is NOT returned as part of the result set.
>
> If I remove that record and recreate using:
> INSERT INTO BATCH_SESSION (batch_id, group_id, environment, batch_name,
> curr_status, pend_status, times_run, curr_queue, curr_action, last_activity,
> last_queue, last_start, last_stop, last_term, last_log_id, last_detail_id,
> last_process_id, last_record_cnt, last_error_cnt) VALUES
> ('DevBatch','admin','Development','DevBatch','I',' ',0,0,' ','22-FEB-05',0,
> '22-FEB-05', '22-FEB-05',' ',0,0,' ',0,0)
>
> ..where all columns are populated with some kind of default data, then the
> record is returned by the exact same select as above.
>
> If I use SQL+ and use the same select after either insert, the record is
> returned just fine.
>
> Thank you for your time Daniel, and thanks for the insult as well.
> Frank
>
>

>>-- 
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond) 

What insult? Oracle can not distinguish, by design, between closed quotes and NULL and your post specifically stated the behaviors are different.

The issue you have has nothing to do with Oracle and everything to do with the driver you are using.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Tue Mar 01 2005 - 11:30:43 CST