oracle ORA-01792: 在 sql 中使用 WITH 时，表或视图中的最大列数为 1000 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38655654/
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
ORA-01792: maximum number of columns in a table or view is 1000 error while using WITH in sql
提问by Pooja Patil
I have a query :
我有一个疑问：
WITH abc AS
(
(SELECT SRC_DATA.*,
(SELECT MAX(DECODE(OBJ.AUD_ACTION_FLAG,'D',OBJ.OUPDATE_COUNT,OBJ.NUPDATE_COUNT))
FROM SMARTTRIAL_ODR_LANDING.AUD_TRIAL_DESIGN OBJ
WHERE OBJ.AUD_DATE_CHANGED BETWEEN TO_DATE('01-JAN-1900') AND (SRC_DATA.AUD_DATE_CHANGED)
AND DECODE(OBJ.AUD_ACTION_FLAG,'D',OBJ.OTRIAL_NO,OBJ.NTRIAL_NO)= DECODE(SRC_DATA.AUD_ACTION_FLAG,'D',SRC_DATA.OTRIAL_NO,SRC_DATA.NTRIAL_NO)
AND OBJ.AUD_ACTION_FLAG <> 'D'
) UPDATE_COUNT,
/***Multiple select statement like above with many other look up tables like AUD_TRIAL_DESIGN ****/
FROM SMARTTRIAL_ODR_LANDING.AUD_TRIAL SRC_DATA /***AUD_TRIAL is the base table***/
),
WITH def AS
(SELECT OBJ_DATA .*,
/***Similar statement as mentioned in above block and lookup table is AUD_OBJECTIVE***/
FROM SMARTTRIAL_ODR_LANDING.AUD_TRIAL_OBJECTIVE OBJ_DATA /***AUD_TRIAL_OBJECTIVE is the base table***/
)
----Query to select columns-----
----查询选择列-----
FROM abc
LEFT JOIN def
LEFT JOIN xyz ON (column from def = column from xyz)
For the simliar structure of query written by me, following error is returned :
对于我写的查询的类似结构，返回以下错误：
ORA-01792: maximum number of columns in a table or view is 1000
01792. 00000 - "maximum number of columns in a table or view is 1000"
*Cause: An attempt was made to create a table or view with more than 1000
columns, or to add more columns to a table or view which pushes
it over the maximum allowable limit of 1000. Note that unused
columns in the table are counted toward the 1000 column limit.
*Action: If the error is a result of a CREATE command, then reduce the
number of columns in the command and resubmit. If the error is
a result of an ALTER TABLE command, then there are two options:
1) If the table contained unused columns, remove them by executing
ALTER TABLE DROP UNUSED COLUMNS before adding new columns;
2) Reduce the number of columns in the command and resubmit.
Could anyone please suggest a solution
任何人都可以请提出解决方案
回答by Jim Wartnick
We had a similar problem (Here is an excerpt from the SR):
我们遇到了类似的问题（这是 SR 的摘录）：
Creating view generates ORA-01792 maximum number of columns in a table or view is 1000
创建视图生成 ORA-01792 表或视图中的最大列数为 1000
We have a new application that has a view that contains 35 columns. However, when creating it, it errors out stating that there are over 1000 columns, which is false. I will attach the view definition
我们有一个包含 35 列的视图的新应用程序。但是，在创建它时，它错误地指出有超过 1000 列，这是错误的。我将附上视图定义
Here is what Oracle said (and it did fix the problem):
这是 Oracle 所说的（它确实解决了问题）：
Bug 19893041 : ORA-01792 HAPPEN WHEN UPDATE TO 12.1.0.2
错误 19893041：更新到 12.1.0.2 时发生 ORA-01792
closed as dup of
关闭为 dup
Bug 19509982 : DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT.
错误 19509982：默认禁用修复 ORA-1792。
Solution: SQL> alter system set "_fix_control"='17376322:OFF'; Or B. Apply patch 19509982 (no conflicts found with the attached opatch)
解决方法：SQL>alter system set "_fix_control"='17376322:OFF'; 或 B. 应用补丁 19509982（未发现与附加的 opatch 冲突）
That may be the same issue you're encountering.
这可能与您遇到的问题相同。
