Re: Select ?

I would suggest to read the documentation. You changed the original question now you want to see something else.

SQL> CREATE TABLE test_table (id NUMBER, testcolu CLOB);

Table created.

SQL> INSERT INTO test_table VALUES(1, 'test');

1 row created.

SQL> INSERT INTO test_table VALUES(2, ' ');

1 row created.

SQL> INSERT INTO test_table VALUES(3, NULL);

1 row created.

SQL> INSERT INTO test_table VALUES(4, EMPTY_CLOB());

1 row created.

SQL> COMMIT;    1 SELECT id, testcolu
   2 FROM test_table
   3 WHERE dbms_lob.getlength(testcolu) = 0    4* OR (dbms_lob.getlength(testcolu) = 1 AND dbms_lob.instr(testcolu, ' ') = 1) SQL> /         ID TESTCOLU

--------- --------------------------------------------------------------------------
         2
         4

If the columns updated to ' ' it can not show NULL during select.

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Seema Singh wrote:

> When I used query SELECT id FROM test_table WHERE LENGTH(testcolu) = 0;
> I received error like
> ORA-00932: inconsistent datatypes
> I'm on 8163.
> Let me know if you have any thoughts.
> I want to findout those columns which are updated thru empty_clob() 
> functions or those columns which are showing NULL during select but 
> those columns were updated either thru EMPTY_CLOB() or ' '.?
> thx- Seema
> 
> 


>> From: Vladimir Begun <Vladimir.Begun_at_oracle.com>

>> Reply-To: ORACLE-L_at_fatcity.com

>> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

>> Subject: Re: Select ?

>> Date: Mon, 10 Nov 2003 10:19:25 -0800

>>

>> SELECT id FROM test_table WHERE LENGTH(testcolu) = 0;

>> -- 

>> Vladimir Begun

>> The statements and opinions expressed here are my own and

>> do not necessarily represent those of Oracle Corporation.

>>

>> Seema Singh wrote:

>>

>>> What SQL I have to use.Is there any way can i know what are those 

>>> columns were updated thru EMPTY_CLOB() function?

>>

>>

>>

>> -- 

>> Please see the official ORACLE-L FAQ: http://www.orafaq.net

>> -- 

>> Author: Vladimir Begun

>>  INET: Vladimir.Begun_at_oracle.com

>>

>> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

>> San Diego, California        -- Mailing list and web hosting services

>> ---------------------------------------------------------------------

>> To REMOVE yourself from this mailing list, send an E-Mail message

>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

>> the message BODY, include a line containing: UNSUB ORACLE-L

>> (or the name of mailing list you want to be removed from).  You may

>> also send the HELP command for other information (like subscribing).

> 
> 
> _________________________________________________________________
> MSN Shopping upgraded for the holidays!  Snappier product search... 
> http://shopping.msn.com
> 



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).