[SPCONV-ERR(<NN>)]:DROP PROCEDURE <STRING> Statement Ignored

Database administrator activities are not valid in PL/SQL, though they can be performed indirectly using the DBMS_SQL package if necessary.

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
select @mycol1=col1
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)]:DROP PROCEDURE proc2  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)]:DROP PROCEURE proc2 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,
'DROP PROCEDURE PROC2'
  , 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;