The following example illustrates how the T-SQL to PL/SQL parser in the Migration Workbench could produce this error:
CREATE PROC proc1
AS
declare @mycol1 varchar(30)
drop procedure proc2
grant all to public
FROM table1, table2
WHERE table1.id = table2.id
The following code is the PL/SQL code produced by the parser:
CREATE OR REPLACE PROCEDURE
proc1
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1 VARCHAR2(30);
/*[SPCONV-ERR(4)]:GRANT
statement ignored*/
BEGIN
BEGIN
StoO_rowcnt :=
0;
StoO_selcnt :=
0;
StoO_error
:= 0;
SELECT
col1
INTO proc1.mycol1
FROM table1, table2
WHERE table1.id
= table2.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 proc1;
It is unusual to have these administration statements in PL/SQL but they can be executed using the DBMS_SQL package. Additional error handling is required in this case.
CREATE OR REPLACE PROCEDURE
proc1
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1 VARCHAR2(30);
/*[SPCONV-ERR(4)]:GRANT
statement ignored*/
CURSOR_NAME INTEGER;
RET INTEGER;
BEGIN
BEGIN
StoO_rowcnt :=
0;
StoO_selcnt :=
0;
StoO_error
:= 0;
BEGIN
CURSOR_NAME :=
DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CURSOR_NAME,
'GRANT ALL TO PUBLIC'
, DBMS_SQL.V7);
RET := DBMS_SQL.EXECUTE(CURSOR_NAME);
DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
END;
SELECT
col1
INTO proc1.mycol1
FROM table1, table2
WHERE table1.id
= table2.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 proc1;