Full outer joins require manual intervention. If a full outer join is done on more than one table, Oracle-wide restrictions may be hit, even when the query is rewritten.
Oracle does not explicitly support full outer joins today. However, there are several ways to express a full outer join within Oracle. Let us suppose that you want to do the following query:
select a.col2
acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
from a,b where a.col1 (+) = b.col1 (+);
In this example, the predicate 'a.col1 (+) = b.col1 (+)' is "Oracle-ese" notation for a full outer join (although this is not supported within Oracle today).
The most efficient way to execute this query is by using a union-all of a left outer join and a right outer join (with an additional predicate):
select a.col2
acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
from a,b where a.col1=b.col1(+)
union all
select a.col2
acol2, a.col1 acol1, b.col1 bcol1, b.col2 bcol2
from a,b where a.col1(+)=b.col1 and a.col1 is null;
The following example illustrates how the T-SQL to PL/SQL parser in the Migration Workbench could produce this error:
CREATE PROC myouterjoin
AS
BEGIN
select tab1.col1,
tab2.col2 from tab1 FULL OUTER JOIN tab2 on tab1.id=tab2.id
END
The following code is the PL/SQL code produced by the parser:
CREATE OR REPLACE
PACKAGE MYOUTERJOINPkg AS
TYPE RT1 IS RECORD (
col1
tab1.col1%TYPE,
col2
tab2.col2%TYPE
);
TYPE RCT1 IS REF
CURSOR RETURN RT1;
END;
..
..
CREATE OR REPLACE
PROCEDURE myouterjoin(
RC1 IN OUT myouterjoinPkg.RCT1)
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
OPEN RC1 FOR
SELECT
tab1.col1, tab2.col2 FROM tab1, tab2
WHERE (tab1.id /*FULL*/=/*FULL*/
/*[SPCONV-ERR(5)]:(FULL OUTER JOIN) MANUAL CONVERSION MIGHT BE
REQUIRED*/
tab2.id)
;
END;
This problem can be avoided by rewriting the PL/SQL code by hand as follows:
CREATE OR REPLACE
PACKAGE MYOUTERJOINPkg AS
TYPE RT1 IS RECORD (
col1
tab1.col1%TYPE,
col2
tab2.col2%TYPE
);
TYPE RCT1 IS REF
CURSOR RETURN RT1;
END;
..
..
CREATE OR REPLACE
PROCEDURE myouterjoin(
RC1 IN OUT myouterjoinPkg.RCT1)
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
BEGIN
OPEN RC1 FOR
SELECT
tab1.col1, tab2.col2 FROM tab1, tab2
WHERE (tab1.id
= tab2.id (+))
union all
SELECT
tab1.col1, tab2.col2 FROM tab1, tab2
WHERE (tab1.id
(+) = tab2.id ) and tab1.id is null;
END;