Solution to ORA-01417 under Oracle 11g

Solution to ORA-01417 under Oracle 11g

Today there is customer feedback, there is a SQL on 12C no problem, on Oracle 11g, the error is reported, the error content is ORA-01417: The table can be externally connected to at most one other table. This section analyzes the SQL statements and abstracts them into the following examples.

No problem in 12c:

SQL> select * from v $ version;

BANNER CON_ID
------------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production 0
PL/SQL Release 12.1.0.1.0-Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0-Production 0
NLSRTL Version 12.1.0.1.0-Production 0
Drop table test1 purge;
Drop table test2 purge;
Drop table test3 purge;
Create table test1 (id number primary key );
Create table test2 (id number );
Create table test3 (dept_id number, measure_id number );
Insert into test1 values (1 );
Insert into test1 values (2 );
Insert into test1 values (3 );
Insert into test2 values (10 );
Insert into test2 values (20 );
Insert into test3 values (1, 10 );
Commit;

SQL> select *
From test1, test2, test3
Where test1.id = test3.dept _ id (+)
And test2.id = test3.measure _ id (+ );
ID DEPT_ID MEASURE_ID
----------------------------------------
1 10 1 10
2 10
2 20
3 20
1 20
3 10
You have selected 6 rows.

There is a problem at 11g:

SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
SQL> select *
From test1, test2, test3
Where test1.id = test3.dept _ id (+)
And test2.id = test3.measure _ id (+ );
Where test1.id = test3.dept _ id (+)
*
Row 3 has an error:
ORA-01417: tables can be externally connected to at most one other table

-- Associate test1 and test2 with test3 to solve the problem.

SQL> select *
From (select test1.id dept_id, test2.id measure_id from test1, test2),
Test3
Where a. dept_id = test3.dept _ id (+)
And a. measure_id = test3.measure _ id (+ );

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Install Oracle 11.2.0.4 x64 in Oracle Linux 6.5