Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

February 05, 2005

ORA-01445

Continuing my adventures with ANSI join syntax today I found an interesting feature.

I was writing a query to simplify a rather complicated data structure (are there any other kind in Oracle Applications?) I was being good and writing it in an iterative fashion. As I needed extra columns from extra tables I was simply adding them to my FROM clause. Everything was working fine until I added the last table - GL_CODE_COMBINATIONS.

As soon as I did that my query stopped working and I got an error message;

ORA-01445: cannot select ROWID from a join view wihtout a key-preserved table.

Which rather surprised me as there were no ROWID's in my query. for once, Google wasn't very useful. Luckily MetaLink, Oracle's support web site, came to my aid. Through the diligent research of Sunder Rajan in the DBA forum I found that it wasn't my fault really, but that Oracle (or at least Oracle 9.2) has a limit of 1050 columns in any query that uses ANSI joins. By my reckoning the tables in my FROM clause between them had about 1100 columns. I told you it was complicated.

As a workaround I changed the line;

LEFT OUTER JOIN gl_code_combinations gcc ON dist.dist_code_combination_id = gcc.code_combination_id

to;

LEFT OUTER JOIN (SELECT code_combination_id, segment3 FROM gl_code_combinations) gcc ON dist.code_combination_id = gcc.code_combination_id

And still got the error. After reducing the number of columns from another couple of tables though the error went away and the query returned the data I expected.

I mention this here because the MetaLink forums aren't publicly available and other people may fall foul of this pernicuous issue.

Of course having a query that accesses this many tables is a really bad idea because it's going to be a nightmare to maintain. Now that I've got the query working, and used it to build a view, I'm going to write some rather strenuous documentation.

Posted by Andy Todd at February 05, 2005 04:19 PM

Comments

Thanks for this tip. Though Google did not aid you, it did help me by bringing your post on top of the results.

Posted by: Naveen on February 16, 2005 08:35 AM

Same here ! Thanks for the post

Posted by: jamalak on March 1, 2005 06:10 AM

Thanks for posting - you saved me hours!!

Posted by: Chris Park on April 5, 2005 02:31 AM