Home » SQL & PL/SQL » SQL & PL/SQL » Faulty Script
Faulty Script [message #205166] Thu, 23 November 2006 13:16 Go to next message
klofisch
Messages: 5
Registered: November 2006
Junior Member
I got the message (ORA-00907: Right Bracket is missing) when i execute the following script on Oracle 10gR2 XE

CREATE TABLE UmweltSpr (
  idUmweltSpr INTEGER UNSIGNED   NOT NULL ,
  Name VARCHAR(45)   NOT NULL ,
  Vorname VARCHAR(45)   NOT NULL ,
  Aktiv BOOL   NOT NULL ,
  LoginName VARCHAR(20)   NOT NULL ,
  LoginPassword VARCHAR(20)   NOT NULL   ,
PRIMARY KEY(idUmweltSpr));




CREATE TABLE Kennzahlen (
  idKennzahlen INTEGER UNSIGNED   NOT NULL ,
  KennzBez VARCHAR(20)      ,
PRIMARY KEY(idKennzahlen));




CREATE TABLE CostCenter (
  idCostCenter INTEGER UNSIGNED   NOT NULL ,
  UmweltSpr_idUmweltSpr INTEGER UNSIGNED   NOT NULL ,
  CostCentBez VARCHAR(20)   NOT NULL   ,
PRIMARY KEY(idCostCenter, UmweltSpr_idUmweltSpr)  ,
  FOREIGN KEY(UmweltSpr_idUmweltSpr)
    REFERENCES UmweltSpr(idUmweltSpr)

);


CREATE INDEX CostCenter_FKIndex1 ON CostCenter (UmweltSpr_idUmweltSpr);


CREATE INDEX IFK_Rel_01 ON CostCenter (UmweltSpr_idUmweltSpr);


CREATE TABLE KennzErf (
  idKennzErf INTEGER UNSIGNED   NOT NULL ,
  Kennzahlen_idKennzahlen INTEGER UNSIGNED   NOT NULL ,
  CostCenter_UmweltSpr_idUmweltSpr INTEGER UNSIGNED   NOT NULL ,
  CostCenter_idCostCenter INTEGER UNSIGNED   NOT NULL ,
  Wert DECIMAL   NOT NULL ,
  Jahr YEAR   NOT NULL   ,
PRIMARY KEY(idKennzErf, Kennzahlen_idKennzahlen, CostCenter_UmweltSpr_idUmweltSpr, CostCenter_idCostCenter)    ,
  FOREIGN KEY(Kennzahlen_idKennzahlen)
    REFERENCES Kennzahlen(idKennzahlen)

,
  FOREIGN KEY(CostCenter_idCostCenter, CostCenter_UmweltSpr_idUmweltSpr)
    REFERENCES CostCenter(idCostCenter, UmweltSpr_idUmweltSpr)

);


CREATE INDEX KennzErf_FKIndex1 ON KennzErf (Kennzahlen_idKennzahlen);
CREATE INDEX KennzErf_FKIndex2 ON KennzErf (CostCenter_idCostCenter, CostCenter_UmweltSpr_idUmweltSpr);


CREATE INDEX IFK_Rel_02 ON KennzErf (Kennzahlen_idKennzahlen);
CREATE INDEX IFK_Rel_03 ON KennzErf (CostCenter_idCostCenter, CostCenter_UmweltSpr_idUmweltSpr);






What is wrong


Thanks in advance
Peter

[Updated on: Thu, 23 November 2006 13:17]

Report message to a moderator

Re: Faulty Script [message #205173 is a reply to message #205166] Thu, 23 November 2006 13:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
UNSIGNED, BOOL and YEAR are not part of Oracle world. Kick them out of CREATE TABLE statements or change the datatype to a valid one.

Creating indexes IFK_Rel_01, IFK_REL_02 and IFK_REL_03 will fail because these columns are already indexed.

'CostCenter_UmweltSpr_idUmweltSpr' is too long (Oracle object names must be shorter than 31 characters).
Re: Faulty Script [message #205176 is a reply to message #205166] Thu, 23 November 2006 14:10 Go to previous messageGo to next message
klofisch
Messages: 5
Registered: November 2006
Junior Member
mhmhmh

I tried this

CREATE TABLE UmSpr (
  idUmSpr INTEGER NOT NULL ,
  Name VARCHAR(45) NOT NULL ,
  Vorname VARCHAR(45) NOT NULL ,
  Aktiv INTEGER NOT NULL ,
  LoginName VARCHAR(20) NOT NULL ,
  LoginPassword VARCHAR(20) NOT NULL   ,
PRIMARY KEY(idUmSpr));

CREATE TABLE Kennz (
  idKennzahlen INTEGER NOT NULL,
  KennzBez VARCHAR(20) NOT NULL,
PRIMARY KEY(idKennzahlen));

CREATE TABLE CostC (
  idCostCenter INTEGER NOT NULL ,
  UmSpr_idUmSpr INTEGER NOT NULL ,
  CostCentBez VARCHAR(20) NOT NULL   ,
PRIMARY KEY(idCostCenter, UmSpr_idUmSpr)  ,
  FOREIGN KEY(UmSpr_idUmSpr)
    REFERENCES UmSpr(idUmSpr));

CREATE TABLE KennzErf (
  idKennzErf INTEGER NOT NULL ,
  Kennz_idKennzahlen INTEGER NOT NULL ,
  CostC_idCostCenter INTEGER NOT NULL ,
  CostC_UmSpr_idUmSpr INTEGER NOT NULL ,
  Wert DECIMAL NOT NULL,
  Jahr INTEGER NOT NULL,
PRIMARY KEY(idKennzErf, Kennz_idKennzahlen, CostC_idCostCenter, CostC_UmSpr_idUmSpr),
  FOREIGN KEY(Kennz_idKennzahlen)
    REFERENCES Kennz(idKennzahlen),
  FOREIGN KEY(CostC_idCostCenter, CostC_UmSpr_idUmSpr)
    REFERENCES CostC(idCostCenter, UmSpr_idUmSpr)
);



But got this Error: "ORA-00911: Invalid indication / character" (Using the german interface)

mmh Whats up with it?
Re: Faulty Script [message #205178 is a reply to message #205176] Thu, 23 November 2006 15:11 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I ran your script (using copy-paste technique) and all tables were created successfully. How did you run it? Through SQL*Plus? Some GUI tool?

This is what Oracle says about the error:
Oracle
ORA-00911 invalid character

Cause: Special characters are valid only in certain places. If special characters other than $, _, and # are used in a name and the name is not enclosed in double quotation marks ("), this message will be issued. One exception to this rule is for database names; in this case, double quotes are stripped out and ignored.

Action: Remove the invalid character from the statement or enclose the object name in double quotation marks.

As I've already said, there's nothing wrong with it (as far as I'm concerned). Could you give some more information about how you did it?
Re: Faulty Script [message #205415 is a reply to message #205166] Fri, 24 November 2006 10:40 Go to previous messageGo to next message
klofisch
Messages: 5
Registered: November 2006
Junior Member
I am using the build-in SQL-Interface by Browser.
Re: Faulty Script [message #205420 is a reply to message #205415] Fri, 24 November 2006 11:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see; you are using iSQL*Plus. I tried that too; all tables were created successfully on my Oracle 10g release 10.2.0.1.0.

So, what happens when you run it using NOT iSQL*Plus, but pure SQL*Plus run at operating system command prompt as

OS> sqlplus username/password@database
SQL> <execute CREATE TABLE statements here>
Re: Faulty Script [message #205586 is a reply to message #205166] Sun, 26 November 2006 08:33 Go to previous messageGo to next message
klofisch
Messages: 5
Registered: November 2006
Junior Member
I tried to create the first table and got Error ORA-00907 again. .... strange thing
Re: Faulty Script [message #205601 is a reply to message #205586] Sun, 26 November 2006 12:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would you mind posting exact output? Such as this one:
SQL> CREATE TABLE UmSpr (
  2    idUmSpr INTEGER NOT NULL ,
  3    Name VARCHAR(45) NOT NULL ,
  4    Vorname VARCHAR(45) NOT NULL ,
  5    Aktiv INTEGER NOT NULL ,
  6    LoginName VARCHAR(20) NOT NULL ,
  7    LoginPassword VARCHAR(20) NOT NULL   ,
  8  PRIMARY KEY(idUmSpr));

Table created.

SQL>

I copied first CREATE TABLE statement from post #3 in this topic, pasted it into SQL*Plus session and executed. No ORA-00907 here, as you can see.

Therefore, I'd like to see your output and compare those two.

Also, you didn't mention your Oracle database version. Which one is it?
Re: Faulty Script [message #205603 is a reply to message #205166] Sun, 26 November 2006 12:30 Go to previous messageGo to next message
klofisch
Messages: 5
Registered: November 2006
Junior Member
latest 10g XE.

I c+p all tables separatly and it works
Re: Faulty Script [message #205606 is a reply to message #205603] Sun, 26 November 2006 12:43 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, right ... I'm sorry, you did say it was XE in the very first post.

Did you get this error while running (copy-pasted) script in iSQL*Plus or SQL*Plus? No matter which one I tried, all four tables were created successfully ...
Previous Topic: pragma serially_reusable
Next Topic: Procedure inside the Package
Goto Forum:
  


Current Time: Fri Dec 09 09:40:00 CST 2016

Total time taken to generate the page: 0.09909 seconds