OraFAQ Forum: SQL & PL/SQL » insert(record ) returns ORA-00947: not enough values

Home » SQL & PL/SQL » SQL & PL/SQL » insert(record ) returns ORA-00947: not enough values

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

insert(record ) returns ORA-00947: not enough values [message #178667]

Thu, 22 June 2006 03:12

zodiac_hh
Messages: 10
Registered: June 2006

Junior Member

Hello,

I created a record of a table using %rowtype and set some of the values of it, especially the ones that are not nullable in the table. Then I wanted to insert this record into the table, but I continue to receive "ORA-00947: not enough values". Do I have to set ALL of the record fields manually? I thought Oracle inserts NULL where no specific value is given.

PROCEDURE my_proc 
IS 
  my_record my_table%rowtype; -- Table contains 10 fields
BEGIN 
  my_record.field1 := 'TheValue'; 
  my_record.field2 := 'TheValue2'; 
  INSERT INTO my_table VALUES (my_record); 
END my_proc;

Report message to a moderator

Re: insert(record ) returns ORA-00947: not enough values [message #178673 is a reply to message #178667]

Thu, 22 June 2006 03:21

aciolac
Messages: 242
Registered: February 2006

Senior Member

but in case:

PROCEDURE my_proc 
IS 
  my_record my_table%rowtype; -- Table contains 10 fields
BEGIN 
  my_record.field1 := 'TheValue'; 
  my_record.field2 := 'TheValue2'; 
...................
  my_record.field10 := 'TheValue10'; 

  INSERT INTO my_table VALUES (my_record); 
END my_proc;

also give error?

Report message to a moderator

Re: insert(record ) returns ORA-00947: not enough values [message #178674 is a reply to message #178667]

Thu, 22 June 2006 03:22

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You need to remove the brackets from the insert statement if you're going to insert a whole record.

SQL> create table temp_insert (col_1 varchar2(10)
                              ,col_2 varchar2(10)
                              ,col_3 varchar2(10)
                              ,col_4 varchar2(10));

Table created.

SQL> 
SQL> declare
  2    my_record temp_insert%rowtype;
  3  BEGIN 
  4  
  5    my_record.col_1 := 'TheValue'; 
  6    my_record.col_2 := 'TheValue2'; 
  7    INSERT INTO temp_insert values my_record; 
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> select * from temp_insert;

COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
TheValue   TheValue2

[Updated on: Thu, 22 June 2006 03:23]

Report message to a moderator

Re: insert(record ) returns ORA-00947: not enough values [message #178675 is a reply to message #178667]

Thu, 22 June 2006 03:26

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Loose the brackets:

PROCEDURE my_proc 
IS 
  my_record my_table%rowtype; -- Table contains 10 fields
BEGIN 
  my_record.field1 := 'TheValue'; 
  my_record.field2 := 'TheValue2'; 
  INSERT INTO my_table VALUES my_record; 
END my_proc;

An alternative is to reference the individual elements:

PROCEDURE my_proc 
IS 
  my_record my_table%rowtype; -- Table contains 10 fields
BEGIN 
  my_record.field1 := 'TheValue'; 
  my_record.field2 := 'TheValue2'; 
  INSERT INTO my_table VALUES (my_record.field1, my_record.field2); 
END my_proc;

[EDIT]

second again...

MHE

[Updated on: Thu, 22 June 2006 03:27]

Report message to a moderator

Re: insert(record ) returns ORA-00947: not enough values [message #178682 is a reply to message #178674]

Thu, 22 June 2006 03:35

zodiac_hh
Messages: 10
Registered: June 2006

Junior Member

JRowbottom, thank you, that caused the error. I copied the insert-statement from a book (german O'Reilly PL/SQL pocket reference), they obviously have a flaw there.

Report message to a moderator

Previous Topic:	update tab with 3 million rows
Next Topic:	Please help me in this SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 19 00:43:02 CDT 2025