SQL: Missing right parenthesis and table or view does not exist errors

Question

I am trying to create 5 tables, however the first 3 drop and create fine with no issues but the last 2 band and fan do not seem to have much luck.

DROP TABLE festival cascade constraints;
CREATE TABLE festival (
 Festival_ID    VARCHAR2(4) PRIMARY KEY,
 Festival_Organizer     varchar2(30),
 Festival_Location  varchar2(20));

DROP TABLE venue cascade constraints;
CREATE TABLE venue(
 Venue_ID   VARCHAR2(3) PRIMARY KEY,
 Venue_Name     VARCHAR2(25),
 Venue_Location VARCHAR2(15),
 Venue_Contact_Person VARCHAR2(30),
 Venue_Contact_Number NUMERIC(9,2),
 Venue_Type VARCHAR2(15),
 Venue_Size VARCHAR2(6),
 Venue_HirePrice FLOAT(8));

DROP TABLE staff cascade constraints;
CREATE TABLE staff(
 Staff_ID   VARCHAR2(3) PRIMARY KEY,
 Staff_Firstname    VARCHAR2(20),
 Staff_Surname  VARCHAR2(25),
 Staff_Specialism   VARCHAR2(20),
 Staff_Salary   NUMERIC(8,2),
 Staff_Contact_Number   NUMERIC(9,2),
 Staff_Address  VARCHAR2(50),
 Venue_ID   VARCHAR2(5) REFERENCES venue(Venue_ID));

DROP TABLE band cascade constraints;
CREATE TABLE band(
 Band_ID    VARCHAR2(3) PRIMARY KEY,
 Band_Name  VARCHAR2(25),
 Band_ContactNo NUMERIC(8,2),
 Band_PlayDate  DATE NOT NULL,
 Band_PlayTime  TIME (8) NOT NULL,
 Band_Storage_Location  VARCHAR2(30),
 Band_Agent VARCHAR2(25),
 Band_AE    VARCHAR2 (3),
 Venue_ID   VARCHAR2(5) REFERENCES venue(Venue_ID));

DROP TABLE fan cascade constraints;
CREATE TABLE fan(
 Fan_ID VARCHAR(6) PRIMARY KEY,
 Fan_Firstname  VARCHAR2(15),
 Fan_Surname    VARCHAR2(30),
 Fan_Contact_Number NUMERIC(9,2),
 Fan_Address    VARCHAR2(50),
 Fan_DOB    DATE NOT NULL,
 Band_ID    VARCHAR2(5) REFERENCES band(Band_ID));

When I run the code I get the following errors:

DROP TABLE band cascade constraints ORA-00942: table or view does not exist

CREATE TABLE band( Band_ID VARCHAR2(3) PRIMARY KEY, Band_Nam ORA-00907: missing right parenthesis

DROP TABLE fan cascade constraints ORA-00942: table or view does not exist

CREATE TABLE fan( Fan_ID VARCHAR(6) PRIMARY KEY, Fan_Firstna ORA-00942: table or view does not exist

Use this logic : If not Exist....Create Table then you will be able to drop it. NOTE: Once you dropped a tale, if you drop the same table again, it will throw an error saying table does not exist. — Cookie Monster, Dec 04 '17 at 02:50

Raul Sauco · Answer 1 · 2017-12-04T00:37:39.147

The problem is that you are trying to drop an object that doesn't exists on the database. The error message seems to indicate that table band and table fan don't exist.

I'm more familiar with MySql where you can add an IF EXISTS condition to the DROP TABLE statement but I believe that Oracle does not support that.

The easiest option for you will be to run both CREATE statements without the DROP statements once to make sure that the tables are created, after that you should be able to run the above script.

The more elegant solution could be to use an script to check if the table exists, if it does, drop it, otherwise create it directly, something like:

IF (SELECT * FROM TABLES WHERE name='band' > 0)
  DROP TABLE band

CREATE TABLE band (...)

There are more complete answers on this question

score 0 · Answer 2 · answered Dec 04 '17 at 06:18

Reason is you are trying to drop a table that doesn't exist.

Either comment out the drop table statements or if you want an automated version, Enclose all the drop statements in an Anonymous block and handle only the specific ORA-Error (ORA-00942).

Instead of

DROP TABLE band cascade constraints;
CREATE TABLE band..

Use :

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE band cascade constraints';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE <> -942 THEN
         RAISE;
      END IF;
END;
CREATE TABLE band..

score 0 · Answer 3 · answered Dec 04 '17 at 07:37

The ORA-00942 errors for the DROP TABLE statements are expected because you are indeed trying to drop tables which do not exist. So you don't need to worry about those.

The ORA-00907 error when you try to create the BAND table is because of this line:

"Venue_Contact_Number NUMERIC(9,2),"

numeric is not a valid Oracle data type. You meant NUMBER.

Then, because you didn't create the BAND table you get ORA-00942 when you try to create the FAN table. This is because FAN has a foreign key which references the BAND table.

As a beginner you need to learn how to read your own code with a cool eye so that you can spot the bugs. When you run a whole bunch of related statements in one script there is the risk that a single bug will cascade into a series of errors. When this happens, extract the first failing statement and work it on in isolation. Quite often removing that bug will clear up all the other fails, which should be the case here.

SQL: Missing right parenthesis and table or view does not exist errors

3 Answers3