CODEWITHSUNDEEP
sqloracle-database
ziggy
ziggy

Reputation: 15876

SQL Converting a varchar to a clob within an SQL union

Why does oracle not allow the following query

select to_clob(1) from dual
union
select wm_concat(sysdate) from dual;

wm_concat returns a clob. To make both queries in the union have the same type columns i convert the column in the first query to a clob but Oracle gives me an [1]: ORA-00932: inconsistent datatypes: expected - got CLOB error even though both are returning a clob value.

Each of the queries work individually and both return a clob value.

Upvotes: 2

Views: 15557

Answers (1)

Justin Cave
Justin Cave

Reputation: 231831

I don't believe wm_concat returns a CLOB.

This shows that the return is Typ=1 which is a VARCHAR2

SQL> select dump(wm_concat(sysdate)) from dual;

DUMP(WM_CONCAT(SYSDATE))
--------------------------------------------------------------------------------
Typ=1 Len=9: 49,52,45,70,69,66,45,49,51

which you can also see if you create a view

SQL> ed
Wrote file afiedt.buf

  1  create view vw_wm_concat
  2  as
  3* select wm_concat(sysdate) col from dual
SQL> /

View created.

SQL> desc vw_wm_concat;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL                                                VARCHAR2(4000)

If you convert the VARCHAR2 returned by WM_CONCAT into a CLOB, the next problem is that Oracle doesn't support doing a DISTINCT on a CLOB column which is required in order to do a UNION. Assuming that you don't really need to remove duplicate rows, you can use a UNION ALL rather than a UNION.

Putting the two together, something like this

SQL> ed
Wrote file afiedt.buf

  1   select to_clob(1) from dual
  2   union all
  3* select to_clob(wm_concat(sysdate)) col from dual
SQL> /

TO_CLOB(1)
------------------------------------------------------------
1
14-FEB-13

will work

Upvotes: 4

Related Questions