[SPCONV-ERR(<NN>)]:CREATE VIEW <STRING> Statement Ignored

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;