[SPCONV-ERR(<NN>)]:(LIKE) if using '[' Manual Conversion Might be Required

Oracle does not support syntax such as [a-z] matching any lower case alphabetic character. If these are used, Oracle functions such as TRANSLATE and REPLACE may be useful in the manual conversion. If full regular expression support is required, a call out to C is possible. If '[' is used, manual conversion is required.

The following example illustrates how the T-SQL to PL/SQL parser in the Migration Workbench could produce this error:

CREATE PROC mylikestatement (
@myvcharout varchar(40) OUTPUT)
AS
BEGIN
select @myvcharout=tab1.col1 from tab1 where tab1.col like '[a-z]1234'
END

The following code is the PL/SQL code produced by the parser:

CREATE OR REPLACE PROCEDURE mylikestatement(
myvcharout  IN OUT VARCHAR2)
AS
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
 /*[SPCONV-ERR(5)]:(LIKE) if using '[' MANUAL CONVERSION MIGHT BE REQUIRED*/

BEGIN
 BEGIN
  StoO_rowcnt := 0;
  StoO_selcnt := 0;
  StoO_error  := 0;

  SELECT   tab1.col1
  INTO mylikestatement.myvcharout FROM tab1
  WHERE tab1.col  LIKE '[a-z]1234';
  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 removing any performance shortcuts as follows:

CREATE OR REPLACE PROCEDURE mylikestatement(
myvcharout  IN OUT VARCHAR2)
AS
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
 /*[SPCONV-ERR(5)]:(LIKE) if using '[' MANUAL CONVERSION MIGHT BE REQUIRED*/

BEGIN
 BEGIN
  StoO_rowcnt := 0;
  StoO_selcnt := 0;
  StoO_error  := 0;

  SELECT   tab1.col1
  INTO mylikestatement.myvcharout FROM tab1
  WHERE  translate(substring(tab1.col,1,1),
          '0123456789abcdefghijklmnopqrstuvwxyz',
          '1111111111zzzzzzzzzzzzzzzzzzzzzzzzzz')='z'
          and
          substring(tab1.col,2)='1234'
  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;