0

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.

SomeoneLearning17
  • 151
  • 8

2 Answers2

2

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)
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thank you! Made the changes and when we create the tables oracle is throwing a "ORA-00955: name is already used by an existing object". Not sure if this is supposed to happen since the first block of code works and it should have dropped the ADDRESS table? – SomeoneLearning17 Dec 06 '21 at 20:10
  • I don't know what you did (as you never posted it). I re-ran code **I posted** several times, and it executed successfully every time. – Littlefoot Dec 06 '21 at 20:24
  • It may fail at creating ADDRESS when initially ADDRESS exists and EMPLOYEE does not. – Serg Dec 06 '21 at 20:30
  • Right, @Serg. Thank you for using your brain (whilst I'm not using mine). But, if each dynamic SQL statement is enclosed into its own BEGIN-EXCEPTION-END block, then it *works* OK. Code is now fixed & tested. Thanks again! – Littlefoot Dec 06 '21 at 20:37
  • This worked, thank you! – SomeoneLearning17 Dec 07 '21 at 00:48
1

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(
...
Andreas Hauser
  • 92
  • 8