从视图创建表-Oracle SQL SQL错误:ORA-01723:不允许长度为零的列 [英] Create Table from View - Oracle SQL SQL Error: ORA-01723: zero-length columns are not allowed

问题描述

我需要使用

CREATE  TABLE NEW_TABLE AS
    SELECT *
    from VIEW

它给出如下错误信息.无法从视图创建表(使用Select *语句)?

It is giving error message as below. It is not possible to create table from view (with Select * statement)?

Error report -
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 -  "zero-length columns are not allowed

推荐答案

为列指定NULL却未指定类型时出现此问题.啊！

I have this problem when NULL is specified for a column, but the type is not specified. Arrgh!

您将需要查看代码.当我使用时通常会发生这种情况:

You will need to look at the code. This often happens when I use:

select '' as x

因为我认为''应该具有正确的类型.

Because I think '' should have the right type.

无论如何，解决方案都很简单:

In any case, the solution is simple:

select cast(NULL as varchar2(255)),
       cast(NULL as number)

或任何类型.

您将需要更改视图定义，或将查询与带有显式强制转换的子查询一起使用.

You'll need to either change the view definition, or use a query with a subquery with explicit casts.

这篇关于从视图创建表-Oracle SQL SQL错误:ORA-01723:不允许长度为零的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！