For example

SQL> create table dates(d date);

Table created.

SQL> insert into dates select sysdate from dual;

1 row created.

SQL> select * from dates;


SQL> insert into dates (d) values (select sysdate fom dual);
insert into dates (d) values (select sysdate fom dual)
ERROR at line 1:
ORA-00936: missing expression


3 Answers 3


If you want to use a SELECT statement where only a single value is allowed you need to put that SELECT statement into brackets:

insert into dates (d)
( (select sysdate from dual) )

That could be extended for multiple columns:

insert into dates 
  (select sysdate from dual), 
  (select max(some_col) from other_table)

You just need to make sure that the SELECT returns exactly one row and exactly one column

  • Why is the extra pair of brackets needed? Do they act like operators?
    – Lazer
    May 29, 2011 at 8:14
  • The brackets simply denotes a subquery. This syntax is specified in the SQL standard if I'm not totally mistaken (and works across most DBMS)
    – user1822
    May 29, 2011 at 12:33
  • oddly, the select can return no rows without an error, in which case a null gets inserted, eg insert into dates(d) values((select sysdate from dual where 1=2));. May 29, 2011 at 13:16
  • 1
    @Jack: yes, I think that's expected. If you'd use the same kind of subquery inside a select list, NULL is the only sensible "result" if the query returns nothing.
    – user1822
    May 29, 2011 at 13:54
  • good point. I think I'd prefer to specify explicitly whether to error or insert null if that were possible, but if there is going to be a default that is the sensible one. May 29, 2011 at 14:05

I think without knowing (I don't do Oracle so don't have a box to try this on) that the problem is here:

insert into dates (d) values (select sysdate fom dual)
                              ^^^^^^ <- this isn't a date; it expects a date here

Try this instead:

insert into dates (d) 
select sysdate fom dual

Because the parenthesis tells the grammar that the value inside the paren is a value, not a value producing statement. I suppose, if you really want a more indepth explanation that I could go and look that up for you in the SQL language spec.


I guess it is just a historic fact.

It is just not yet done that way.

From SQL Server I know that they are releasing such restrictions between 2005 and 2008 and we often haven step into problems when we run such scripts on 2005.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.