The T-SQL to PL/SQL parser in the Migration Workbench is unable to convert all cases of OUTER JOINs. For example, when the parser encounters a statement such as the following:
select a RIGHT OUTER JOIN b ON d = e
It does not know which table d and e are in. Therefore, it cannot add the Oracle style (+) OUTER JOIN symbols.
Statements such as the following:
select a RIGHT OUTER JOIN b ON a.d = b.e
are correctly converted as follows:
select a, b where a.d(+) = b.e;
The following example illustrates how the parser could produce this error:
CREATE PROC myouterjoin
AS
BEGIN
declare @mycol1 varchar(30)
declare @mycol2 varchar(30)
select
@mycol1=tab1.col1, @mycol2=tab2.col2 from tab1
RIGHT OUTER JOIN tab2
on tab1.id=tab2.id
END
The following PL/SQL code is produced by the parser in the Migration Workbench:
CREATE OR REPLACE
PROCEDURE myouterjoin
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1 VARCHAR2(30);
mycol2 VARCHAR2(30);
BEGIN
/*[SPCONV-ERR(7)]:[Parsing(myouterjoin)]('RIGHT
JOIN') Complex outer joins are not reliably converted*/
BEGIN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := 0;
SELECT tab1.col1, tab2.col2
INTO
myouterjoin.mycol1, myouterjoin.mycol2 FROM tab1, tab2
WHERE (tab1.id
(+)= tab2.id)
;
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN
TOO_MANY_ROWS THEN
StoO_rowcnt := 2;
WHEN
OTHERS THEN
StoO_rowcnt := 0;
StoO_selcnt := 0;
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
END;
This
simple OUTER JOIN does not require alteration.