OraFAQ Forum: SQL & PL/SQL » Create Tables Error Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Create Tables Error Statement (SQL Developer, 3.1.07, Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create Tables Error Statement [message #569942]

Sat, 03 November 2012 10:12

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

Hi. All I'm trying to do is create 4 simple tables. This is my first project using SQL so I'm totally new to it, not just SQL itself but database design/management, including foreign/primary key concepts. I think that's why the errors are being generated due to a duplication of foreign key names (perhaps?) but I really don't where I'm going wrong with the design structure in terms of the FK's and the relationships. Could someone please shed some light as to what or where I'm going wrong?

Thanks.

Added the error report in the attachment.

Create Table Hotel
(Hotel_No Char(4 Byte) Not Null,
H_Name VarChar2(20 Byte) Not Null,
H_Address VarChar2(30 Byte),
Constraint Hotel_PK Primary Key (Hotel_No));

Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));

Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No), 
Constraint Hotel_No_Room_No_FK (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));

Create Table Guest
(Guest_No Char(4) Not Null,
G_Name VarChar2(30),
G_Address VarChar2(35),
Constraint Guest_PK Primary Key (Guest_No));

Attachment: Error Messages.txt
(Size: 1.81KB, Downloaded 1069 times)

Report message to a moderator

Re: Create Tables Error Statement [message #569944 is a reply to message #569942]

Sat, 03 November 2012 10:22

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you use SQL*Plus instead you will directly know where the errors are:

SQL> Create Table Hotel
  2  (Hotel_No Char(4 Byte) Not Null,
  3  H_Name VarChar2(20 Byte) Not Null,
  4  H_Address VarChar2(30 Byte),
  5  Constraint Hotel_PK Primary Key (Hotel_No));

Table created.

OK

SQL> Create Table Room
  2  (Room_No VarChar2(4) Not Null,
  3  Hotel_No Char(4) Not Null,
  4  R_Type Char(1),
  5  R_Price Number(5,2),
  6  Constraint Room_PK Primary Key (Room_No, Hotel_No),
  7  Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No))
                       *
ERROR at line 7:
ORA-00904: : invalid identifier

FOREIGN KEY keywords are missing.
Database SQL Reference, constraint, see out_of_line_constraint syntax diagram.

Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No), 
Constraint Hotel_No_Room_No_FK (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));

Same error.

SQL> Create Table Guest
  2  (Guest_No Char(4) Not Null,
  3  G_Name VarChar2(30),
  4  G_Address VarChar2(35),
  5  Constraint Guest_PK Primary Key (Guest_No));

Table created.

OK

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #569958 is a reply to message #569944]

Sat, 03 November 2012 14:16

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

Thanks, silly mistakes on my behalf leaving out the "Foreign Key" on some lines. If you could, would you mind double checking the code again for me?

Foreign key relationships and/or logical errors not sure?

And thanks for the help.

Jasper.

Report message to a moderator

Re: Create Tables Error Statement [message #569960 is a reply to message #569958]

Sat, 03 November 2012 14:23

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the new code.

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #569961 is a reply to message #569958]

Sat, 03 November 2012 14:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

nitpicking advice below
do NOT ever use CHAR datatype; and ALWAYS use VARCHAR2 instead
With CHAR you have problems & questions why no rows ever get returned.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Create Tables Error Statement [message #569963 is a reply to message #569961]

Sat, 03 November 2012 14:32

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

Yes posting the code would help Smile

Smile

Any advice is good advice BlackSwan, I'll make those changes later.
Cheers.

Create Table Hotel
(Hotel_No Char(4 Byte) Not Null,
H_Name VarChar2(20 Byte) Not Null,
H_Address VarChar2(30 Byte),
Constraint Hotel_PK Primary Key (Hotel_No));

Create Table Room
(Room_No VarChar2(4 Byte) Not Null,
Hotel_No Char(4 Byte) Not Null,
R_Type Char(1 Byte),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Hotel_No_FK  Foreign Key (Hotel_No) References Hotel(Hotel_No));

Create Table Booking
(Hotel_No Char(4 Byte) Not Null,
Guest_No Char(4 Byte) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4 Byte),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No), 
Constraint Hotel_No_Room_No_FK  Foreign Key (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Hotel_No_FK  Foreign Key (Hotel_No) References Hotel(Hotel_No));
 
Create Table Guest
(Guest_No Char(4 Byte) Not Null,
G_Name VarChar2(30 Byte),
G_Address VarChar2(35 Byte),
Constraint Guest_PK Primary Key (Guest_No));

Report message to a moderator

Re: Create Tables Error Statement [message #569964 is a reply to message #569963]

Sat, 03 November 2012 14:59

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Follow BlackSwan advice
2/ Use CHAR and not BYTE for string length unit, you will avoid many problems and I bet you think in characters and not in bytes to define your name
3/ You cannot reference a primary key/table you didn't previously created, so GUEST must be created before BOOKING
4/ I'd add Room_No in the primary key of BOOKING: a guest can change his room or can book 2 rooms during his stay
5/ 2 constraints can't have the same name:

SQL> Create Table Hotel
  2  (Hotel_No Char(4 Byte) Not Null,
  3  H_Name VarChar2(20 Byte) Not Null,
  4  H_Address VarChar2(30 Byte),
  5  Constraint Hotel_PK Primary Key (Hotel_No));

Table created.

SQL> 
SQL> Create Table Room
  2  (Room_No VarChar2(4 Byte) Not Null,
  3  Hotel_No Char(4 Byte) Not Null,
  4  R_Type Char(1 Byte),
  5  R_Price Number(5,2),
  6  Constraint Room_PK Primary Key (Room_No, Hotel_No),
  7  Constraint Hotel_No_FK  Foreign Key (Hotel_No) References Hotel(Hotel_No));

Table created.

SQL> 
SQL> Create Table Guest
  2  (Guest_No Char(4 Byte) Not Null,
  3  G_Name VarChar2(30 Byte),
  4  G_Address VarChar2(35 Byte),
  5  Constraint Guest_PK Primary Key (Guest_No));

Table created.

SQL> Create Table Booking
  2  (Hotel_No Char(4 Byte) Not Null,
  3  Guest_No Char(4 Byte) Not Null,
  4  Date_From Date Not Null,
  5  Date_To Date,
  6  Room_No VarChar2(4 Byte),
  7  Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
  8  Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No), 
  9  Constraint Hotel_No_Room_No_FK  Foreign Key (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
 10  Constraint Hotel_No_FK  Foreign Key (Hotel_No) References Hotel(Hotel_No));
Constraint Hotel_No_FK  Foreign Key (Hotel_No) References Hotel(Hotel_No))
           *
ERROR at line 10:
ORA-02264: name already used by an existing constraint

Maybe you can name your foreign keys from the child and parent table relationship (just an example):
room_hotel_fk
booking_hotel_fk

Regards
Michel

[Updated on: Sat, 03 November 2012 15:01]

Report message to a moderator

Re: Create Tables Error Statement [message #569968 is a reply to message #569964]

Sat, 03 November 2012 16:24

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

Its working perfectly.

Thanks so much for the help its really appreciated.

And the working code below:

Create Table Hotel
(Hotel_No Char(4) Not Null,
H_Name VarChar2(20) Not Null,
H_Address VarChar2(30),
Constraint Hotel_PK Primary Key (Hotel_No));

Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Room_Hotel_Fk Foreign Key (Hotel_No) References Hotel(Hotel_No));

Create Table Guest
(Guest_No Char(4) Not Null,
G_Name VarChar2(30),
G_Address VarChar2(35),
Constraint Guest_PK Primary Key (Guest_No));

Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Room_No),
Constraint Booking_Guest_FK Foreign Key (Guest_No) References Guest(Guest_No), 
Constraint Booking_Hotel_Room_FK  Foreign Key (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Booking_Hotel_FK  Foreign Key (Hotel_No) References Hotel(Hotel_No));

[Updated on: Sat, 03 November 2012 16:26]

Report message to a moderator

Re: Create Tables Error Statement [message #569969 is a reply to message #569968]

Sat, 03 November 2012 16:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

sooner or later you will REGRET using CHAR datatypes

Report message to a moderator

Re: Create Tables Error Statement [message #569970 is a reply to message #569969]

Sat, 03 November 2012 17:08

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

Yes I forgot to change the data type. Too preoccupied with the 'byte' mistake from previous Smile

Smile

thank you for reminding.

Report message to a moderator

Re: Create Tables Error Statement [message #569977 is a reply to message #569970]

Sun, 04 November 2012 01:50

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Also Booking PK should also contain Date_From as it did in the previous versions (so 4 columns).
And it is a central table, I'd make it an IOT (Index-Organized Table) with all columns in the PK.

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #569980 is a reply to message #569942]

Sun, 04 November 2012 02:40

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

Are you open to even more advice? If so:

Primary key constraints require an index. Your syntax is creating an index implicitly, and it will be a unique index. Check this with

select index_name,uniqueness from user_indexes;

I strongly believe that best practice is to use non-unique indexes: they give you more flexibility later. To create explicitly a non-unique index, define your constraints like this:

orcl> Create Table Hotel
  2  (Hotel_No Char(4) Not Null,
  3  H_Name VarChar2(20) Not Null,
  4  H_Address VarChar2(30),
  5  Constraint Hotel_PK Primary Key (Hotel_No) using index (create index hotel_pk on hotel(hotel_no)));

Table created.

orcl> select uniqueness from user_indexes where index_name='HOTEL_PK';

UNIQUENES
---------
NONUNIQUE

orcl>

Report message to a moderator

Re: Create Tables Error Statement [message #569991 is a reply to message #569980]

Sun, 04 November 2012 08:52

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

John Watson wrote on Sun, 04 November 2012 08:40

Are you open to even more advice?

I am...

Razz

Quote:

I strongly believe that best practice is to use non-unique indexes: they give you more flexibility later.

OK, I'm really confused - why would you want a non-unique index
on a primary key?

Quote:

5 Constraint Hotel_PK Primary Key (Hotel_No) using index (create index hotel_pk on hotel(hotel_no)));

And again, why on earth would you create an index with the same
name as the constraint?

I would do CREATE INDEX Hotel_PK_IDX on Hotel(Hotel_No) - am I wrong,
and if so, why?

Any input appreciated.

Paul...

Report message to a moderator

Re: Create Tables Error Statement [message #570007 is a reply to message #569991]

Sun, 04 November 2012 12:20

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

OK, I'm really confused - why would you want a non-unique index
on a primary key?

This allows you to make the constraint deferrable and even deferred that is the control of the primary key is postpone to the end of the transaction instead of the end of the statement.

Quote:

And again, why on earth would you create an index with the same
name as the constraint?

It is easier for maintenance to have the same name. Note that this is what Oracle does by default.

Quote:

I would do CREATE INDEX Hotel_PK_IDX on Hotel(Hotel_No) - am I wrong,
and if so, why?

If you already have a primary key (as in the script above) the index already exists.

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570009 is a reply to message #569991]

Sun, 04 November 2012 12:28

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

Quote:

OK, I'm really confused - why would you want a non-unique index
on a primary key?

Common question! Non-unique indexes give much better manageability. Try this:

select index_name from user_indexes;
alter table hotel disable constraint hotel_pk;
select index_name from user_indexes;
alter table hotel enable constraint hotel_pk;
select index_name from user_indexes;

You'll find that doing it your way, with an implicitly created unique index, Oracle has to drop the index when you disable the constraint and re-create it when you enable it. This can be disastrous: massive downtime. My way, the explicitly created non-unique index survives. Don't believe me - check it out.
Another reason is that you might want to create the constraint as deferrable (look that up: I believe that best practice is that constraints should be defined as DEFERRABLE INITIALLY IMMEDIATE) and you cannot defer a constraint that is enforced with a unique index.
Quote:

And again, why on earth would you create an index with the same
name as the constraint?

I would do CREATE INDEX Hotel_PK_IDX on Hotel(Hotel_No) - am I wrong,
and if so, why?

No opinion, this is entirely up to you and your naming conventions.
--
Edit: Argggg! Michel got in while I was typing! But we agree on this.

[Updated on: Sun, 04 November 2012 12:30]

Report message to a moderator

Re: Create Tables Error Statement [message #570033 is a reply to message #569942]

Sun, 04 November 2012 23:41

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

Great advice thank you.
Mind helping out with one more problem? I'm trying to insert some sample data into the tables for testing queries etc. However, its not being inserted and an error message is being generated (below). A few days ago when I tried inserting the identical 'sample text' (attached) into four 'dummy' tables the data populated all the records and everything worked great.Over the past day I have tried playing around with the data types, double checked the sample data for inconsistency, removed all foreign keys, tried using the original data for the original create table statements but nothing has worked and I keep receiving the below error message. A little bit frustrating.

It would be really interesting to know why it's not allowing me to insert that data because I have no idea. As i said have tried everything I know. The attachment is the 'sample data' im trying to insert.

ERROR MESSAGE:
table BOOKING truncated.
table ROOM truncated.
table GUEST truncated.
table HOTEL truncated.
Error starting at line 6 in command:
insert into hotel values
('H100','Sheraton','Melbourne')
Error at Command Line:6 Column:0
Error report:
SQL Error: ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [373], [BIN$XQQxCzAhRPCuX323VsAKNA==$0], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
*Action: Report as a bug - the first argument is the internal error number

Attachment: Data Sample.txt
(Size: 2.31KB, Downloaded 1015 times)

Report message to a moderator

Re: Create Tables Error Statement [message #570038 is a reply to message #570033]

Sun, 04 November 2012 23:55

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support
Have a look at alert.log and trace files.
You can also read this article: Troubleshooting Internal Errors.

And you have to give the Oracle database version number, with 4 decimals:

select * from v$version;

Once again, Use SQL*Plus and copy and paste your session.
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

We help you much in this topic, please help us to help you in following the guide.

Regards
Michel

[Updated on: Sun, 04 November 2012 23:57]

Report message to a moderator

Re: Create Tables Error Statement [message #570054 is a reply to message #570033]

Mon, 05 November 2012 04:00

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

FloorPlay wrote on Mon, 05 November 2012 05:41

Great advice thank you.
Mind helping out with one more problem? I'm trying to insert some sample data into the tables for testing queries etc. However, its not being inserted and an error message is being generated (below).

When you are faced with this sort of problem, try inserting
the data table by table - find the table that's causing the
problem and then go record by record - that's what I did.

You're violating the PK of Booking trying to insert the 3rd
booking record.

Also, the tool you're using appears to give very crytic error
messages - have you tried SQL Developer from Oracle? It's free
and works on Windows and Linux. It pointed me straight at the
problem - no bizarre ORA-00600/ORA-07445/ORA-03113 errors for
me.

HTH,

Paul...

[Updated on: Mon, 05 November 2012 04:06]

Report message to a moderator

Re: Create Tables Error Statement [message #570057 is a reply to message #570054]

Mon, 05 November 2012 04:15

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The ORA-600 error comes from the server, it does not depend on the client tool (or has a very tiny likelyhood).

It is better to use SQL*Plus, you cannot post a SQL Developer session in text mode, can you? And SQL*Plus directly spots to the point it found an error or an expected pattern.

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570059 is a reply to message #570057]

Mon, 05 November 2012 04:52

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

Michel Cadot wrote on Mon, 05 November 2012 10:15

The ORA-600 error comes from the server, it does not depend on the client tool (or has a very tiny likelyhood).

Salut Michel,

I would of course expect all messages shown by a client to come
from the server - that's the function of a client after all.

This is why I'm a bit puzzled - I got no such ORA-600 error.
Was the attachment changed?

Quote:

It is better to use SQL*Plus, you cannot post a SQL Developer session in text mode, can you?

Voilà

Error starting at line 5 in command:
insert into booking values
 ('H100','G002','1-AUG-12','2-AUG-12','2')
Error report:
SQL Error: ORA-00001: unique constraint (SYS.BOOKING_PK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
1 rows inserted.

Quote:

And SQL*Plus directly spots to the point it found an error or an expected pattern.

See output from SQL Developer - right on the money!

Salutations.

Paul...

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570060 is a reply to message #570059]

Mon, 05 November 2012 05:18

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I got no such ORA-600 error.

Have you the same version and patch level than OP? If not, you may not have the same error. (Note: we have not this OP information).

Quote:

See output from SQL Developer - right on the money!

1/ This is not a syntax error, so the example is irrelevant to what we are talking about.
2/ Execute OP's original code as I did and show where SQL Developer puts the error pointer.
3/ I love the "Error message" and the "1 row inserted" one. Is there an error or is there a row inserted? How a row can be inserted if there is a primary key violation?

Give me back my money.

Regards
Michel

[Updated on: Mon, 05 November 2012 05:18]

Report message to a moderator

Re: Create Tables Error Statement [message #570081 is a reply to message #570060]

Mon, 05 November 2012 08:46

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

I shouldn't be laughing I'm either over analysising the situation or I'm an idiot.

If I simply take out this line of code

Constraint Booking_Hotel_Room_FK  Foreign Key (Hotel_No, Room_No) References Room(Hotel_No, Room_No),

from the 'Booking' table I'm able to insert all the data with no problem. But now I don't think the the relationships will work correctly?? Am i right?

The code for the tables is below:

Please tell me this correct.

Create Table Hotel
(Hotel_No Char(4) Not Null,
H_Name VarChar2(20) Not Null,
H_Address VarChar2(30),
Constraint Hotel_PK Primary Key (Hotel_No));

Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Room_Hotel_Fk Foreign Key (Hotel_No) References Hotel(Hotel_No));

Create Table Guest
(Guest_No Char(4) Not Null,
G_Name VarChar2(30),
G_Address VarChar2(35),
Constraint Guest_PK Primary Key (Guest_No));

Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From, Room_no),
Constraint Booking_Guest_FK Foreign Key (Guest_No) References Guest(Guest_No), 
Constraint Booking_Hotel_FK Foreign Key (Hotel_No) References Hotel(Hotel_No));

Report message to a moderator

Re: Create Tables Error Statement [message #570083 is a reply to message #570081]

Mon, 05 November 2012 08:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

& the CHAR(4) still exist.
Sad

Sad

Report message to a moderator

Re: Create Tables Error Statement [message #570087 is a reply to message #570083]

Mon, 05 November 2012 09:11

FloorPlay
Messages: 12
Registered: November 2012

Junior Member

lol did u get my PM black swan?

All Im trying to do at this moment is have the relationships setup perfectly and to have the ability to insert data. As soon as that's all done I will change what you suggested, along with adding the indexs etc. Did u say change all data types to varcahr2? What attention span? Smile

Smile

EDIT: Did Michel not say to leave them as Char?

2/ Use CHAR and not BYTE for string length unit

[Updated on: Mon, 05 November 2012 09:18]

Report message to a moderator

Re: Create Tables Error Statement [message #570091 is a reply to message #570087]

Mon, 05 November 2012 10:06

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You misunderstood what I said:
Quote:

1/ Follow BlackSwan advice

that is use VARCHAR2 not CHAR (I should repeat it explicitly).

Quote:

2/ Use CHAR and not BYTE for string length unit

That is VARCHAR2(10 CHAR) not VARCHAR2(10 BYTE) as in your code.

Regards
Michel

[Updated on: Mon, 05 November 2012 10:07]

Report message to a moderator

Re: Create Tables Error Statement [message #570093 is a reply to message #570060]

Mon, 05 November 2012 10:25

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

[quote title=Michel Cadot wrote on Mon, 05 November 2012 11:18]Quote:

I got no such ORA-600 error.

Quote:

Have you the same version and patch level than OP? If not, you may not have the same error. (Note: we have not this OP information).

For me the version I tested on is

SQL> select *  from v$version;

BANNER
--------------------------------------------------------

Oracle Database 11g Express Edition Release 11.2.0.2.0 -
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Quote:

1/ This is not a syntax error, so the example is irrelevant to what we are talking about.

? The sample data and table structure are so simple, I imagine they
would work back to at least 8i? I presume the sample data is what
you mean by "example".

Quote:

2/ Execute OP's original code as I did and show where SQL Developer puts the error pointer.

I looked, and can't figure out how you executed the code. I just ran
the scripts that the OP said were finally correct and then downloaded
the attached text file and ran the SQL in that. What did you do?

Quote:

3/ I love the "Error message" and the "1 row inserted" one. Is there an error or is there a row inserted? How a row can be inserted if there is a primary key violation?

That "1 row inserted" refers to the subsequent row, for which there
was no problem.

Quote:

Give me back my money.

pas question! (no question) Smile

Smile

Salutations.

Paul...

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570094 is a reply to message #570093]

Mon, 05 November 2012 11:07

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

That "1 row inserted" refers to the subsequent row, for which there was no problem.

So this is very clear when you 1000 statements to know which ones failed and which ones succeeded!

Once again, execute the FIRST script OP posted in the FISRT post of this topic and post me the result you have in text.

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570107 is a reply to message #570094]

Mon, 05 November 2012 12:49

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

[quote title=Michel Cadot wrote on Mon, 05 November 2012 17:07]Quote:

That "1 row inserted" refers to the subsequent row, for which there was no problem.

So this is very clear when you 1000 statements to know which ones failed and which ones succeeded!

Well, in the example I posted, it give line and verse - pointing exactly
to the error.

Quote:

Once again, execute the FIRST script OP posted in the FISRT post of this topic and post me the result you have in text.

I'm not sure I understand the point of doing this, since the OP
made it clear that there were problems, which were then corrected
thanks to group members - why am I running code that I know to
be wrong?

In any case, here are the results I obtained using SQL Developer
and Oracle 11.2.0.2.0 XE - I don't appear to have the problem
reported by the OP.

table HOTEL created.

Error starting at line 7 in command:
Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No))
Error at Command Line:13 Column:23
Error report:
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error starting at line 15 in command:
Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Hotel_No, Guest_No, Date_From),
Constraint Guest_No_FK Foreign Key (Guest_No) References Guest(Guest_No), 
Constraint Hotel_No_Room_No_FK (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No))
Error at Command Line:23 Column:31
Error report:
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
table GUEST created.

HTH,

Paul...

Quote:

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570112 is a reply to message #570107]

Mon, 05 November 2012 13:46

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So this is as clear as mud compare to SQL*PLus output...

Compare "Error at Command Line:23 Column:31" when you don't know which line is line 23 (without speaking about thuosand lines script and it tells you error at line 1546, do you count each line?)
to

SQL> Create Table Room
  2  (Room_No VarChar2(4) Not Null,
  3  Hotel_No Char(4) Not Null,
  4  R_Type Char(1),
  5  R_Price Number(5,2),
  6  Constraint Room_PK Primary Key (Room_No, Hotel_No),
  7  Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No));
Constraint Hotel_No_FK (Hotel_No) References Hotel(Hotel_No))
                       *
ERROR at line 7:
ORA-00904: : invalid identifier

See the * that points to the error. This is what I called directly point to, not a line and column number.

Regards
Michel

[Updated on: Mon, 05 November 2012 13:47]

Report message to a moderator

Re: Create Tables Error Statement [message #570115 is a reply to message #570112]

Mon, 05 November 2012 14:17

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

Salut encore Michel (Hi again),

Quote:

Compare "Error at Command Line:23 Column:31" when you don't know which line is line 23 (without speaking about thuosand lines script and it tells you error at line 1546, do you count each line?)

You obviously haven't used SQL Developer Smile

Smile

The line number and column number
are visible in the IDE and navigating to them is easy peasy even if the
script is 1000 lines long - and leads one to the star of SQL*Plus.

Given that SQL*Plus and SQL Developer are both made by the same
people - it's not surprising that they give the same results, albeit
in slightly different ways.

I don't want to get embroiled in a religous war over which is the
best tool and I respect your sterling work on these groups. I'm grateful
to have learned about the benefits of non-unique indexes on
PK's in this thread and shall remember that - otherwise, I shall
not contribute further (but shall to other threads).

Salutations.

Paul...

Quote:

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570119 is a reply to message #570115]

Mon, 05 November 2012 14:40

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The problem is not the tool you use everyday but the one you use to post.
Here we cannot see your screen with line numbers.

Quote:

It is better to use SQL*Plus, you cannot post a SQL Developer session in text mode, can you?

Regards
Michel

Report message to a moderator

Re: Create Tables Error Statement [message #570122 is a reply to message #570119]

Mon, 05 November 2012 14:54

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

Michel Cadot wrote on Mon, 05 November 2012 20:40

The problem is not the tool you use everyday but the one you use to post.
Here we cannot see your screen with line numbers.

OK - the light has dawned (j'aperçois la lumière!) - others can't interact
if I don't use SQL*Plus - I'll use it in future in my tests and responses
to posts here. Not everybody has/uses SQL Developer (although they should! Smile

Smile

)

Salutations.

Paul...

Quote:

Regards
Michel

Report message to a moderator

Previous Topic:	Multiplication Funtion In ORACLE
Next Topic:	Sum(cost) basing on id entered (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 27 00:45:10 CDT 2025