Oracle: insert without columns specification

I have an oracle table with a sequence and a trigger to autoincrement a column. Now I want to make an insert. Normally I should write:

INSERT INTO table (column1, column2,...) VALUES (value1, value2)

but I just want to insert a record with no default values. How to do this in Oracle?

`Mysql`: INSERT INTO table () VALUES ()

`Mssql`: INSERT INTO table default VALUES 

`Oracle:` INSERT INTO table (column1, column2,...) VALUES (default, default,...)

Is this the only way? I have开发者_开发技巧 to list all columns?

---

INSERT INTO table (column1) VALUES (default);

The rest will be defaulted!

---

In Oracle you don't HAVE to specify the columns but not doing so will leave you open to introducing bugs as and when your table definition changes.

You could insert with:

INSERT INTO t VALUES (value1, value2, value3);

This assumes the table t has three columns

It is far better and supportable to insert with:

INSERT INTO t (column1, column2, column3) VALUES (value1, value2, value3);

I wouldn't use PL/SQL (if you can help it) as you introduce context switching from PL/SQL to SQL and then back to PL/SQL again.

---

I missed this part on the first read:

I have an oracle table with a sequence and a trigger to autoincrement a column.

So I assume there is on PK column populated using the sequence, and the others all have default values. Given that, I would do this:

INSERT INTO table (pk_column) VALUES (NULL);

The trigger will override the NULL value (and if it doesn't for some reason, the insert will fail); and the other columns will be populated using defaults.

---

Is this the only way? I have to list all columns?

Yes. And it's good practice to always specify all columns in an INSERT statement that you want to supply values for.

---

There is only one case where you don't need to specify COLUMNS in Oracle and that is when you are inserting rows with values specified for all columns and that too in same sequence as the definition of table. In all other cases one needs to specify column list.

Example :

create table tt (id number, name varchar2(30));
insert into tt values (1,'AC');
insert into tt values ('2',1);

Especially note 2nd insert statement -> First parameter '2' is provided as String and expectation is number in ID column. Here does necessary casting and inserts 2 in ID column. But user may have wanted to insert '2' in NAME column and 1 as ID. With specifying column list Oracles helps us to avoid such errors.

Also there will be rarely any situation now a days where you DON"T need to specify column list because almost all tables created mostly have an auto-incremented unique ID column which is auto-filled via Trigger or because of IDENTITY columns and you provide values of rest of the columns only that means those column list must be provided else it will result in error.

In case this unique ID is generated and inserted by an application say generated in java application via Random number generation or Hibernate then as all column would be inserted column list might be avoided.