Home » SQL & PL/SQL » SQL & PL/SQL » ERROR at line 1: ORA-00947: not enough values (ISQL Plus 10.2.0.1.0)
ERROR at line 1: ORA-00947: not enough values [message #357038] Mon, 03 November 2008 11:42 Go to next message
whatevany
Messages: 2
Registered: November 2008
Junior Member
Hello all. When I try to insert the following values into my table I get this error message: ERROR at line1: ORA-00947: not enough values.

Insert Into Employees
VALUES ('Randy','Richards','25911745',2);

Below is the table I'm trying to insert it into:

 Create Table Employees
(
  FName Varchar(15) Not Null,
  Minit Char,
  Lname Varchar(15) Not Null,
  SSN Char(9) Not Null,
  BDate Date,
  Address Varchar(30),
  Sex Char,
  Salary Decimal(10,2),
  SuperSSN Char(9),
  DNO Int Not Null,
  Primary Key(SSN),
  Foreign Key(SuperSSN) References Employees(SSN),
  Foreign Key(DNO) References Department(DNumber)
)
;

Any information would be greatlty appreciated Smile
Re: ERROR at line 1: ORA-00947: not enough values [message #357041 is a reply to message #357038] Mon, 03 November 2008 11:50 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@whatevany,

You need to specify the column names as in the following Syntax:

Quote:
INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);



Try to go through INSERT Statements.

Hope this helps,

Regards,
Jo
Re: ERROR at line 1: ORA-00947: not enough values [message #357043 is a reply to message #357041] Mon, 03 November 2008 12:11 Go to previous messageGo to next message
whatevany
Messages: 2
Registered: November 2008
Junior Member
thank you Smile but now I get another error message Sad

ERROR at line 1:
ORA-02291: integrity constraint (LABAKER.SYS_C00153754) violated - parent key not found
This is the code I used:
Insert Into Employees
(FName,LName,SSN,DNO)
Values
 ('Randy','Richards','123456789',2);

thanks again!





Re: ERROR at line 1: ORA-00947: not enough values [message #357045 is a reply to message #357038] Mon, 03 November 2008 12:16 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
A couple of observations here.
This is not very good syntax for the creation of a table in Oracle.

We use VARCHAR2, not VARCHAR.
We give lengths to datatypes.

As for your new error...
02291, 00000,"integrity constraint (%s.%s) violated - parent key not found"
// *Cause: A foreign key value has no matching primary key value.
// *Action: Delete the foreign key or add a matching primary key.
Re: ERROR at line 1: ORA-00947: not enough values [message #357125 is a reply to message #357038] Tue, 04 November 2008 01:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to what joy_division said: a char(9) is not a good idea. People will curse you over it in the long run.
Re: ERROR at line 1: ORA-00947: not enough values [message #357145 is a reply to message #357038] Tue, 04 November 2008 02:50 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Use Number datatype instead of
 Salary Decimal(10,2), 
 DNO Int Not Null,



Regards,
Oli
Re: ERROR at line 1: ORA-00947: not enough values [message #357148 is a reply to message #357125] Tue, 04 November 2008 02:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Frank wrote on Tue, 04 November 2008 08:22
a char(9) is not a good idea. People will curse you over it in the long run.


As the column whose datatype is CHAR(9) is called "SSN" (Social Security Number?) and if it is expected that it ALWAYS contains 9 characters, would CHAR(9) instead of VARCHAR2(9) make a (significant) difference?
Re: ERROR at line 1: ORA-00947: not enough values [message #357156 is a reply to message #357148] Tue, 04 November 2008 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom Char Vs Varchar

Regards
Michel
Re: ERROR at line 1: ORA-00947: not enough values [message #357165 is a reply to message #357156] Tue, 04 November 2008 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Michel; "your" AskTom discussion leads to yet another one in which Tom says:
Tom Kyte
If the field is in fact ALWAYS 10 bytes long, using a CHAR will not hurt -- HOWEVER, it will not help either.

So I guess this answers my question good enough.
Re: ERROR at line 1: ORA-00947: not enough values [message #357191 is a reply to message #357165] Tue, 04 November 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, indeed.

Regards
Michel
Re: ERROR at line 1: ORA-00947: not enough values [message #357250 is a reply to message #357148] Tue, 04 November 2008 07:50 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Tue, 04 November 2008 03:57
Frank wrote on Tue, 04 November 2008 08:22
a char(9) is not a good idea. People will curse you over it in the long run.


As the column whose datatype is CHAR(9) is called "SSN" (Social Security Number?) and if it is expected that it ALWAYS contains 9 characters...


But as we can see, in the OP first example, it doesn't have 9 characters. So when the OP wants wants to find the SSN that is not of length 9, they will scratch theirs heads and wonder why the following doesn't return any rows.
select * from employees where length(ssn) <> 9;

Re: ERROR at line 1: ORA-00947: not enough values [message #357253 is a reply to message #357250] Tue, 04 November 2008 07:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Joy Division
But as we can see, in the OP first example, it doesn't have 9 characters

Heh, wrong!

In his first example we don't see anything:
Insert Into Employees
VALUES 
('Randy', 'Richards', '25911745', 2);
Nobody but OP knows (yeah, right ...) what value is supposed to be inserted into which column.

However, his second message suggests the opposite:
Insert Into Employees
(FName, LName, SSN, DNO)
 Values
('Randy', 'Richards', '123456789', 2);
                           ^
                           |
                          SSN (9 characters)
Re: ERROR at line 1: ORA-00947: not enough values [message #357263 is a reply to message #357253] Tue, 04 November 2008 08:51 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hence my advice still stands.
Don't use chars for anything other than non-nullable single-position fields.
Or just don't use chars at all..
Previous Topic: URL Escape using UTL_URL?
Next Topic: Stored Procedure Running Continuously
Goto Forum:
  


Current Time: Fri Dec 09 23:24:53 CST 2016

Total time taken to generate the page: 0.08063 seconds