FAQ
Hello

Need some help on this. I have 2 table

Emp

empid (running on oracle sequenceno)
empname
deptid

Dept

deptid
empid
deptname

I need to insert into emp table first and then get
seqid just created and insert into dept table. Would
it be possible to do tat ?



Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs

Search Discussions

  • Lex de Haan at Mar 18, 2005 at 5:30 am
    check out the Oracle SQL reference (and/or read my book :-)
    once you have selected NEXTVAL from an Oracle sequence,
    you can use CURRVAL in that same session to refer to the same sequence value ...
    kind regards,

    Lex.


    Visit my website at http://www.naturaljoin.nl

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 11:17
    To: oracle-l_at_freelists.org
    Subject: Insert into 2 tables at one time

    Hello

    Need some help on this. I have 2 table

    Emp

    empid (running on oracle sequenceno)
    empname
    deptid

    Dept

    deptid
    empid
    deptname

    I need to insert into emp table first and then get seqid just created and insert
    into dept table. Would it be possible to do tat ?



    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Stephen booth at Mar 18, 2005 at 5:33 am

    On Fri, 18 Mar 2005 02:16:51 -0800 (PST), Kean Jacinta wrote:
    Hello

    Need some help on this. I have 2 table
    I need to insert into emp table first and then get
    seqid just created and insert into dept table. Would
    it be possible to do tat ?
    Yes. When you insert into emp you query the view for the next value
    to get the new value and then for the insert into dept you use the
    current value.

    Stephen

    --
    It's better to ask a silly question than to make a silly assumption.
    --
    http://www.freelists.org/webpage/oracle-l
  • Martic Zoran at Mar 18, 2005 at 6:07 am
    Just simple example:

    insert into emp values (seq1.nextval, ....) returning
    empid into :somebind;
    insert into dept values (..,:somebind, ...);

    Regards,
    Zoran

    Kean Jacinta wrote:
    Hello

    Need some help on this. I have 2 table


    Emp
    ----------
    empid (running on oracle sequenceno)
    empname
    deptid

    Dept
    ----------
    deptid
    empid
    deptname

    I need to insert into emp table first and then get
    seqid just created and insert into dept table. Would
    it be possible to do tat ?








    __________________________________
    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l
    Do you Yahoo!?
  • Ken Naim at Mar 18, 2005 at 9:05 am
    You can do it in one statement with something called a multi table insert.

    INSERT ALL

    INTO emp (empid, empname,deptno)

    VALUES (seq_emp_id.nextval, :empname,:deptno)
    INTO dept (deptid,empid,deptname)

    VALUES(:deptid, seq_emp_id.currval, :deptname);

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 5:17 AM
    To: oracle-l_at_freelists.org
    Subject: Insert into 2 tables at one time

    Hello

    Need some help on this. I have 2 table

    Emp

    empid (running on oracle sequenceno)
    empname
    deptid

    Dept

    deptid
    empid
    deptname

    I need to insert into emp table first and then get seqid just created and
    insert into dept table. Would it be possible to do tat ?



    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Ken Naim at Mar 18, 2005 at 9:26 am
    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
    (deptid,empid,deptname)

    VALUES(deptid, seq_emp_id.currval, deptname)
    Select :deptid deptid, :deptname deptname, :deptno deptno, :empname from
    dual.

    I wouldn't use this unless I needed a query anyway to extract some of the
    data.

    -----Original Message-----
    From: Gints Plivna
    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.
    You cannot perform a multitable insert into a remote table.
    You cannot specify a table collection expression when performing a
    multitable insert.
    In a multitable insert, all of the insert_into_clauses cannot combine to
    specify more than 999 target columns.
    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.
    Plan stability is not supported for multitable insert statements.
    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
    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

    You can do it in one statement with something called a multi table insert.
    INSERT ALL
    INTO emp (empid, empname,deptno)
    VALUES (seq_emp_id.nextval, :empname,:deptno) INTO dept
    (deptid,empid,deptname)
    VALUES(:deptid, seq_emp_id.currval, :deptname);


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 5:17 AM
    To: oracle-l_at_freelists.org
    Subject: Insert into 2 tables at one time

    Hello

    Need some help on this. I have 2 table


    Emp
    ----------
    empid (running on oracle sequenceno)
    empname
    deptid

    Dept
    ----------
    deptid
    empid
    deptname

    I need to insert into emp table first and then get seqid just created and
    insert into dept table. Would it be possible to do tat ?








    __________________________________
    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Kean Jacinta at Mar 18, 2005 at 11:17 am
    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 ?


    Ken Naim 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
    (deptid,empid,deptname)
    VALUES(deptid, seq_emp_id.currval, deptname)
    Select :deptid deptid, :deptname deptname, :deptno deptno, :empname from
    dual.

    I wouldn't use this unless I needed a query anyway to extract some of the
    data.

    -----Original Message-----
    From: Gints Plivna
    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.
    You cannot perform a multitable insert into a remote table.
    You cannot specify a table collection expression when performing a
    multitable insert.
    In a multitable insert, all of the insert_into_clauses cannot combine to
    specify more than 999 target columns.
    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.
    Plan stability is not supported for multitable insert statements.
    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
    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

    You can do it in one statement with something called a multi table insert.
    INSERT ALL
    INTO emp (empid, empname,deptno)
    VALUES (seq_emp_id.nextval, :empname,:deptno) INTO dept
    (deptid,empid,deptname)
    VALUES(:deptid, seq_emp_id.currval, :deptname);


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 5:17 AM
    To: oracle-l_at_freelists.org
    Subject: Insert into 2 tables at one time

    Hello

    Need some help on this. I have 2 table


    Emp
    ----------
    empid (running on oracle sequenceno)
    empname
    deptid

    Dept
    ----------
    deptid
    empid
    deptname

    I need to insert into emp table first and then get seqid just created and
    insert into dept table. Would it be possible to do tat ?








    __________________________________
    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Ken Naim at Mar 18, 2005 at 11:41 am
    Currval is session specific so it will know which is the correct one. I have
    tested this previously and have it in production code for over a year and
    haven't had any issues. My best advice is test it thoroughly.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 11: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 ?


    Ken Naim 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
    (deptid,empid,deptname)
    VALUES(deptid, seq_emp_id.currval, deptname) Select :deptid deptid,
    :deptname deptname, :deptno deptno, :empname from dual.

    I wouldn't use this unless I needed a query anyway to extract some of the
    data.

    -----Original Message-----
    From: Gints Plivna
    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.
    You cannot perform a multitable insert into a remote table.
    You cannot specify a table collection expression when performing a
    multitable insert.
    In a multitable insert, all of the insert_into_clauses cannot combine to
    specify more than 999 target columns.
    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.
    Plan stability is not supported for multitable insert statements.
    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
    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

    You can do it in one statement with something called a multi table insert.
    INSERT ALL
    INTO emp (empid, empname,deptno)
    VALUES (seq_emp_id.nextval, :empname,:deptno) INTO dept
    (deptid,empid,deptname)
    VALUES(:deptid, seq_emp_id.currval, :deptname);


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 5:17 AM
    To: oracle-l_at_freelists.org
    Subject: Insert into 2 tables at one time

    Hello

    Need some help on this. I have 2 table


    Emp
    ----------
    empid (running on oracle sequenceno)
    empname
    deptid

    Dept
    ----------
    deptid
    empid
    deptname

    I need to insert into emp table first and then get seqid just created and
    insert into dept table. Would it be possible to do tat ?








    __________________________________
    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Reidy, Ron at Mar 18, 2005 at 11:22 am
    Currval is session dependent.

    Ron Reidy
    Lead DBA
    Array BioPharma, Inc.

    -----Original Message-----
    From: 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 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 =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
    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
    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);
    =20
    =20
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 5:17 AM
    To: oracle-l_at_freelists.org
    Subject: Insert into 2 tables at one time
    =20
    Hello
    =20
    Need some help on this. I have 2 table
    =20
    =20
    Emp
    ----------
    empid (running on oracle sequenceno)
    empname
    deptid
    =20
    Dept
    ----------
    deptid
    empid
    deptname
    =20
    I need to insert into emp table first and then get seqid just created and
    insert into dept table. Would it be possible to do tat ?
    =20
    =20
    =20
    =20
    =20
    =20
    =20
    =20
    __________________________________
    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l
    =20
    --
    http://www.freelists.org/webpage/oracle-l
    --
    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-l
  • Kean Jacinta at Mar 18, 2005 at 11:24 am
    Oh ........ i see . :) THank You so much .
    JKean
    "Reidy, Ron" wrote:
    Currval is session dependent.

    Ron Reidy
    Lead DBA
    Array BioPharma, Inc.

    -----Original Message-----
    From: 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 ?

    Ken Naim 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
    (deptid,empid,deptname)
    VALUES(deptid, seq_emp_id.currval, deptname)
    Select :deptid deptid, :deptname deptname, :deptno deptno, :empname from
    dual.

    I wouldn't use this unless I needed a query anyway to extract some of the
    data.

    -----Original Message-----
    From: Gints Plivna
    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.
    You cannot perform a multitable insert into a remote table.
    You cannot specify a table collection expression when performing a
    multitable insert.
    In a multitable insert, all of the insert_into_clauses cannot combine to
    specify more than 999 target columns.
    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.
    Plan stability is not supported for multitable insert statements.
    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
    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

    You can do it in one statement with something called a multi table insert.
    INSERT ALL
    INTO emp (empid, empname,deptno)
    VALUES (seq_emp_id.nextval, :empname,:deptno) INTO dept
    (deptid,empid,deptname)
    VALUES(:deptid, seq_emp_id.currval, :deptname);


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kean Jacinta
    Sent: Friday, March 18, 2005 5:17 AM
    To: oracle-l_at_freelists.org
    Subject: Insert into 2 tables at one time

    Hello

    Need some help on this. I have 2 table


    Emp
    ----------
    empid (running on oracle sequenceno)
    empname
    deptid

    Dept
    ----------
    deptid
    empid
    deptname

    I need to insert into emp table first and then get seqid just created and
    insert into dept table. Would it be possible to do tat ?








    __________________________________
    Do you Yahoo!?
    Make Yahoo! your home page
    http://www.yahoo.com/r/hs
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    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
    to be for the use of the individual or entity named above. If you are not the
    intended recipient, please be aware that any disclosure, copying, distribution
    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.

    ---------------------------------
    Do you Yahoo!?
    Yahoo! Small Business - Try our new resources site!

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 18, '05 at 5:20a
activeMar 18, '05 at 11:41a
posts10
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase