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;