ORA-01790 with huge SQL statement - how to track down the problem column
471877Sep 24 2009 — edited Sep 25 2009Hi,
We're having some issues with a huge SQL statement (3500 lines - I'm not joking). And yes it is all one statement.
INSERT into TAB..... SELECT (with about 50 uses of the WITH clause, and about 50 unions).
The statement runs (takes a while) when you execute it and we can get the plan for the current statement while it's running. However if you just try 'explain plan for' for the statement you just get a ORA-01790 error as there is a mismatch in the datatypes between one or more of the unions.
The issue is with such a huge statement how do I know which columns oracle is complaining about - it obviously knows to throw the error but doesn't actually tell you where the problem lies.
Is there an event I can set to show me this? Is there some other way of deriving it?
We're on 10.2.0.4.1 on redhat.
Cheers,
Harry