Do not get ORA-25156 "an old style outer join (+) cannot be used with ANSI join" when I should be

Question

When converting some old styles to SQL-92 style, a (+) was accidentally left in the query. There were no errors when I expected the ORA-25156 error: the old-style outer join (+) could not be used with the addition of ANSI.

I have the following tables:

 create table inner_join ( id integer , some_data varchar2(32) ); insert into inner_join values (1, 'a'); insert into inner_join values (2, 'b'); create table outer_join ( id integer , some_data varchar2(32) ); insert into outer_join values(2, 'c'); insert into outer_join values(3, 'd'); 

An A (more complex) query has been converted, similar to the following:

 select * from outer_join oj , inner_join ij where ij.id = oj.id(+); 

and by mistake the request was converted as follows

 select * from outer_join oj join inner_join ij on ij.id = oj.id(+); 

This gives the expected results, however, it is rather dangerous because:

• It is easy for someone to assume that it was assumed that the INNER JOIN is unlike the OUTER JOIN (suppose what happened ...), thereby causing unintentional errors when the query is changed again.
• The displayed behavior may change in the future, leading to errors in a number of places.

• It seems to contradict the documentation

  You cannot specify a (+) operator in a query block that also contains the join syntax of a FROM clause.

• A significant advantage of using the SQL-92 standard is that when an error occurs in the code (i.e., no connection), an error occurs

I would always like the ORA-25156 to go up when it should be.

To demonstrate the problem: I do not get an error if I must, if I run these requests

 select * from outer_join oj join inner_join ij on ij.id = oj.id(+); ID SOME_DATA ID SOME_DATA --- --------- --- --------- 2 c 2 b 1 a select * from inner_join ij join outer_join oj on ij.id = oj.id(+); ID SOME_DATA ID SOME_DATA --- --------- --- --------- 2 b 2 c 1 a 

If I add a third table

 create table middle_join ( id integer , some_data varchar2(32) ); insert into middle_join values (1, 'e'); insert into middle_join values (2, 'f'); insert into middle_join values (3, 'g'); 

then when the old-style join is in the "middle" of the request, there is no error

 select * from inner_join ij join outer_join oj on ij.id = oj.id(+) join middle_join mj on ij.id = mj.id; ID SOME_DATA ID SOME_DATA ID SOME_DATA --- --------- --- --------- --- --------- 1 a 1 e 2 b 2 c 2 f 

If the connection is at the "end" of the request, then the correct error occurs (!).

 select * from inner_join ij join middle_join mj on ij.id = mj.id join outer_join oj on ij.id = oj.id(+); on ij.id = oj.id(+) * ERROR at line 6: ORA-25156: old style outer join (+) cannot be used with ANSI joins 

Why is this happening? How can I ensure that the ORA-25156 is raised in all cases to avoid simple errors and future problems?

I tested this on 12.1.0.1, but I would be surprised if it was specific to this version ...

Answer 1

I believe that the error occurs when trying to use (+) in the WHERE clause in the ANSI syntax request. There is no error trying to do this in the ON clause, because in Oracle the old crappy syntax was no ON clause.

I assume it is dirty that you can package (+) in ON, but the error is for the WHERE clause.