Database administrator activities are not valid in PL/SQL but they can be performed indirectly if necessary through the DBMS_SQL package.
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)
CREATE view myview as
select col1,col2 from table1
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(
RC1 IN OUT proc1Pkg.RCT1)
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1 VARCHAR2(30);
BEGIN
OPEN RC1 FOR
SELECT
col1, col2 FROM table1;
/*[SPCONV-ERR(4)]:CREATE
VIEW myview statement ignored*/
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;
The following package is generated:
CREATE OR REPLACE
PACKAGE PROC1Pkg AS
TYPE RT1 IS RECORD (
col1
table1.col1%TYPE,
col2
table1.col2%TYPE
);
TYPE RCT1 IS REF
CURSOR RETURN RT1;
END;
Package definition and cursor are not required. View creation can be performed using the DBMS_SQL package.
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);
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,
'CREATE
view myview as select col1,col2 from table1'
, 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;