We are trying to drop all tables in a database and then create them again, but oracle is throwing an error. the error report is:

Error report -
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "CREATE" 
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

and the code is:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || EMPLOYEE;
   EXECUTE IMMEDIATE 'DROP TABLE ' || ADDRESS;

EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

CREATE TABLE EMPLOYEE(
    EmployeeID int,
    FirstName varchar(225),
    LastName varchar(255),
    Position varchar(255),
    SSN int,
    Address varchar(255),
    Phone int,
    AddressID int,
    
    PRIMARY KEY (EmployeeID),
    FOREIGN KEY (AddressID) REFERENCES ADDRESS(AddressID)
);

CREATE TABLE ADDRESS(
    AddressID int,
    Street varchar(225),
    City varchar(225),
    State varchar(225),
    Zip int
);

We want to do this for all tables but so far it's not working for the two tables we are trying to drop at the start.

CodePudding user response：

Should've been like this:

SQL> BEGIN
  2      BEGIN
  3          EXECUTE IMMEDIATE 'DROP TABLE EMPLOYEE';
  4      EXCEPTION
  5          WHEN OTHERS THEN
  6              IF sqlcode != -942 THEN
  7                  RAISE;
  8              END IF;
  9      END;
 10
 11      BEGIN
 12          EXECUTE IMMEDIATE 'DROP TABLE ADDRESS';
 13      EXCEPTION
 14          WHEN OTHERS THEN
 15              IF sqlcode != -942 THEN
 16                  RAISE;
 17              END IF;
 18      END;
 19
 20  END;
 21  /

PL/SQL procedure successfully completed.

SQL> CREATE TABLE ADDRESS(
  2      AddressID int primary key,
  3      Street varchar(225),
  4      City varchar(225),
  5      State varchar(225),
  6      Zip int
  7  );

Table created.

SQL> CREATE TABLE EMPLOYEE(
  2      EmployeeID int,
  3      FirstName varchar(225),
  4      LastName varchar(255),
  5      Position varchar(255),
  6      SSN int,
  7      Address varchar(255),
  8      Phone int,
  9      AddressID int,
 10      PRIMARY KEY (EmployeeID),
 11      FOREIGN KEY (AddressID) REFERENCES ADDRESS(AddressID)
 12  );

Table created.

SQL>

What did you do wrong?

• table names should be enclosed into single quotes with dynamic SQL because - if they don't exist, code will fail
• enclose each dynamic SQL statement into its own BEGIN-EXCEPTION-END block to avoid problems when one of tables exists (and another does not)
• terminate PL/SQL block with a slash (line #11); otherwise, some tools (like SQL*Plus) won't be able to execute whole code as a script
• first create ADDRESS table (and add a primary key constraint because foreign key on EMPLOYEE will fail otherwise)

CodePudding user response：

Try to swap the CREATE tables, because you try to use a FOREIGN KEY (AddressID), which is at the moment of creation not accessable.

CREATE TABLE ADDRESS(
...

CREATE TABLE EMPLOYEE(
...