I have a select statement that joins many tables with combined column count of over 1000. I am selecting roughly 200 columns from various tables (far less than 1000 column limit). In Oracle 11g, the select statement returns result but in Oracle 12c it throws error:
ORA-01792: maximum number of columns in a table or view is 1000
Note: Same result using either sql-developer or sqlPlus.
It this a configuration issue or a bug in Oracle 12c? Thanks for your time.
SELECT * from v$version
-------------------------------------------------------------------------------- ----------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production | 0 | |
PL/SQL Release 12.1.0.2.0 - Production | 0 | |
CORE 12.1.0.2.0 Production | 0 | |
TNS for Linux: Version 12.1.0.2.0 - Production | 0 | |
NLSRTL Version 12.1.0.2.0 - Production | 0 | |
5 rows selected
Following script demonstrates the issue:
declare v_sql varchar2(32767);
begin
-- create test table GREEN with 500 columns
v_sql := 'create table GREEN (G_COLUMN_001 number(9) not null';
for i in 1 .. 499 loop
v_sql := v_sql || ',' || chr(10) || 'G_COLUMN_' || lpad(to_char(i+1), 3, '0') || ' varchar2(2) null';
end loop;
v_sql := v_sql || ')';
execute immediate v_sql;
-- create test table RED with 500 columns
v_sql := 'create table RED (G_COLUMN_001 number(9) not null';
for i in 1 .. 499 loop
v_sql := v_sql || ',' || chr(10) || 'R_COLUMN__' || lpad(to_char(i+1), 3, '0') || ' varchar2(2) null';
end loop;
v_sql := v_sql || ')';
execute immediate v_sql;
end;
/
-- create test table PURPLE with 2 columns
create table PURPLE (G_COLUMN_001 number(9) not null, P_COLUMN_002 varchar2(2));
--********************************************************************************--
-- selecting one column work!
--********************************************************************************--
select p.P_COLUMN_002
from GREEN g
join RED r on g.G_COLUMN_001 = r.G_COLUMN_001
join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001;
--********************************************************************************--
-- notice the purple table only has 2 columns, but selecting p.* fails in Oracle 12c:
-- SQL Error: ORA-01792: maximum number of columns in a table or view is 1000
--********************************************************************************--
select p.*
from GREEN g
join RED r on g.G_COLUMN_001 = r.G_COLUMN_001
join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001;
--********************************************************************************--
-- sub selecting only one column also fails in Oracle 12c:
-- SQL Error: ORA-01792: maximum number of columns in a table or view is 1000
--********************************************************************************--
select (select 1 from dual where 1 = 2) as exp_column
from GREEN g
join RED r on g.G_COLUMN_001 = r.G_COLUMN_001
join PURPLE p on g.G_COLUMN_001 = p.G_COLUMN_001;
drop table GREEN;
drop table RED;
drop table PURPLE;