A "gotcha" with 10.2 to 11.1 upgrade
Date: Wed, 9 Jan 2008 11:19:02 -0700
Message-ID: <4025610e0801091019k4757b44aw6be4c9a34491a3e2@mail.gmail.com>
If anybody is planning on an upgrade to 11.1, here's an interesting
'gotcha' that I ran across.
ANSI joins behave differently in 11.1 than they did in at least 10.2 (I don't know about earlier versions).
According to Oracle, in 10.2 there was a bug in that 10.2 ANSI joins didn't return the ORA-00918 (ambiguous column) error, but that does happen now in 11.1. To me though, it seems more some sort of 'bug' (or at least a discrepency) between the syntax of the 'USING' and the 'ON' clause of join statements.
To test, run this test case.
CREATE TABLE "ORA_TESTALL"
( "JOB_ID" VARCHAR2(10 BYTE),
"LAB_ID" VARCHAR2(10 BYTE),
"SPECIES" VARCHAR2(35 BYTE)
);
CREATE TABLE "ORA_TESTCODE"
( "LAB_ID" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"STATE" VARCHAR2(2 BYTE),
"JOB_ID" VARCHAR2(10 BYTE)
);
CREATE TABLE "ORA_TESTJOB"
( "JOB_ID" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"SUBMITTER" VARCHAR2(75 BYTE)
);
commit;
REM INSERTING into ORA_TESTALL
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values (null,'AAW422','CO2');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values (null,'AAW422','Fe');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values (null,'AEY329','Fe');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values (null,'AEY329','Mg');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values (null,'BGQ980','Ni');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values (null,'BGQ980','Pb');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values ('10002','CAA024','Fe');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values (null,'LDG444','Zr');
Insert into ORA_TESTALL (JOB_ID,LAB_ID,SPECIES) values
('MRP02164','C153670','Hg');
REM INSERTING into ORA_TESTCODE
Insert into ORA_TESTCODE (LAB_ID,STATE,JOB_ID) values ('AAW422',null,'HM286');
Insert into ORA_TESTCODE (LAB_ID,STATE,JOB_ID) values ('AEY329',null,'HM1686');
Insert into ORA_TESTCODE (LAB_ID,STATE,JOB_ID) values ('BGQ980',null,'HM4192');
Insert into ORA_TESTCODE (LAB_ID,STATE,JOB_ID) values ('CAA024','AK','10002');
Insert into ORA_TESTCODE (LAB_ID,STATE,JOB_ID) values ('LDG444',null,'71291');
Insert into ORA_TESTCODE (LAB_ID,STATE,JOB_ID) values
('C153670','ND','MRP02164');
REM INSERTING into ORA_TESTJOB
Insert into ORA_TESTJOB (JOB_ID,SUBMITTER) values ('10002','Person, A');
Insert into ORA_TESTJOB (JOB_ID,SUBMITTER) values ('71291',Couple, A');
Insert into ORA_TESTJOB (JOB_ID,SUBMITTER) values ('MRP02164',Public, John Q.');
commit;
CREATE OR REPLACE FORCE VIEW ora_problem_view (lab_id,
job_id,
state,
species,
submitter)
AS
SELECT lab_id, job_id, state, species, submitter FROM ora_testcode LEFT OUTER JOIN ora_testjob USING (job_id)
LEFT OUTER JOIN ora_testall USING (lab_id)
;
commit;
Run this on both versions of Oracle (at least 10.2 and 11.1). The ORA_PROBLEM_VIEW doesn't compile under 11.1, as you'll receive the ORA-00918 error, but it works fine under 10.2. Actually I should clarify that statement a bit more.
In 11.1, you can compile the view fine in SQL Plus and select from it. However, look at it in either the web-based or java-based EM, Sql Developer, or TOAD, and it displays as 'Invalid', and you can't compile it or view the data.
The 'fix' from Oracle is to change the view to use the 'ON' cluase of ANSI joins, as:
CREATE OR REPLACE FORCE VIEW ora_fixed_view (lab_id,
job_id,
state,
species,
submitter)
AS
SELECT a.lab_id, a.job_id, state, species, submitter
FROM ora_testcode a LEFT OUTER JOIN ora_testjob b ON a.job_id = b.job_id
LEFT OUTER JOIN ora_testall c ON a.lab_id = c.lab_id
;
So, if your database is using ANSI joins and you are thinking about moving to 11g, check your join syntax first. Hopefully this will save some of you the grief it caused me over the last one and a half weeks. Sort of my minuscle 'payback' for the help I've recieved here.
(sorry if there are any typos above)
-- -- Bill Ferguson -- -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 09 2008 - 12:19:02 CST