This statement is found in oracle's training material:

INSERT INTO (SELECT employee_id, last_name
FROM employee
WHERE department_id=50)
VALUES(9999,'peter');

The above subquery is interpreted as the list columns to be inserted with
values. But what is the point of having this syntax? And why bother
putting in a WHERE clause? Isnt it good enough to list just the column names
instead of a doing a subquery which is nothing more than indicating what
columns are involved??

Although by the looks of it ... its a poor example.
However, its trying to introduce to you the insert into (view) ...
syntax.

In the above example, the view is a dynamic view.
Probably later on its trying to introduce to you the "with check
option" etc. syntax.

So the clue for you is probably to ... read on!

Anurag

Maybe the point is to be obscure?

Actually, it is an in-line view.

If the PK is just the employee_id, then this is definitely obscure.
It's obvious the department_id is never assigned a value (unless there
is some behind the scenes trigger).

Hmm, is this some way of avoiding a trigger on the employee table?

1 create table emp (
2 emp_id number PRIMARY KEY,
3 emp_nm varchar(20),
4 dept_id number ,
5* other varchar(10) )
SQL> /

Table created.

SQL> insert into emp values (1, 'adam', 50,'first');

1 row created.

SQL> insert into emp values (2, 'brent', 30,'second');

1 row created.

SQL> insert into emp values (3, 'cary',10, NULL);

1 row created.

SQL> create or replace trigger emp_in before insert on emp
2 for each row
3 begin
4 :new.dept_id := 50;
5 end;
6 /

Trigger created.

SQL> select emp_id , emp_nm from emp
2 where dept_id=50;

EMP_ID EMP_NM
---------- --------------------
1 adam

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10

SQL> insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL> insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL>insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL>insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL>

Nope, the trigger still fires. Might be a job-security-thru-obscurity
ploy? I see no reason to use that versus the simple:
insert into employee VALUES(9999,'peter');

HTH,
ed