We have a table which we want to extend with new columns,
by doing so we will get the SQLException "Not enough values"
when the existing code execute an INSERT statement without
the new columns values.

Is there any way to specify a default value for a column or create
a trigger which solves this? What command should I use to modify the
existing table if this is possible?

/Kenneth

Hi Kenneth,

I believe all you have to do is alter your Insert statements. There is a
form of the Insert statement that lets you specify only the columns you want
to insert.

This sample is from SQL Plus so you can see the form of Insert you can use
for that table with the new columns. I used the DEPT table from the
scott/tiger schema. I hope this helps you.

This code will cause the error because there are 3 columns in the table:
INSERT INTO dept

VALUES (88, 'Programming');

ORA-00947: not enough values



This code will work. Here you specify only the columns you want to insert:

INSERT INTO dept

(deptno, dname)

VALUES (88, 'Programming');

1 row inserted

"Emad-ud-deen Richard Leiman & Eva Fithria Leiman" <a@a.com> wrote in
message news:djQWb.34065$Kh3.442 (AT) newssvr33 (DOT) news.prodigy.com...
I would recommend to NEVER use insert-statements like the first example, for
exactly for the reason the OP has encountered. In my current job, we won't
let code like that pass the QA/code review.
In a real-world situation, the chance that your table will at some time have
columns added, removed, of even just switched will almost equal 1.

Theo

To utilize default values to be inserted into the table, I would recreate
the column with a DEFAULT clause specifying which value to use.

and as mentioned by others I would also explicitly state in my code which
columns are being modified in the insert statement.

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DEFAULT 'DEFAULT_VALUE';



"Theo" <theoa_nl (AT) hotmail (DOT) com> wrote