[RESOLVED] missing or invalid option???

[nvidia]

Hi i am creating an 'ITEM' table with the following code:


-- Creation of Item Table
DROP TABLE ITEM CASCADE CONSTRAINTS;
CREATE TABLE ITEM
(


);



But when i'm trying to run it i have an error:

SQL> @table.sql
DROP TABLE ITEM CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist


Input truncated to 2 characters
unknown command ")" - rest of line ignored.


Also when i create a table i tend not to add any data before going to add information. Is this the reason y? I don't believe so but ? Can som1 help please:)

[chazzy]

i think your first error is quite clear - you're telling it to drop a table that doesn't exist. try changing it to something like "DROP TABLE IF EXISTS"

[nvidia]

Sorry i've tried to input that code but i still get an error message. This time it is different:

- Creation of Item Table
DROP TABLE IF EXISTS ITEM;


CREATE TABLE ITEM(




);


Error message:

SQL> @table.sql
DROP TABLE IF EXISTS ITEM
*
ERROR at line 1:
ORA-00933: SQL command not properly ended


Input truncated to 2 characters
unknown command ")" - rest of line ignored.

There should not be any errors for something like this??
Question: Without adding attributes to my 'item' table, does this make a difference?

[chazzy]

oh right. oracle doesn't have if exist.

if you really want to have that capability, you'll need to look at the table dba tables to see first if its there.

but in this case, is this really a necessary thing? you know the table won't exist, so why try to drop it?

[nvidia]

With the following code:



-- Creation of Item Table
DROP TABLE ITEM CASCADE CONSTRAINTS;

CREATE TABLE ITEM(
ITEMID NUMBER (6) NOT NULL,



);


I try to run it using the following commands
start table.sql or
@table.sql

but i get the following error message:


DROP TABLE ITEM CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist


Input truncated to 2 characters
unknown command ")" - rest of line ignored.

After the execute command, it should say somethnig like 'table created'. But it does not? Why.

[nvidia]

Hi i am trying to create an ITEM table with the following code:


--Creation of Item Table
DROP TABLE ITEM CASCADE CONSTRAINTS ;
CREATE TABLE ITEM(
ITEMID NUMBER(6) NOT NULL,
ITEMDESC VARCHAR2(30) NOT NULL,
);


i get this error message:

SQL> @table.sql
DROP TABLE ITEM CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist


)
*
ERROR at line 4:
ORA-00904: invalid column name


Can som1 tell me why please??

[aussie girl]

Create Table Item(
Itemid Number (6), Not Null

);

[RGL]

I'm kind of new to this and use Postgre SQL but I'll assume they are similar so
if I'm not mistaken you don't have to add the word constraints to the drop table command.

As for line 4, what is VarChar2? Do you need to use the 2?

[chazzy]

nvidia

On the last line of a create table, you don't use a comma. commas are used to separate the attributes given to the table and if there are no more, don't use a comma. ORA-00904 is because of this issue. Your drop clause has the same issue from your other thread. As I pointed out, you need to write a script/stored procedure that checks in the dba tables if the table exists or not.

I would really recommend that you get an OTN account, as they have a reference for what all of the various ORA and PLS errors mean. It's free too.

RGL

oracle has a varchar2, works pretty much like a varchar, but has better memory usage.

[nvidia]

Hi i have the following code:



DROP TABLE ITEM CASCADE CONSTRAINTS;
CREATE TABLE ITEM(
ITEMID NUMBER(6) NOT NULL
ITEMDESC VARCHAR2(30) NOT NULL,
);


I get the following error message:



SQL> @table.sql
DROP TABLE ITEM CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist


CREATE TABLE ITEM(
*
ERROR at line 1:
ORA-00922: missing or invalid option


Please not i have not popluted this table because i need to creat the table FIRST and give the response 'table created' before adding any data. This is what i belive should happen, and it has happened before. So why am i getting this error message?

[chazzy]

ORA-00922: missing or invalid option
Cause: An invalid option was specified in defining a column or storage clause. The valid option in specifying a column is NOT NULL to specify that the column cannot contain any NULL values. Only constraints may follow the datatype. Specifying a maximum length on a DATE or LONG datatype also causes this error.
Action: Correct the syntax. Remove the erroneous option or length specification from the column or storage specification.

It appears that you're missing the comma after "itemid" and as i pointed out in your other thread, you should not have a comma after "itemdesc"

[nvidia]

Thank you

[nvidia]

Hi i am trying to creat a table with the following code:


--Creation of Item Table
DROP TABLE ITEM CASCADE CONSTRAINTS;
CREATE TABLE ITEM(
ITEMID NUMBER (6) NOT NULL,
ITEMDESC VARCHAR2 (30) NOT NULL
CATEGORY VARCHAR2 (25),
);


and the error:

SQL> @table.sql
DROP TABLE ITEM CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist


CATEGORY VARCHAR2 (25),
*
ERROR at line 4:
ORA-00907: missing right parenthesis

Ok, when i go to line 4 'ITEMDESC VARCHAR2 (30) NOT NULL' and add the comma at the end 'ITEMDESC VARCHAR2 (30) NOT NULL,' i get a different message saying :

SQL> @table.sql
DROP TABLE ITEM CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist


)
*
ERROR at line 5:
ORA-00904: invalid column name

I don't understand why this is happening, do i need to put a comma at then end of every line? Confused:confused:

[chazzy]

nvidia

this is the same issue as your last 3 topics.

your create statement must have correct commas!


CREATE TABLE ITEM(
ITEMID NUMBER (6) NOT NULL,
ITEMDESC VARCHAR2 (30) NOT NULL,
CATEGORY VARCHAR2 (25)
);

[nvidia]

nvidia

this is the same issue as your last 3 topics.

your create statement must have correct commas!


CREATE TABLE ITEM(
ITEMID NUMBER (6) NOT NULL,
ITEMDESC VARCHAR2 (30) NOT NULL,
CATEGORY VARCHAR2 (25)
);


If i must have the correct commas, then why does the last line 'CATEGORY VARCHAR2 (25)' not have a comma? Is this because we are using the ; to end this statement? Meaning that the very last line does not need (,). Am i right in saying that?

[chazzy]

If i must have the correct commas, then why does the last line 'CATEGORY VARCHAR2 (25)' not have a comma? Is this because we are using the ; to end this statement? Meaning that the very last line does not need (,). Am i right in saying that?

I stated the reason above (now) [used to be in another thread]. The reason you don't use a comma on the last line is that the comma indicates that there are more commands to execute. When you miss a comma, it tries to parse 2 commands as 1 command and typically throws an error that doesn't make sense [sql parsers don't typically have validators].

So just to reiterate

Use a comma to separate items/statements inside of a single statement (like a create table statement)
don't put a comma after your last command, to indicate to the sql engine that it has no more commands to parse inside of the block.


I hope that this makes it clearer to you now.

I decided to merge your threads together, since they're all the same topic, just more debugging as you get deeper into your problem.

[nvidia]

Thank you, very much appreciated for your help and suggestions.

[nvidia]

Thank you very much for solving my problem. Sorry i have caused any annoyance bye the way. Again, thank you.

[chazzy]

nope, no annoyance at all!