| 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;
|
|
|
|
| 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?
|
|
|
|
| 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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
