oracle 不允许零长度列 - 从视图创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16941680/
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
zero-length columns are not allowed - Creating a table from view
提问by Naga
I designed a process to copy data from views into corresponding tables in oracle. All I do is call a procedure passing 'view name' as parameter and this creates corresponding table (if not exists else drop and creates the table). So this happens dynamically and I have around 50 views, and they are scheduled as oracle jobs.
我设计了一个过程，将视图中的数据复制到oracle 中的相应表中。我所做的就是调用一个传递“视图名称”作为参数的过程，这会创建相应的表（如果不存在，则删除并创建表）。所以这是动态发生的，我有大约 50 个视图，它们被安排为 oracle 作业。
Now coming to issue I have a few tables failing with below error on some days...
现在问题来了，我有几张表在某些日子失败并出现以下错误...
ORA-01723: zero-length columns are not allowed
ORA-01723: 不允许零长度列
I am aware the reason for this is a couple of columns in the view being null, but not on all days. Yes I should be using a CAST for those columns, but as I mentioned this happens dynamically I have no pre idea which those columns are or which view is that? Any leads to identify if there are "zero-length columns" in view before I start creating table so that I can think of some solution. Any better remedy for this is really appreciated.
我知道这样做的原因是视图中的几列为空，但并非整天都如此。是的，我应该对这些列使用 CAST，但正如我提到的，这是动态发生的，我不知道这些列是哪些或哪个视图？在我开始创建表之前确定是否有“零长度列”的任何线索，以便我可以想到一些解决方案。任何更好的补救措施都非常感谢。
Note:
笔记：
- CREATE TABLE TABLE_NAME AS SELECT * FROM VIEW_NAME --> This is my table creation SQL.
- The reason I choose this is 'INSERT INTO' may take more time and resource due to logging and locking.
- CREATE TABLE TABLE_NAME AS SELECT * FROM VIEW_NAME --> 这是我的表创建 SQL。
- 我选择它的原因是由于日志记录和锁定，“插入”可能需要更多的时间和资源。
Thanks, Naga'
谢谢，娜迦
回答by suPPLer
As you said you call a procedure to create a table from view. So I assume that you already are using dynamic SQL (Native Dynamic SQL aka EXECUTE IMMEDIATEstatement or DBMS_SQLpackage) to execute DDL statements. Then you could generate more complicated CREATE TABLE ASstatement with using the Oracle dictionary views such as USER_VIEWSand USER_TAB_COLUMNSto get info about columns' types, length, scale and whatever else you may need to write a right CAST call.
正如你所说，你调用一个过程来从视图中创建一个表。所以我假设您已经在使用动态 SQL（本机动态 SQL 又名EXECUTE IMMEDIATE语句或DBMS_SQL包）来执行 DDL 语句。然后，您可以CREATE TABLE AS使用 Oracle 字典视图（例如USER_VIEWS和USER_TAB_COLUMNS）生成更复杂的语句，以获取有关列类型、长度、比例以及编写正确 CAST 调用所需的任何其他信息。
A dirty example is below:
一个肮脏的例子如下：
create or replace view v
as
select decode(dummy, 'Y', '123') s
, 1 n
, 2.2 f
, cast (1.1 as number(5,3)) fs
from dual
/
set serveroutput on
declare
l_query varchar2(32767) := 'create table t as select <column list> from v';
l_type varchar2(100);
begin
for tc in (
select * from user_tab_columns
where table_name = 'V'
order by column_id
) loop
l_type := tc.data_type;
l_type := l_type ||
case tc.data_type
when 'NUMBER' then
case when tc.data_precision is not null then '(' || tc.data_precision || case when tc.data_scale is not null then ','||tc.data_scale end || ')' end
when 'VARCHAR2' then
'(' || tc.char_length || ' ' || case tc.char_used when 'C' then 'char' else 'byte' end || ')'
end;
l_query := replace(l_query, '<column list>', 'cast("'||tc.column_name||'" as '|| l_type ||') "'||tc.column_name||'" ,<column list>');
end loop;
l_query := replace(l_query, ',<column list>');
dbms_output.put_line(l_query);
end;
/
Results:
结果：
view V created.
anonymous block completed
create table t as select cast("S" as VARCHAR2(3 char)),cast("N" as NUMBER),cast("F" as NUMBER),cast("FS" as NUMBER(5,3)) from v
Good luck.
祝你好运。
回答by David Aldridge
The reason I choose this is 'INSERT INTO' may take more time and resource due to logging and locking.
我选择它的原因是由于日志记录和锁定，“插入”可能需要更多的时间和资源。
I think that you have based your method of dropping and recreating the tables every time on a false belief. Truncated them and using a direct path insert, optionally with nologging, would give you practically the same result without this problem. If you're using:
我认为您每次删除和重新创建表格的方法都是基于错误的信念。截断它们并使用直接路径插入（可选地使用 nologging）会给您几乎相同的结果，而不会出现此问题。如果您使用：
create table .. as select from ...
... then you're fully logging the operation anyway.
......那么你无论如何都要完全记录操作。
