oracle创建视图包含clob字段,报错:数据类型不一致:应为-,但却获得CLOB

在oracle中创建视图的时候,语句中包含有clob类型的字段。视图创建语句大概如下:

CREATE OR REPLACE FORCE VIEW "T_PROJECTS" ("ID", "NAME","DSC_INFO") AS
(
select a.id,a.name,'' DSC_INFO from t_project_a a
union
select b.id,b.name,b.dsc_info from t_project_b b
union
select c.id,c.name,c.dsc_info from t_project_c c
);
但在执行的时候却报错了,错误为:
数据类型不一致:应为-,但却获得CLOB
检查以后发现,dsc_info字段为clob类型,起初以为是clob类型初始化的问题,于是把语句改为
CREATE OR REPLACE FORCE VIEW "T_PROJECTS" ("ID", "NAME","DSC_INFO") AS
(
select a.id,a.name,empty_clob() DSC_INFO from t_project_a a
union
select b.id,b.name,b.dsc_info from t_project_b b
union
select c.id,c.name,c.dsc_info from t_project_c c
);
执行仍然报错。然后查资料:oracle中clob实际上是使用char来存储数据的,所以在对clob字段进行查询时为了保证正确应该对字段进行转换,转换成char,并给字段增加别名。转换成char的函数为:to_char(clob字段名)。最后,sql改为:
CREATE OR REPLACE FORCE VIEW "T_PROJECTS" ("ID", "NAME","DSC_INFO") AS
(
select a.id,a.name,'' DSC_INFO from t_project_a a
union
select b.id,b.name,to_char(b.dsc_info) DSC_INFO from t_project_b b
union
select c.id,c.name,to_char(c.dsc_info) DSC_INFO from t_project_c c
);
执行成功,以作记录。

————————————————
版权声明:本文为CSDN博主「southArbor」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/u010295735/article/details/79215441

上一篇:Windows运维之道——PXE备份系统


下一篇:Android开源代码解读のOnScrollListener实现ListView滚屏时不加载数据