This discussion is archived
4 Replies Latest reply: Dec 13, 2012 10:22 PM by user575115 RSS

Insert All

user575115 Newbie
Currently Being Moderated
Hi,
I'm inserting multiple tables using below sql.In this sql i need to insert one table(cw_elements_meta_reqs) two time with differ of few columns like below.But here unique constraint throwing error due sequence will reruns same vale .Is there any chance(logic) to resolve this.I have tried one time sequnce in side values and one time in select stement but not works.Pls help
INSERT ALL
INTO ibis.cw_portfolio_template
  (
    portfolio_template_id,
    YEAR,
    MONTH,
    group_number,
    sub_group,
    subject,
    subject_option,
    lvl,
    component,
    self_taught,
    combined
    --exam_timezone
  )
  VALUES
  (
    ibis.cw_portfolio_template_seq.nextval,
    YEAR,
    MONTH,
    group_number,
    subgroup_number,
    subject,
    subject_option,
    lvl,
    component,
    'N',
    combined
    --exam_timezone
  )
INTO ibis.cw_portfolio_template_element
  (
    portfolio_template_element_id,
    portfolio_template_id,
    max_file_size,
    media_type_id
  )
  VALUES
  (
    ibis.cw_pf_template_element_seq.nextval,
    ibis.cw_portfolio_template_seq.nextval ,
    max_bytes,
    media_type_id
  )
INTO ibis.cw_elements_meta_reqs
  (
    elements_meta_reqs_id,
    portfolio_template_element_id,
    element_metadata_id,
    sort_order
  )
  VALUES
  (
    ibis.cw_elements_meta_reqs_seq.nextval,--seq,
    ibis.cw_pf_template_element_seq.nextval,
    5,1
  )
INTO ibis.cw_elements_meta_reqs
  (
    elements_meta_reqs_id,
    portfolio_template_element_id,
    element_metadata_id,
    sort_order
  )
  VALUES
  (
    ibis.cw_elements_meta_reqs_seq.nextval,
    ibis.cw_pf_template_element_seq.nextval,
    6,2
  )
INTO ibis.cw_pf_template_element_media
  (
    PORTFOLIO_TEMPLATE_ELEMENT_ID,
    Media_type_id
  )
  VALUES
  (
    ibis.cw_pf_template_element_seq.nextval,
    media_type_id
  )
SELECT DISTINCT YEAR,
  MONTH ,
  (SELECT sg.group_number
  FROM subject_group sg
  WHERE sg.year = ceft.year
  AND sg.month  = ceft.month
  AND sg.lvl    = ceft.lvl
  AND sg.subject= ceft.subject
  ) group_number,
  (SELECT sg.subgroup_number
  FROM subject_group sg
  WHERE sg.year = ceft.year
  AND sg.month  = ceft.month
  AND sg.lvl    = ceft.lvl
  AND sg.subject= ceft.subject
  ) subgroup_number,
  subject ,
  subject_option ,
  lvl ,
  component ,
  (SELECT ep.combined combined
  FROM ibis.subject_component sc,
    ibis.examination_paper ep
  WHERE sc.year        = ceft.year
  AND sc.month         = ceft.month
  AND sc.subject       = ceft.subject
  AND sc.subject_option= ceft.subject_option
  AND sc.lvl           = ceft.lvl
  AND sc.language      = ceft.language
  AND sc.component     =ceft.component
  AND ep.year          = sc.year
  AND ep.month         =sc.month
  AND ep.paper_code    =sc.paper_code
  ) combined,
  -- ceft.exam_timezone exam_timezone,
  max_bytes ,
  (SELECT media_type_id
  FROM ibis.cw_file_type
  WHERE file_extension=UPPER(ceft.file_ext)
  ) media_type_id
FROM ibis.component_e_crsework_file_type ceft ;
  • 1. Re: Insert All
    jeneesh Guru
    Currently Being Moderated
    Like this?
    create table m(id number,name varchar2(10));
    
    create table d1(id number primary key,name varchar2(10));
    
    create sequence seq_test;
    
    insert into m values(1,'Test');
    insert into m values(2,'Test2');
    
    
    create or replace function f1 return number
    is
    begin
     return seq_test.nextval;
    end;
    
    insert all
      into d1 (id, name)
        values(seq1,name)
      into d1 (id,name)
        values(seq2,name)
    select f1 seq1,
           f1 seq2,name
    from m;
    
    4 rows inserted.
    
    select * from d1;
    
    ID NAME     
    -- ----------
     1 Test       
     3 Test2      
     2 Test       
     4 Test2 
  • 2. Re: Insert All
    Hoek Guru
    Currently Being Moderated
    Can you post the exact error message?

    Keep in mind:
    "The sequence.NEXTVAL expression is used in each VALUES clause but each increment is constant for the entire INTO list. Given the fact that Oracle doesn't guarantee the execution order of an INSERT ALL statement, this sequence behaviour actually makes sense (because we couldn't guarantee a NEXTVAL before a CURRVAL, for example)."

    http://www.oracle-developer.net/display.php?id=209
    (Scroll down to the "multi-table inserts and sequences"-part )

    And you might want to explore this thread as well:
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6915127515933#tom7871897954847
    Testing with nextval and currval might do it.
  • 3. Re: Insert All
    user575115 Newbie
    Currently Being Moderated
    thanks.
  • 4. Re: Insert All
    user575115 Newbie
    Currently Being Moderated
    .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points