Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00932: inconsistent datatypes: expected - got CLOB

Rajneesh S-OracleFeb 14 2020 — edited Feb 17 2020

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;

pastedImage_0.png

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;

pastedImage_1.png

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;

pastedImage_2.png

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

Thanks,

Rajneesh

This post has been answered by Paulzip on Feb 14 2020
Jump to Answer
Comments
Post Details
Added on Feb 14 2020
12 comments
45,817 views