| SQL Converting a varchar to a clob within an SQL union [message #577382] |
Thu, 14 February 2013 11:59  |
ziggy25
Messages: 205
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 #577401 is a reply to message #577382] |
Thu, 14 February 2013 13:50  |
Solomon Yakobson
Messages: 1397
Registered: January 2010
|
Senior Member |
|
|
ziggy25 wrote on Thu, 14 February 2013 12:59Why 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 |
Solomon Yakobson
Messages: 1397
Registered: January 2010
|
Senior Member |
|
|
BlackSwan wrote on Thu, 14 February 2013 13:02what 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 |
|
|
|
] 
Members
Search
Help
Register
Login
Home
