PLS-00103 compilation error when creating table within a procedure

Question

I am writing some PL/SQL code which create table for database.

When I try to trace the code I keep getting error PLS-00103. I have looked at other threads and it suggests there is a missing a begin of loop but I cannot see where that might be.

SET SERVEROUTPUT

EXECUTE THELLO;

 CREATE OR REPLACE PROCEDURE THELLO AS
    WELCOME VARCHAR(50);
BEGIN
    CREATE TABLE NEW_PART(
P_PARTKEY   NUMBER(12)  NOT NULL,
P_NAME      VARCHAR(55) NOT NULL,
P_MFGR      VARCHAR(25) NOT NULL,
P_BRAND     CHAR(10)    NOT NULL,
P_TYPE      VARCHAR(25) NOT NULL,
P_SIZE      NUMBER(12)  NOT NULL,
P_CONTAINER     CHAR(10)    NOT NULL,
P_RETAILPRICE   NUMBER(12,2)    NOT NULL,
P_COMMENT   VARCHAR(23) NOT NULL,
    CONSTRAINT NEW_PART_PEKEY PRIMARY KEY (P_PARTKEY),
    CONSTRAINT NEW_PART_CHECK1 CHECK(P_PARTKEY >= 0),
    CONSTRAINT NEW_PART_CHECK2 CHECK(P_SIZE >= 0),
    CONSTRAINT NEW_PART_CHECK3 CHECK(P_RETAILPRICE >= 0) );

CREATE TABLE NEW_SUPPLIER(
S_SUPPKEY   NUMBER(12)  NOT NULL,
S_NAME      CHAR(25)    NOT NULL,
S_ADDRESS   VARCHAR(40) NOT NULL,
S_NATIONKEY NUMBER(12)  NOT NULL,
S_PHONE     CHAR(15)    NOT NULL,
S_ACCTBAL   NUMBER(12,2)    NOT NULL,
S_COMMENT   VARCHAR(101)    NOT NULL,
    CONSTRAINT NEW_SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY),
    CONSTRAINT NEW_SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY)
        REFERENCES NATION(N_NATIONKEY),
    CONSTRAINT NEW_SUPPLIER_CHECK1 CHECK(S_SUPPKEY >= 0) );

CREATE TABLE NEW_PARTSUPP(
PS_PARTKEY  NUMBER(12)  NOT NULL,
PS_SUPPKEY  NUMBER(12)  NOT NULL,
PS_AVAILQTY NUMBER(12)  NOT NULL,
PS_SUPPLYCOST   NUMBER(12,2)    NOT NULL,
PS_COMMENT  VARCHAR(199)    NOT NULL,
    CONSTRAINT NEW_PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY)
        REFERENCES NEW_PART(P_PARTKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY)
        REFERENCES NEW_SUPPLIER(S_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_CHECK1 CHECK(PS_PARTKEY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK2 CHECK(PS_AVAILQTY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK3 CHECK(PS_SUPPLYCOST >= 0) );

DECLARE

BEGIN

  FOR PARTKEY IN (SELECT DISTINCT PS_PARTKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_PART ( SELECT * 
                           FROM PART
                           WHERE P_PARTKEY = PARTKEY.PS_PARTKEY );
    COMMIT;
  END LOOP;

  FOR SUPPKEY IN (SELECT DISTINCT PS_SUPPKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_SUPPLIER ( SELECT * 
                               FROM SUPPLIER
                               WHERE S_SUPPKEY = SUPPKEY.PS_SUPPKEY );
    COMMIT;
  END LOOP;

  INSERT INTO NEW_PARTSUPP ( SELECT * 
                             FROM PARTSUPP 
                 WHERE PS_PARTKEY IN ( SELECT P_PARTKEY
                                                   FROM NEW_PART ) AND
                                   PS_SUPPKEY IN ( SELECT S_SUPPKEY
                                                   FROM NEW_SUPPLIER ) );
  COMMIT;
END THELLO;
/
show errors
prompt Done.

PROBLEM:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

You can not execute ddl inside the procedure i.e you can not execute create table inside the procedure. If you really want to execute any ddl inside the procedure then use execute immediate and also declare can not be used inside begin.. end — Popeye
Tag correctly. SQL Server and Oracle do not use the same sql dialect and have different features. — SMor
Also, you should replace char and varchar with the standard varchar2. — William Robertson

Popeye Popeye · Accepted Answer · 2019-11-09T03:54:36

As I did comment, table creation can not be the part of the procedure unless it is used in EXECUTE IMMEDIATE.

Create table separately before creating procedure.

CREATE TABLE NEW_PART(
P_PARTKEY   NUMBER(12)  NOT NULL,
P_NAME      VARCHAR(55) NOT NULL,
P_MFGR      VARCHAR(25) NOT NULL,
P_BRAND     CHAR(10)    NOT NULL,
P_TYPE      VARCHAR(25) NOT NULL,
P_SIZE      NUMBER(12)  NOT NULL,
P_CONTAINER     CHAR(10)    NOT NULL,
P_RETAILPRICE   NUMBER(12,2)    NOT NULL,
P_COMMENT   VARCHAR(23) NOT NULL,
    CONSTRAINT NEW_PART_PEKEY PRIMARY KEY (P_PARTKEY),
    CONSTRAINT NEW_PART_CHECK1 CHECK(P_PARTKEY >= 0),
    CONSTRAINT NEW_PART_CHECK2 CHECK(P_SIZE >= 0),
    CONSTRAINT NEW_PART_CHECK3 CHECK(P_RETAILPRICE >= 0) );

CREATE TABLE NEW_SUPPLIER(
S_SUPPKEY   NUMBER(12)  NOT NULL,
S_NAME      CHAR(25)    NOT NULL,
S_ADDRESS   VARCHAR(40) NOT NULL,
S_NATIONKEY NUMBER(12)  NOT NULL,
S_PHONE     CHAR(15)    NOT NULL,
S_ACCTBAL   NUMBER(12,2)    NOT NULL,
S_COMMENT   VARCHAR(101)    NOT NULL,
    CONSTRAINT NEW_SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY),
    CONSTRAINT NEW_SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY)
        REFERENCES NATION(N_NATIONKEY),
    CONSTRAINT NEW_SUPPLIER_CHECK1 CHECK(S_SUPPKEY >= 0) );

CREATE TABLE NEW_PARTSUPP(
PS_PARTKEY  NUMBER(12)  NOT NULL,
PS_SUPPKEY  NUMBER(12)  NOT NULL,
PS_AVAILQTY NUMBER(12)  NOT NULL,
PS_SUPPLYCOST   NUMBER(12,2)    NOT NULL,
PS_COMMENT  VARCHAR(199)    NOT NULL,
    CONSTRAINT NEW_PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY)
        REFERENCES NEW_PART(P_PARTKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY)
        REFERENCES NEW_SUPPLIER(S_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_CHECK1 CHECK(PS_PARTKEY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK2 CHECK(PS_AVAILQTY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK3 CHECK(PS_SUPPLYCOST >= 0) );

Now, your procedure code.

CREATE OR REPLACE PROCEDURE THELLO AS
    WELCOME VARCHAR(50);
BEGIN

  FOR PARTKEY IN (SELECT DISTINCT PS_PARTKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_PART ( SELECT * 
                           FROM PART
                           WHERE P_PARTKEY = PARTKEY.PS_PARTKEY );
    COMMIT;
  END LOOP;

  FOR SUPPKEY IN (SELECT DISTINCT PS_SUPPKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_SUPPLIER ( SELECT * 
                               FROM SUPPLIER
                               WHERE S_SUPPKEY = SUPPKEY.PS_SUPPKEY );
    COMMIT;
  END LOOP;

  INSERT INTO NEW_PARTSUPP ( SELECT * 
                             FROM PARTSUPP 
                 WHERE PS_PARTKEY IN ( SELECT P_PARTKEY
                                                   FROM NEW_PART ) AND
                                   PS_SUPPKEY IN ( SELECT S_SUPPKEY
                                                   FROM NEW_SUPPLIER ) );
  COMMIT;
END THELLO;
/

PLS-00103 compilation error when creating table within a procedure

1 Answers