Where the LIKE function is used with OUTER JOINS, the T-SQL to PL/SQL parser in the Migration Workbench is uncertain where to put the Oracle (+) OUTER JOIN syntax markers.
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 and tab1.id like 'j%'
END
The following PL/SQL code is produced by the parser. Warnings are passed to the Migration Workbench log file.
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 and tab1.id LIKE 'j%')
;
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 problem can be avoided by adding (+) to the LIKE directive if necessary.
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 and tab1.id (+) LIKE 'j%')
;
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;