sqloracle-databaseif-statementcompiler-errorsdrop-table

Oracle drop table if exists is throwing an error starting at line 1: in command

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.

Solution

  • 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?