Home » SQL & PL/SQL » SQL & PL/SQL » SQL Converting a varchar to a clob within an SQL union
SQL Converting a varchar to a clob within an SQL union [message #577382] Thu, 14 February 2013 11:59 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
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.
Re: SQL Converting a varchar to a clob within an SQL union [message #577383 is a reply to message #577382] Thu, 14 February 2013 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries004.htm#SQLRF52341

what is said about restrictions on UNION?
Re: SQL Converting a varchar to a clob within an SQL union [message #577393 is a reply to message #577382] Thu, 14 February 2013 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, wm_concat is an undocumented function and so you cannot assume anything and Oracle is ALWAYS right.

Regards
Michel
Re: SQL Converting a varchar to a clob within an SQL union [message #577401 is a reply to message #577382] Thu, 14 February 2013 13:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
ziggy25 wrote on Thu, 14 February 2013 12:59
Why does oracle not allow the following query


Because UNION implies DISTINCT which is not allowed for CLOBS. Meditate over:

SQL> select to_clob(1) from dual
  2  union
  3  select to_clob(1) from dual
  4  /
select to_clob(1) from dual
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB


SQL> select to_clob(1) from dual
  2  union all
  3  select to_clob(1) from dual
  4  /

TO_CLOB(1)
-----------------------------------------------------------------
1
1

SQL> 


SY.
Re: SQL Converting a varchar to a clob within an SQL union [message #577403 is a reply to message #577383] Thu, 14 February 2013 14:03 Go to previous message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
BlackSwan wrote on Thu, 14 February 2013 13:02
what is said about restrictions on UNION?


It lies. It should say something like: Since comparison opertion is not allowed for LOBs, any LOB set operation which requires comparison is not allowed. Since UNION ALL does not need comparison it works for LOBs. Same applies to BFILE VARRAY, and nested table:

SQL> desc NumList
 NumList TABLE OF NUMBER

SQL>  select NumList(1) from dual
  2  union
  3   select NumList(1) from dual
  4  /
 select NumList(1) from dual
        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got SCOTT.NUMLIST


SQL>  select NumList(1) from dual
  2  union all
  3   select NumList(1) from dual
  4  /

NUMLIST(1)
------------------------------------------------------------------------------
NUMLIST(1)
NUMLIST(1)

SQL>  select sys.OdciNumberList(1) from dual
  2  union
  3   select sys.OdciNumberList(1) from dual
  4  /
 select sys.OdciNumberList(1) from dual
            *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got SYS.ODCINUMBERLIST


SQL>  select sys.OdciNumberList(1) from dual
  2  union all
  3   select sys.OdciNumberList(1) from dual
  4  /

SYS.ODCINUMBERLIST(1)
--------------------------------------------------------------------------------
ODCINUMBERLIST(1)
ODCINUMBERLIST(1)

SQL>  select b from tbl
  2  union
  3   select b from tbl
  4  /
 select b from tbl
        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got FILE


SQL>  select b from tbl
  2  union all
  3   select b from tbl
  4  /
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> 


SY.

[Updated on: Thu, 14 February 2013 14:18]

Report message to a moderator

Previous Topic: External Tables (Oracle Loader)
Next Topic: Using Collections
Goto Forum:
  


Current Time: Thu Oct 02 03:54:35 CDT 2014

Total time taken to generate the page: 0.11526 seconds