Home > Blogroll, sql > 10’000 columns in a query

10’000 columns in a query

April 11th, 2007 Leave a comment Go to comments

You cannot have more than 1000 columns in a view, but what about a query?

I tried

select 1,2,3,4,....,10000 from dual;

it seems to work fine. However, when increasing, I am facing various errors :

select 1,2,3,4,....,17000 from dual;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15201], 
[], [], [], [], [], [], []

or even

select 1,2,3,4,....,50000 from dual;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Tags:
  1. Tim Hall
    April 11th, 2007 at 09:50 | #1
    Reply | Quote

    If I saw a query with 10,000 columns in it, I would calmly make my way to the nearest emergency exit, whilst avoiding any eye contact. I would then proceed home and never return to that site. :)

    heers

    Tim…

  2. Vidya
    April 12th, 2007 at 16:15 | #2
    Reply | Quote

    Laurent:
    1000 columns in a view – what about a mview is it the same?

  3. Satish Kandi
    April 13th, 2007 at 07:36 | #3
    Reply | Quote

    How did you execute that query?

    I tried from sqlplus and received SP2-0027: Input is too long (> 2499 characters) – line ignored.

    Toad hangs after 2000 columns.

  4. laurentschneider
    April 13th, 2007 at 08:48 | #4
    Reply | Quote

    yes, but you have to do it on more than one line

    select 1,
    2,
    3,
    ….
    ,10000 from dual;

  5. laurentschneider
    April 13th, 2007 at 09:08 | #5
    Reply | Quote 

    create materialized view mv as select
1 c1,
2 c2,
3 c3,
4 c4,
5 c5,
6 c6,
7 c7,
8 c8,
9 c9,
....
1024 c1024 from dual;

1024 c1024 from dual
                *
ERROR at line 1025:
ORA-01792: maximum number of columns in a table or view is 1000

  6. Satish Kandi
    April 18th, 2007 at 14:10 | #6
    Reply | Quote

    Ah… Thanks.. thats really being dumb on my part.

  7. Jude Onuh
    March 22nd, 2011 at 22:57 | #7
    Reply | Quote

    @Vidya
    Since Materialized View consists of 2 main components, a View and a Log Table, it suffers any constraint that either View or Table or both face. Therefore, the maximum columns a Materialized View can have is 1,000.

  1. No trackbacks yet.
CAPTCHA Image
*