This discussion is archived
10 Replies Latest reply: Oct 11, 2012 1:29 PM by GabyPR RSS

default for clob

newbiegal Newbie
Currently Being Moderated
Hi friends,

I'm getting the below error when I use where clause in clob field.. mname is a clob field with default ' '
SQL>select * from test where mname=' ';
select * from test where mname=' '
                                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
Thanks a lot
  • 1. Re: default for clob
    phaeus Pro
    Currently Being Moderated
    Hello,
    try to convert your clob during select in varchar.

    SELECT * FROM test WHERE to_char(mname) = ' ';

    I dont think it is a good idea to do a clob in this manner directly in the where clause.

    regards
    Peter
  • 2. Re: default for clob
    Solomon Yakobson Guru
    Currently Being Moderated
    SQL doesn't know how to compare CLOBs. Use DBMS_LOB.COMPARE:
    SQL> create table test(
      2                    id number,
      3                    mname clob
      4                   )
      5  /
    
    Table created.
    
    SQL> insert
      2    into test
      3    values(
      4           1,
      5           ' '
      6          )
      7  /
    
    1 row created.
    
    SQL> insert
      2    into test
      3    values(
      4           2,
      5           'abc'
      6          )
      7  /
    
    1 row created.
    
    SQL> select  *
      2    from  test
      3    where mname = ' '
      4  /
      where mname = ' '
            *
    ERROR at line 3:
    ORA-00932: inconsistent datatypes: expected - got CLOB
    
    
    SQL> select  *
      2    from  test
      3    where dbms_lob.compare(mname,' ') = 0
      4  /
    
            ID MNAME
    ---------- ----------
             1
    SY.

    Edited by: Solomon Yakobson on Oct 10, 2012 3:47 PM
  • 3. Re: default for clob
    newbiegal Newbie
    Currently Being Moderated
    Thanks so much
  • 4. Re: default for clob
    933475 Newbie
    Currently Being Moderated
    dbms_lob.compare(mname,' ') is the only way.
  • 5. Re: default for clob
    Nimish Garg Guru
    Currently Being Moderated
    i believe it can be also done as below (not tested)
    select * from test where mname=to_clob(' ');
  • 6. Re: default for clob
    jeneesh Guru
    Currently Being Moderated
    Nimish Garg wrote:
    i believe it can be also done as below (not tested)
    select * from test where mname=to_clob(' ');
    If you test it, you will get the error message
    ORA-00932: inconsistent datatypes: expected - got CLOB
  • 7. Re: default for clob
    Nimish Garg Guru
    Currently Being Moderated
    then try this

    select * from test where to_char(substr(mname,1,1))=' ' and length(mname) = 1;

    * length must be added as well in where clause

    Edited by: Nimish Garg on Oct 11, 2012 10:14 AM
  • 8. Re: default for clob
    jeneesh Guru
    Currently Being Moderated
    Nimish Garg wrote:
    then try this

    select * from test where to_char(substr(mname,1,1))=' ';
    This will for sure return all those rows starting with ' '.

    It is not same as giving "mname= ' '", it is same as "mname like ' %'"

    Anyhow, I dont want an answer, I was just pointing out that what you were trying to do is wrong...
  • 9. Re: default for clob
    jeneesh Guru
    Currently Being Moderated
    Nimish Garg wrote:
    then try this

    select * from test where to_char(substr(mname,1,1))=' ' and length(mname) = 1;

    * length must be added as well in where clause

    Edited by: Nimish Garg on Oct 11, 2012 10:14 AM
    that looks OK :) ... It can be made better, may be
    to_char(substr(mname,1,2))=' '
    :)
  • 10. Re: default for clob
    GabyPR Newbie
    Currently Being Moderated
    newbiegal can I contact you by e-mail about a question related to other post of yours ? please reply
    you may contact me at: gaby@writeme.com , but reply here first so I know you sent me an e-mail.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points