Error: ORA-00922: missing or invalid option creating colums

Toad for Oracle & DevOps Toolkit
1

Using the create table section and getting this error. What is the most likely issue? table name spaces? character or number lengths?

CREATE TABLE ANDY.Andy EPS Data Warehouse
(
Pharmacy_Name VARCHAR2(1),
Pharmacy_State VARCHAR2(1),
Pharmacy_Zip VARCHAR2(1),
Pharmacy_NPI VARCHAR2(1),
TX_NUMBER VARCHAR2(1),
Pick_up_date DATE,
Prescriber_DEA VARCHAR2(1),
Prescriber_NPI VARCHAR2(1),
Prescriber_Last_Name VARCHAR2(1),
Prescriber_City VARCHAR2(1),
Prescriber_State VARCHAR2(1),
Prescriber_ZIP VARCHAR2(1),
DRUG_NAME VARCHAR2(1),
NDC VARCHAR2(1),
generic_indicator VARCHAR2(1),
FILL_DATE VARCHAR2(1),
FILL_LOCATION VARCHAR2(1),
rx_source VARCHAR2(1),
refill_source VARCHAR2(1),
SCHEDULE VARCHAR2(1),
RX_NUMBER NUMBER,
RX_COM_ID VARCHAR2(1),
DIAGNOSIS_CODE VARCHAR2(1),
Pharmacist VARCHAR2(1),
drug_cost NUMBER,
submitted_AWP NUMBER,
NADAC NUMBER,
WAC NUMBER,
total_price NUMBER,
Paid_ingredient_cost NUMBER,
patient_copay NUMBER,
Paid_dispensing_fee NUMBER,
Margin NUMBER,
Plan_Name VARCHAR2(1),
CARRIER_CODE VARCHAR2(1),
PCN VARCHAR2(1),
BIN VARCHAR2(1),
Group_Number VARCHAR2(1),
qty_dispensed NUMBER,
DAYS_SUPPLY NUMBER,
Days_in_will_call NUMBER,
POS_SOLD_DATE DATE

2

Hi Andy, (I assume!)

It's the spaces in the table name that causes the error you are seeing. Basically the table name will be ANDY.ANDY and the "EPS" and "Data" and "Warehouse" parts of the proposed table name are being seen as additional options for the create table statement. They are obviously invalid.

You have two options:

  • Wrap the table name in double quotes - CREATE TABLE ANDY."Andy EPS Data Warehouse" which will give you what you requested BUT you will always have to reference the table name in double quotes and in EXACTLY the same mix of upper and lower case characters used here;

  • Use underscores - CREATE TABLE ANDY.Andy_EPS_Data_Warehouse which will allow you to use upper, lower or mixed case in queries etc.

You could use both double quotes and underscores, of course.

I avoid using double quoted anything in my objects as it really is a right royal PITA I'm afraid.

HTH

Cheers,
Norm. [TeamT]

3

Yep. That was it. I assumed using a graphical interface instead just writing sql, it would would just change invalid names for me. That's what I get for assuming lol.