Re: Select problem

You said: "If I populate all the columns in that record with at least empty string..."

Daniel was exactly correct - if you are working with a database that treats empty strings differently from NULL, then you are not working with Oracle.

The problem is that your words in your opening post do not describe your situation. Your working example is *NOT* insterting an empty string (a string of length 0). It inserting a non-empty string of length 1, populated with a single space character.

If you want an insult, just keep on getting defensive when people aren't able to discern what you mean from what you type...

SQL> SELECT COUNT(*) FROM (SELECT '' C1 FROM DUAL) WHERE C1 IS NULL   2 /

 COUNT(*)

        1

SQL> SELECT COUNT(*) FROM (SELECT ' ' C1 FROM DUAL) WHERE C1 IS NULL   2 /

 COUNT(*)

        0

DA Morgan wrote:
> 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 - 12:44:05 CST