CREATE TABLE statements are filtered out during parsing and the tables are created just before the procedures are created. This may cause conflicts, but PL/SQL procedures cannot use tables before they are created, so dynamic creation of tables will not work.
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 TABLE mytable
as (myvchar varchar30)
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);
BEGIN
/*[SPCONV-ERR(4)]:CREATE
TABLE mytable statement passed to ddl file*/
DELETE FROM mytable;
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 DDL statements are created:
DROP TABLE mytable
CREATE
TABLE mytable (
myvchar
varchar30
)
In this case, the table is dropped before it is created. No alteration is required if table creation on procedure build time is sufficient.