oracle SQL 在 SQL 联合中将 varchar 转换为 clob
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14880930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Converting a varchar to a clob within an SQL union
提问by ziggy
Why does oracle not allow the following query
为什么oracle不允许下面的查询
select to_clob(1) from dual
union
select wm_concat(sysdate) from dual;
wm_concatreturns 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 CLOBerror even though both are returning a clob value.
wm_concat返回一个clob。为了使联合中的两个查询具有相同类型的列，我将第一个查询中的列转换为 clob，但[1]: ORA-00932: inconsistent datatypes: expected - got CLOB即使两者都返回 clob 值，Oracle 还是给了我一个错误。
Each of the queries work individually and both return a clob value.
每个查询都单独工作，并且都返回一个 clob 值。
回答by Justin Cave
I don't believe wm_concatreturns a CLOB.
我不相信wm_concat返回一个CLOB.
This shows that the return is Typ=1which is a VARCHAR2
这表明，返回的是Typ=1这是一个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 VARCHAR2returned by WM_CONCATinto a CLOB, the next problem is that Oracle doesn't support doing a DISTINCTon a CLOBcolumn 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 ALLrather than a UNION.
如果将VARCHAR2返回的 byWM_CONCAT转换为 a CLOB，则下一个问题是 Oracle 不支持对执行 a所需DISTINCT的CLOB列执行 a UNION。假设您真的不需要删除重复的行，您可以使用 aUNION ALL而不是 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
将工作
