10 Replies Latest reply: Oct 11, 2012 3:29 PM by GabyPR RSS

    default for clob

    newbiegal
      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
          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
            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
              Thanks so much
              • 4. Re: default for clob
                933475
                dbms_lob.compare(mname,' ') is the only way.
                • 5. Re: default for clob
                  Nimish Garg
                  i believe it can be also done as below (not tested)
                  select * from test where mname=to_clob(' ');
                  • 6. Re: default for clob
                    jeneesh
                    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
                      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
                        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
                          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
                            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.