How to add multiple rows using a single insert statement. Using the way in the description

Question

SQL> insert into patient
2  values('&pno','&pname','&title',&dob,&children,'&gp');
Enter value for pno: p3
Enter value for pname: mansell
Enter value for title: mr
Enter value for dob: 23-may-61
Enter value for children: 2
Enter value for gp: Dr.Williams
old   2: values('&pno','&pname','&title',&dob,&children,'&gp')
new   2: values('p3','mansell','mr',23-may-61,2,'Dr.Williams')
values('p3','mansell','mr',23-may-61,2,'Dr.Williams')

ERROR at line 2:
ORA-00984: column not allowed here

This used to work.i really dont understand how to make it work..

Ben · Accepted Answer · 2012-08-26 13:00:15Z

This error is almost certainly caused by the column dob. You're not inserting a date or a date literal, you're inserting a random string, that is being interpreted as a column name.

As a general rule always explicitly convert dates into dates.

You have 2 options in SQL*Plus, you can insert an ANSI date literal

insert into patient (dob)
values ( date '&dob')

where &dob is in the form YYYY-MM-DD.

You can also explicitly convert the column into a date using the to_date function.

insert into patient (dob)
values ( to_date('&dob','yyyy-mm-dd'))

where &dob is of the form yyyy-mm-dd. This is known as a datetime format-model of which there's a long list you can play around with.

All of this assumes that the column dob is a date. If it's not please change it to one. It's never worth the hassle of storing dates as a string. I would highly recommend always explicitly storing century as well. How is Oracle to know whether you're talking about 2067 or 1967?

Guffa's answer takes care of the multiple row element of your question.

Ben · Accepted Answer · 2012-08-26 12:46:49Z

0

You end the query with the semi-colon, after that you can't continue to add to the query.

You can't add more value statements after the insert, but you can use insert all:

insert all
into patient values('&pno','&pname','&title',&dob,&children,'&gp')
into patient values('&pno','&pname','&title',&dob,&children,'&gp')
into patient values('&pno','&pname','&title',&dob,&children,'&gp')
select * from dual;

(Possibly you would need different parameter names for each row.)

You can also insert from a select:

insert into patient
select '&pno','&pname','&title',&dob,&children,'&gp' from dual
union all
select '&pno','&pname','&title',&dob,&children,'&gp' from dual
union all
select '&pno','&pname','&title',&dob,&children,'&gp' from dual;

Ref: http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_9014.htm#SQLRF01604

edited Aug 26, 2012 at 12:46

Ben

53.1k36 gold badges133 silver badges156 bronze badges

answered Aug 26, 2012 at 11:54

Guffa

703k112 gold badges760 silver badges1k bronze badges

1 Comment

Ben Over a year ago

In Oracle you need to select from something.

nhahtdh · Accepted Answer · 2012-08-26 14:01:56Z

0

ORA-00984 Error

This error most commonly occurs when You try to include a column name in the VALUES clause of an INSERT statement.

Then

You can check the values that you inserted AND columns names of your tables and make sure that there are not the same.

Reference

http://www.techonthenet.com/oracle/errors/ora00984.php

edited Aug 26, 2012 at 14:01

nhahtdh

56.9k15 gold badges131 silver badges164 bronze badges

answered Aug 26, 2012 at 11:46

BolBol

346 bronze badges

Collectives™ on Stack Overflow

How to add multiple rows using a single insert statement. Using the way in the description

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related