Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) (Oracle 10g)
ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441663] Tue, 02 February 2010 09:22 Go to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Hi All,

I am trying to execute package and it is giving error
"ORA-01792: maximum number of columns in a table or view is 1000".

I am using Merge statement in my code where i am getting this error, Package is in compiled state but getting error in run time

None of mine tables are containing column more than 600 columns.


Regards,
Akash Nathile
Re: ORA-01792: maximum number of columns in a table or view is 1000 [message #441664 is a reply to message #441663] Tue, 02 February 2010 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Either you or Oracle are mistaken.
I bet Oracle is more correctly reporting reality.
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441680 is a reply to message #441663] Tue, 02 February 2010 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the code how do you expect we know is wrong?
Double-check it, you missed something.

Regards
Michel
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441811 is a reply to message #441680] Wed, 03 February 2010 04:08 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
BlackSwan,

SELECT * from v$version;

BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Michel,

Procedure is in compiled State and it is giving error in run time.

Error Message:

BEGIN merge_statement; END;

*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at "ARTMS_DW.MERGE_STATEMENT", line 4
ORA-06512: at line 1


Regards,
Akash Nathile

  • Attachment: Merge.sql
    (Size: 135.91KB, Downloaded 525 times)
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441812 is a reply to message #441811] Wed, 03 February 2010 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does sum of number of columns of both tables exceeds 1000?

Regards
Michel
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441818 is a reply to message #441663] Wed, 03 February 2010 04:38 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Yes , is that due to it?

Count of columns in both tables is 1172 i.e 586 each.

If it is due to it , can you please tell me what is wrong in Megre statement if counter of processing columns are more than 1000?

Is it oracle Bug or do you have any solution to overcome it.


Regards,
Akash Nathile

Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441820 is a reply to message #441663] Wed, 03 February 2010 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I imagine that oracle creates a temporary inline view based on the merge select, and this is subject to the same restrictions on column numbers as ordinary views. So it's not a bug, just the way it works.
Short of breaking your tables into multiple smaller tables that can be merged seperately, or just not using merge I doubt there is any way round this.
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441821 is a reply to message #441818] Wed, 03 February 2010 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Yes , is that due to it?

Yes, Oracle internally build a (memory) view with the elements you give in the merge:
select * 
from <table1>, <query in "using" clause, here your second table>
where <condition in "on" clause>


Quote:
Is it oracle Bug or do you have any solution to overcome it.

Not a bug, a limit, the only way to workaround this is to decrease the number of columns you select. Check if you really need them all.

Regards
Michel
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441825 is a reply to message #441821] Wed, 03 February 2010 05:02 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Thanks for quick reply.

As per your suggestion i removed 200 columns from Merge statement(Removed 200 columns from Update and Insert statement statement each).

Still getting same error.


Regards,
Akash
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441829 is a reply to message #441825] Wed, 03 February 2010 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to remove them from the input, that is in "into" and "using" clause:
merge into (select <only necessary columns> from t1) t1
using (select <only necessary columns> from t2) t2
on ...

Regards
Michel
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441830 is a reply to message #441829] Wed, 03 February 2010 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, the syntax does not allow a subquery in "into" clause but you can create a view with this subquery and use it in merge.

Regards
Michel
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441840 is a reply to message #441663] Wed, 03 February 2010 06:15 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Michel,


Can you please specify what do you mean by Use view with sub query?

As there is no unused columns in tables and we have to use all the columns from repected tables if so then what is purpose of using View instead of table ?


Regards,
Akash
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441842 is a reply to message #441840] Wed, 03 February 2010 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As per your suggestion i removed 200 columns from Merge statement

Quote:
As there is no unused columns in tables

How could these 2 statements be true at the same time?

Quote:
Can you please specify what do you mean by Use view with sub query?

Create a view selecting all columns but the 200 you removed from merge.

Regards
Michel
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441848 is a reply to message #441663] Wed, 03 February 2010 06:37 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Michel,


I am using merge statement in Package and i have sent you dummy Procedure for time being, to test, that procedure i removed 200 columns from that procedure so, that is not issue.


For sake i have checked all Unused columns from that table as there is none.


Now my question is if i create View using all columns then why should not use base table?



Please send me draft code by using view and using all columns from base table.




Regards,
Akash
Re: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM) [message #441849 is a reply to message #441848] Wed, 03 February 2010 06:40 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now my question is if i create View using all columns then why should not use base table?

The reason I mentioned a view is because of your comment about 200 removed columns.
If there is none, there is no reason to use a view.

Regards
Michel
Previous Topic: question about foreign keys
Next Topic: Selecting 0 instead of negative value
Goto Forum:
  


Current Time: Wed Sep 28 05:43:46 CDT 2016

Total time taken to generate the page: 0.05914 seconds