| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Insert into 2 tables at one time
Currval is session dependent.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kean Jacinta
Sent: Friday, March 18, 2005 9:14 AM
To: kennaim_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: Insert into 2 tables at one time
I am concern with the query selecting from seq_emp_id.currval. Let's =
say there are 2 user insert into the same emp table. So 2 records are =
created ID 221 and ID 222. How shall i know the currval id is accurate. =
Which shall i pick ?=20
=20
Ken Naim <kennaim_at_gmail.com> wrote:
You are correct, I forgot that the sub query was required but the =
sequence
could still work.
INSERT ALL
INTO emp (empid, empname,deptno)
VALUES (seq_emp_id.nextval, empname,deptno) INTO dept=20
(deptid,empid,deptname)
VALUES(deptid, seq_emp_id.currval, deptname)
Select :deptid deptid, :deptname deptname, :deptno deptno, :empname from
dual.=20
I wouldn't use this unless I needed a query anyway to extract some of =
the
data.
-----Original Message-----
From: Gints Plivna [mailto:Gints.Plivna_at_softex.lv]=20
Sent: Friday, March 18, 2005 9:16 AM
To: kennaim_at_gmail.com; jacintakean_at_yahoo.com; oracle-l_at_freelists.org
Subject: RE: Insert into 2 tables at one time
First: Mutitable insert must use subquery.
Second initially thought of the same idea, but as from Oracle docs (look =
at
the last statement):
Restrictions on Multitable Inserts
You can perform multitable inserts only on tables, not on views or
materialized views.=20
You cannot perform a multitable insert into a remote table.=20
You cannot specify a table collection expression when performing a
multitable insert.=20
In a multitable insert, all of the insert_into_clauses cannot combine to
specify more than 999 target columns.=20
Multitable inserts are not parallelized in a Real Application Clusters
environment, or if any target table is index organized, or if any target
table has a bitmap index defined on it.=20
Plan stability is not supported for multitable insert statements.=20
The subquery of the multitable insert statement cannot use a sequence.
At least for 9i.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/stat
ements_913a.htm
Gints
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Ken Naim
> Sent: Friday, March 18, 2005 4:02 PM
> To: jacintakean_at_yahoo.com; oracle-l_at_freelists.org
> Subject: RE: Insert into 2 tables at one time
>=20
> You can do it in one statement with something called a multi table
insert.
>=20
>=20 >=20
>=20
>=20
>=20 >=20
>=20
>=20 >=20 >=20 >=20 >=20 >=20 >=20 >=20> Do you Yahoo!?
> __________________________________
>=20
-- http://www.freelists.org/webpage/oracle-l __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around=20 http://mail.yahoo.com=20 -- http://www.freelists.org/webpage/oracle-l This electronic message transmission is a PRIVATE communication which = contains information which may be confidential or privileged. The information is = intended=20 to be for the use of the individual or entity named above. If you are = not the=20 intended recipient, please be aware that any disclosure, copying, = distribution=20 or use of the contents of this information is prohibited. Please notify = the sender of the delivery error by replying to this message, or notify us = by telephone (877-633-2436, ext. 0), and then delete it from your system. -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 18 2005 - 11:22:40 CST
 |
 |