[SPCONV-ERR(<NN>)]:[Parsing(<NAME>)]:(LIKE .. INNER) Manual Conversion Might be Required

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;