CODEWITHSUNDEEP
oracle-databaseplsql
Jonas Chen
Jonas Chen

Reputation: 3

PLS-00103 compilation error when creating table within a procedure

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

Upvotes: 0

Views: 582

Answers (1)

Popeye
Popeye

Reputation: 35930

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;
/

Upvotes: 1

Related Questions