Problem solve Get help with specific problems with your technologies, process and projects.

Inserting whole rows into tables without denoting column names

In Oracle 8i, I want to insert a whole row using "INSERT INTO TABLE VALUES(x,x,x)". But, a "Before Insert" trigger creates a new (sequential) primary key. My question is, what value should I use for the primary key on the insert or do I really need to use a "INSERT INTO TABLE (COl1,COL2,COL3) VALUES (x,x,x);" instead? I'm trying to avoid the latter.

You'll either need to do one of two things. Either denote the column names or "spoof" the system by employing a workaround. Since the first option is unacceptable, let's see how you can get around it!

First, I'll create a table and a sequence.

ORA9I SQL> create table test1 (id number, name

Table created.

ORA9I SQL> create sequence test1_seq;

Sequence created.
Then, I'll create a trigger for this table.
ORA9I SQL> create trigger test1_trig
  2  before insert on test1 for each row
  3  declare
  4     new_id number;
  5  begin
  6     select test1_seq.nextval into new_id from
  7     :new.id := new_id;
  8  end;
  9  /

Trigger created.
Now, I'll create a view with only the NAME column of that table. This leaves out the ID column of the table.
ORA9I SQL> create view test1_view as select name from

View created.
Instead of inserting directly into the table, insert into the view instead!!!
ORA9I SQL> insert into test1_view values ('bob');

1 row created.
Checking the table, we can see that a sequence value was used here.
ORA9I SQL> select * from test1;

        ID NAME
---------- --------------------
        52 bob
Just to double check, let's insert one more row to verify the sequence number is incrementing.
ORA9I SQL> insert into test1_view values ('bob');

1 row created.

ORA9I SQL> select * from test1;

        ID NAME
---------- --------------------
        52 bob
        53 bob
There it is!!!

For More Information

  • What do you think about this answer? E-mail the editors at [email protected]chDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture