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

Advertisements