Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

D
---------
28-MAY-11

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


SQL>
share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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)
values
( (select sysdate from dual) )

That could be extended for multiple columns:

insert into dates 
(
  id, 
  d,
  other_column
)
values
( 
  some_sequence.nextval, 
  (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

share|improve this answer
    
Why is the extra pair of brackets needed? Do they act like operators? –  Lazer May 29 '11 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) –  a_horse_with_no_name May 29 '11 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));. –  Jack Douglas May 29 '11 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. –  a_horse_with_no_name May 29 '11 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. –  Jack Douglas May 29 '11 at 14:05
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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