Forum Stats

  • 3,769,696 Users
  • 2,253,010 Discussions
  • 7,875,155 Comments

Discussions

NVL Function on CLOB data type

Rede
Rede Member Posts: 105
edited Aug 5, 2011 3:19PM in SQL & PL/SQL
Hi all,

I am not able to use the NVL function over clob..please suggest me a way to do the data validation of the CLOB in sql ....

below is the query i was using...
select t1.pic_name,t2.pic_name
from table1 t1,table2 t2
where nvl(t1.picture_message,'X') <> nvl(t2.picture_message,'X');
i am getting the following error....
ORA-00932: inconsistent datatypes: expected - got CLOB
Thanks
Rede

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Aug 5, 2011 1:37PM
    Maybe you could try something like this:
    SELECT t1.pic_name
         , t2.pic_name
    FROM   table1 t1
         , table2 t2
    WHERE  DBMS_LOB.COMPARE(t1.picture_message, t2.picture_message) != 0
    ;
    My assumption is that you want to return rows where the CLOB values from the picture_message column of both tables are not equal.
  • bpat
    bpat Member Posts: 332
    edited Aug 5, 2011 1:44PM
    You can use CAST as well
    create table t_clob1
    (
    pic_name varchar2(20),
    pic_message clob
    );
    
    create table t_clob2
    (
    pic_name varchar2(20),
    pic_message clob
    );
    
    insert into t_clob1
    (
    select 'A','ABC' from dual union all
    select 'B','ASDS' from dual union all
    select 'C',null from dual
    );
    
    insert into t_clob2
    (
    select 'A','ABC' from dual union all
    select 'B','ASDS' from dual union all
    select 'C',null from dual
    );
    
    SQL> select t1.pic_name,t2.pic_name
    from t_clob1 t1,t_clob2 t2
    where nvl(CAST(t1.pic_message as VARCHAR2(4000)),'X') = nvl(CAST(t2.pic_message as VARCHAR2(4000)),'X'); 
    
    PIC_NAME             PIC_NAME
    -------------------- --------------------
    A                    A
    B                    B
    C                    C
    
    3 rows selected.
    
    Elapsed: 00:00:00.00
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    bpat wrote:
    You can use CAST as well
    What about in the case of a CLOB being larger than what VARCHAR2 can handle? I believe Oracle truncates it. This is just something to watch out for.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,921 Red Diamond
    Your problem is not NVL but rather comparing CLOBs:
    SQL> desc tbl1
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                                 NUMBER
     X                                                  CLOB
    
    SQL> desc tbl2
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                                 NUMBER
     X                                                  CLOB
    
    SQL> select  nvl(x,'NULL CLOB')
      2    from  tbl1
      3  /
    
    NVL(X,'NULLCLOB')
    --------------------------------------------------------------------------------
    NULL CLOB
    
    SQL> select  *
      2    from  tbl1,
      3          tbl2
      4    where tbl1.x = tbl2.x
      5  /
      where tbl1.x = tbl2.x
            *
    ERROR at line 4:
    ORA-00932: inconsistent datatypes: expected - got CLOB
    
    
    SQL> 
    SY.
  • bpat
    bpat Member Posts: 332
    You are right.
    I consider the solution given by you is more appropriate.
    Should we add NVL condition for handling null values as well

    Without NVL :
    SQL> select t1.pic_name,t2.pic_name
    from t_clob1 t1,t_clob2 t2
    where DBMS_LOB.COMPARE(t1.pic_message, t2.pic_message) = 0
    ;
    
    PIC_NAME             PIC_NAME
    -------------------- --------------------
    A                    A
    B                    B
    
    2 rows selected.
    
    Elapsed: 00:00:00.02
    SQL>
    With NVL :
    SQL> select t1.pic_name,t2.pic_name
    from t_clob1 t1,t_clob2 t2
    where DBMS_LOB.COMPARE(nvl(t1.pic_message,'X'), nvl(t2.pic_message,'X')) = 0
    ;
    
    PIC_NAME             PIC_NAME
    -------------------- --------------------
    A                    A
    B                    B
    C                    C
    
    3 rows selected.
    
    Elapsed: 00:00:00.01
    SQL>
    I consider it depends on the requirement whether to use NVL or not.
    But I don't know why in this case we do not receive any inconsistent datatype error
  • user130038
    user130038 Member Posts: 813
    edited Aug 5, 2011 2:05PM
    >
    But I don't know why in this case we do not receive any inconsistent datatype error
    >

    As Solomon mentioned above, the problem is not with NVL but with the way you were comparing CLOBs.
  • bpat
    bpat Member Posts: 332
    Thanks.
    I overlooked the part provided by Solomon
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    bpat wrote:
    You are right.
    I consider the solution given by you is more appropriate.
    Should we add NVL condition for handling null values as well
    I'm not sure because the OPs post is incomplete. I think they had an != operator but the forum software stripped it. If that's the case than I believe the NVL() is not needed because they had NVL(..,'X') != NVL(...,'X') If they are both null then 'X' != 'X' evaluates to false the same way as DBMS_LOB.COMPARE(null,null) != 0 would.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,921 Red Diamond
    edited Aug 5, 2011 3:19PM
    Centinul wrote:

    I'm not sure because the OPs post is incomplete. I think they had an != operator but the forum software stripped it. If that's the case than I believe the NVL() is not needed because they had NVL(..,'X') != NVL(...,'X') If they are both null then 'X' != 'X' evaluates to false the same way as DBMS_LOB.COMPARE(null,null) != 0 would.
    Not exactly.
    DBMS_LOB.COMPARE(t1.picture_message, t2.picture_message) != 0
    will not return rows where pic_message in one table is null and in the other is not (and is not 'X'). While
    DBMS_LOB.COMPARE(nvl(t1.picture_message,'X'),nvl(t2.picture_message,'X')) != 0
    will.

    SY.
This discussion has been closed.