12 Replies Latest reply on Feb 17, 2020 10:14 AM by Rajneesh S-Oracle

    ORA-00932: inconsistent datatypes: expected - got CLOB

    Rajneesh S-Oracle

      Hello All,

       

      If I use NULLIF function for CLOB field, I am facing  ORA-00932 error as below:

       

      with T1 as (

        select

       

         (

          SELECT

                   RTRIM(XMLCAST(

                              XMLAGG(

                                      XMLELEMENT(E,'                 '||x.column_name||' as '||x.column_alias,','||chr(10))

                                       ORDER BY ORD_POS

                                     ) AS CLOB

                          ),','||CHR(10))

          FROM XMLTABLE (

                '/functionalView/columns/column'

                PASSING A.VIEWDEF

                COLUMNS

                  ORD_POS FOR ORDINALITY,

                  COLUMN_NAME  VARCHAR2(30) PATH '@name',

                  COLUMN_ALIAS VARCHAR2(30) PATH '@columnAlias'

              ) X

          ) SELECT_CLAUSE

       

        FROM XML_DATA A

        WHERE ROWNUM=1

      )

      SELECT

      nullif(select_clause,'COL1')

      from  T1;

       

      However above sql is working fine without nullif function as below:

       

      with T1 as (

        select

       

         (

          SELECT

                   RTRIM(XMLCAST(

                              XMLAGG(

                                      XMLELEMENT(E,'                 '||x.column_name||' as '||x.column_alias,','||chr(10))

                                       ORDER BY ORD_POS

                                     ) AS CLOB

                          ),','||CHR(10))

          FROM XMLTABLE (

                '/functionalView/columns/column'

                PASSING A.VIEWDEF

                COLUMNS

                  ORD_POS FOR ORDINALITY,

                  COLUMN_NAME  VARCHAR2(30) PATH '@name',

                  COLUMN_ALIAS VARCHAR2(30) PATH '@columnAlias'

              ) X

          ) SELECT_CLAUSE

       

        FROM XML_DATA A

        WHERE ROWNUM=1

      )

      SELECT

      select_clause

      from  T1;

       

      If I replace xmlagg as clob to listagg then there is no issue as below:

       

       

      with T1 as (

        select

       

         (

          select listagg(x.column_name||' as '||x.column_alias, ', ') within group (order by ord_pos)

          from xmltable (

                '/functionalView/columns/column'

                passing a.viewdef

                columns

                  ord_pos for ordinality,

                  column_name  varchar2(30) path '@name',

                  column_alias varchar2(30) path '@columnAlias'

              ) x

          ) select_clause

       

        FROM XML_DATA A

        WHERE ROWNUM=1

      )

      SELECT

      nullif(select_clause, 'col1')

      from  T1;

       

       

      I want to use xmlagg as CLOB only and is there a way that I can manage error due to nullif clause.

       

      Thanks,

      Rajneesh

        • 1. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
          mathguy

          Replace

           

          nullif(select_clause,'COL1')

           

          with

           

          case when select_clause like 'COL1' then null else select_clause end

          • 2. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
            Solomon Yakobson

            NULLIF can't handle CLOB. Use CASE:

             

            CASE

              WHEN select_clause = to_clob('col1') THEN CAST(NULL AS CLOB)

              ELSE select_clause

            END

             

            SY.

            • 3. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
              mathguy

              Solomon Yakobson wrote:

               

              NULLIF can't handle CLOB. Use CASE:

               

              CASE

              WHEN select_clause = to_clob('col1') THEN CAST(NULL AS CLOB)

              ELSE select_clause

              END

               

              SY.

               

               

              This, like the original solution you posted (now removed), will fail for the same reason, and the same reason NULLIF fails. CASE doesn't allow a CLOB in an equality comparison in the WHEN clause. Happily, LIKE doesn't have the same restriction.

               

              select case when to_clob('COL1') = to_clob('COL1') then null end from dual;

                                                *

               

              ORA-00932: inconsistent datatypes: expected - got CLOB

              00932. 00000 -  "inconsistent datatypes: expected %s got %s"

              *Cause:   

              *Action:

              Error at Line: 11 Column: 18

               

               

               

              select case when to_clob('COL1') like 'COL1' then null end from dual;

               

               

              CASEWHENTO_CLOB('COL1')LIKE'COL1'THENNULLEND

              --------------------------------------------

               

               

              (1 row selected)

               

              The latter also works with a CLOB as the right-hand side operand to LIKE.

              • 4. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                GregV

                If NULLIF cannot handle clobs then the documentation should state so. We don't know your version, but the 19c doc says:

                 

                https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NULLIF.html#GUID-445FC268-7FFA-4850-98C9-D53D88AB240…

                 

                if the arguments are not numeric, then they must be of the same data type, or Oracle returns an error.

                 

                 

                If both are CLOB, then according to the doc it should not return an error.

                • 5. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                  mathguy

                  The documentation doesn't show it, but if you try NULLIF(to_clob('COL1'), to_clob('COL1')) you will get the same error, at least in Oracle 12.2 (my version).

                   

                  The data types allowed in the WHEN clause of a simple CASE expression are stated explicitly in the documentation, and they don't include CLOB. The documentation for searched CASE doesn't say why "clob = other clob" is not allowed; perhaps the parser re-writes it as a simple CASE expression, and then it fails when comparing CLOB? (And then, the same would explain why NULLIF doesn't accept CLOB?)

                  • 6. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                    Paulzip

                    mathguy wrote:

                     

                    Replace

                     

                    nullif(select_clause,'COL1')

                     

                    with

                     

                    case when select_clause like 'COL1' then null else select_clause end

                    I don't agree with your approach at all.  You're assuming...

                    1. OP will always be using the input 'COL1', what if it actually parameterised and the input contains wildcards?  e.g. 'COL1%' or 'COL_123'?

                    2. LIKE will always support CLOBs in all Oracle versions and in the manner you're intending.

                    3. LIKE isn't truncating during comparison.

                     

                    The correct approach is to use the LOB comparison function Oracle have specifically provided, which is also optimised for LOB comparisons

                     

                    case when DBMS_LOB.Compare(select_clause, 'COL1') = 0 then null else select_clause end

                    1 person found this helpful
                    • 7. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                      mathguy

                      You don't agree with my approach at all? Not even a little bit? Not even in the context of all the other attempts offered in this thread? You are hurting my feelings!

                       

                      Judging by your suggestion, though, you do agree with it somewhat. You do agree that we need a CASE expression, NULLIF won't work. You do agree that a simple CASE expression won't work. And you do agree that in it we can't use equality of CLOB, and instead we need a searched CASE expression with a logical condition other than equality of CLOB. I wouldn't call that "at all".

                       

                      Then, regarding your objections:  (1) If the RHS has % or _ in it, we can use the ESCAPE clause of LIKE (that's precisely why that clause exists). Besides, the OP may, in fact, at some point, want to use LIKE with wildcard characters; I don't know if DBMS_LOB.COMPARE can do that. REGEXP_LIKE can, if LIKE is not OK to use. (3) doesn't even make sense if the RHS is strictly less than 4000 characters; LIKE will be true for the full CLOB if and only if it is true for the CLOB truncated to 4000 characters. (Or - rephrase that in bytes, for multi-byte character sets.) And, in any case, CLOB's are not truncated in the LIKE condition, as shown below.

                       

                      The only valid objection is that the documentation of LIKE doesn't seem to allow CLOB. I don't know if this is a documentation flaw, or if in fact CLOB in LIKE is not meant to be used. (I also didn't check yet to see if CLOB in LIKE is in fact documented elsewhere, but not in the "obvious place"; that happens often with Oracle documentation). In any case, LIKE does work with CLOB, and it does not truncate.

                       

                      Nothing wrong with using DBMS_LOB.COMPARE, of course; but your comments about my approach don't seem to hold much water.

                       

                      Test to show that LIKE does not truncate CLOB, on either side:

                       

                      with

                        t (cl1, cl2) as (

                          select to_clob('x') || lpad('x', 3999, 'x'), to_clob('x') || lpad('x', 4000, 'x')

                          from   dual

                        )

                      select length(cl1), length(cl2),

                             case when cl1 like cl2 then 'Y' else 'N' end as rhs_is_truncated,

                             case when cl2 like cl1 then 'Y' else 'N' end as lhs_is_truncated

                      from   t

                      ;

                       

                      LENGTH(CL1) LENGTH(CL2) RHS_IS_TRUNCATED     LHS_IS_TRUNCATED  

                      ----------- ----------- -------------------- --------------------

                             4000        4001 N                    N                

                      1 person found this helpful
                      • 8. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                        Solomon Yakobson

                        Just for fun:

                         

                        REGEXP_REPLACE(SELECT_CLAUSE,'^COL1$')

                         

                        SY.

                        • 9. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                          odie_63

                          mathguy wrote:

                           

                          In any case, LIKE does work with CLOB, and it does not truncate.

                          I wouldn't be so affirmative.

                           

                          Let's see what happens with some other CLOB sizes :

                           

                          with t (cl1, cl2) as (
                            select lpad(to_clob('x'), 100000, 'x')
                                 , lpad(to_clob('x'), 32000, 'x')
                            from dual
                          )
                          select length(cl1), length(cl2),
                                 case when cl1 like cl2 then 'Y' else 'N' end as "CL1 = CL2"
                          from t
                          ;
                          
                          LENGTH(CL1) LENGTH(CL2) CL1 = CL2
                          ----------- ----------- ---------
                               100000       32000 Y
                          
                          

                           

                          Boundary size seems to be 31999 for the RHS operand, as shown below :

                           

                          with t (cl1, cl2) as (
                            select lpad(to_clob('x'), 100000, 'x')
                                 , lpad(to_clob('x'), 31999, 'x')
                            from dual
                          )
                          select length(cl1), length(cl2),
                                 case when cl1 like cl2 then 'Y' else 'N' end as "CL1 = CL2"
                          from t
                          ;
                          
                          LENGTH(CL1) LENGTH(CL2) CL1 = CL2
                          ----------- ----------- ---------
                               100000       31999 N
                          
                          

                           

                          Not equal in that case.

                           

                          Funny thing is that the comparison is apparently done from right to left, e.g. after appending a character to both operands :

                           

                          with t (cl1, cl2) as (
                            select lpad(to_clob('x'), 100000, 'x') || 'a'
                                 , lpad(to_clob('x'), 31999, 'x') || 'a'
                            from dual
                          )
                          select length(cl1), length(cl2),
                                 case when cl1 like cl2 then 'Y' else 'N' end as "CL1 = CL2"
                          from t
                          ;
                          
                          LENGTH(CL1) LENGTH(CL2) CL1 = CL2
                          ----------- ----------- ---------
                               100001       32000 Y
                          
                          

                           

                          Unexpectedly equal again.

                           

                          And if the size of the RHS operand exceeds 32767 :

                           

                          with t (cl1, cl2) as (
                            select lpad(to_clob('x'), 100000, 'x')
                                 , lpad(to_clob('x'), 32768, 'x')
                            from dual
                          )
                          select length(cl1), length(cl2),
                                 case when cl1 like cl2 then 'Y' else 'N' end as "CL1 = CL2"
                          from t
                          
                          ORA-22828: input pattern or replacement parameters exceed 32K size limit
                          
                          

                           

                           

                          Back to OP's question, I think the best solution requires knowing what this 'COL1' could be in reality.

                          If it's always a value in the VARCHAR2 range, then it's probably OK to use LIKE, though I personally wouldn't rely on undocumented feature (or bug, depending on the POV).

                          If it could be an arbitrary CLOB, then DBMS_LOB.COMPARE is the safest option.

                          • 10. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                            mathguy

                            I do agree that DBMS_LOB.* is the safe way to go for CLOBs. My reply to Paulzip was more about the editorial comment than the actual content.

                             

                            With that said:

                             

                            (1) If I understand the OP's attempted code, he's building an aggregate of column names, and he is checking to see that the list doesn't simply consist of a single, specific column name. So the RHS is limited to a small number of characters.

                             

                            (2) What you show, about truncating CLOBs in the LIKE condition, seems version-specific. (Which still shouldn't be relied on in any manner, if not documented... but in our case, if the RHS is relatively short VARCHAR2, then NO truncation of the LHS will lead to wrong results). For example, on my machine I get

                             

                            with t (cl1, cl2) as (

                              select lpad(to_clob('x'), 100000, 'x')

                                  , lpad(to_clob('x'), 32000, 'x')

                              from dual

                            )

                            select length(cl1), length(cl2),

                                  case when cl1 like cl2 then 'Y' else 'N' end as "CL1 = CL2"

                            from t

                            ;

                             

                            LENGTH(CL1) LENGTH(CL2) CL1 = CL2

                            ----------- ----------- ---------

                                 100000       32000         N

                             

                            (Where you got Y)  My version is 12.2.0.1.  And CL2 can't go past 32,767 bytes.

                            1 person found this helpful
                            • 11. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                              odie_63

                              mathguy wrote:

                               

                              (2) What you show, about truncating CLOBs in the LIKE condition, seems version-specific.

                              [...]

                              My version is 12.2.0.1.

                              Yes, forgot to mention the db version in my post : it's 12.1.0.2.

                               

                              I confirm it works too on 18.4.

                               

                              And if the RHS is indeed always a VARCHAR2, then the following is another option :

                              case when length(LHS) = length(RHS) and dbms_lob.substr(LHS) = RHS then null else LHS end
                              1 person found this helpful
                              • 12. Re: ORA-00932: inconsistent datatypes: expected - got CLOB
                                Rajneesh S-Oracle

                                Thank you all,

                                 

                                My problem got solved, constructive confrontation is characteristic of good discussion and I hearty thanks to each and every one of you for your valuable input.

                                 

                                 

                                Regards,

                                Rajneesh