duplicate inserts - why?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: duplicate inserts - why?

  1. #1
    Join Date
    Sep 2004
    Posts
    24

    duplicate inserts - why?

    Hi 2 all of you,

    can you tell me, why my procedure inserts same recordset sometimes twice, and sometimes four times.

    here is my procedure (it's the dynamic procedure from the former thread)

    Code:
    is
     
      begin
        for i in (select ext_date, ext_duration, ext_a_number, ext_b_number from ext_table)
           loop
                 execute immediate 'insert into cdr_'|| i.ext_date || '(cdr_date, cdr_duration, cdr_a_number, cdr_b_number) values ('||i.ext_date||','||i.ext_duration||','''||i.ext_a_number||''', '''||i.ext_b_number||''')';
                 commit;
             --dmbs_output.put_line    
              
          end loop;
       end;

    thanks for your help...

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi petzi,

    (Sorry, no idea why you get duplicates - other than because there are duplicates in the external table?)

    Wanted to warn you not to get into the habit of constructing dynamic SQL as you have here. By concatenating, you are generating a new SQL statement for each row, which has to be hard parsed - heavy overhead, especially if you're processing 8 million rows a day. The table name has to be set up like that, but the data values should go in as bind variables: http://download-west.oracle.com/docs...ynam.htm#15023

  3. #3
    Join Date
    Sep 2004
    Posts
    24
    Hi DaPi,

    I just tested the procedure, using no bind variable - later on I'm using bind variables, because of performance and ....

    thanks.

  4. #4
    Join Date
    Sep 2004
    Posts
    24

    dynamic to_date

    hi again,

    I want to convert the number into the date format in my dynamic statement - but it doesn't work.

    in the internal table, the "ext_date" column is really a date!

    ....'||to_date(i_ext_date,'YYYYMMDD')||'

    --> ORA-01861, ORA-00984 etc. literal doesn't match string format or missing comma....

    Thanks.

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    I think the issue is the single-quote for date format.

    Code:
    to_date(i_ext_date,'YYYYMMDD')||'
    Change it to:

    Code:
    to_date(i_ext_date,''YYYYMMDD'')||'
    Cheers!
    OraKid.

  6. #6
    Join Date
    Sep 2004
    Posts
    24
    Code:
    for i in (select ext_date, ext_time, ext_duration, ext_a_number, ext_unverified_cli, ext_b_number, ext_c_number, ext_cdr_switch_id, ext_sequence_number, ext_connection_id, ext_record_owner_id, ext_stream_id, ext_status, ext_format from ext_table)
           loop
               
    execute immediate 'insert first when' ||i.ext_date|| 'not between 19700101 and 20501231 or' ||i.ext_time|| 'not between 000000 and 235959 or' ||i.ext_duration|| '<= 0 or' ||i.ext_a_number|| '< (select cfg_value from config where cfg_attribut like ''CallMinNumberDigit'') or' ||i.ext_b_number|| '< (select cfg_value from config where cfg_attribut like ''CallMinNumberDigit'') then'
                 ||' into cdr_error'
                 ||' (cdx_date, cdx_time, cdx_duration, cdx_a_number, cdx_unverified_cli, cdx_b_number, cdx_c_number, cdx_switch_id, cdx_sequence_number, cdx_connection_id, cdx_record_owner_id,cdx_stream_id, cdx_format_cdx_status, cdx_file_id) values ('||i.ext_date||','||i.ext_time||','||i.ext_duration||','''||i.ext_a_number||''','''||i.ext_unverified_cli||''','''||i.ext_b_number||''', '''||i.ext_c_number||''','''||i.ext_cdr_switch_id||''','''||i.ext_sequence_number||''','''||i.ext_connection_id||''','''||i.ext_record_owner_id||''','||i.ext_stream_id||','||i.ext_status||','||i.ext_format||',(select max(fil_id) from cdrfile))'
                 ||' else'
                 ||' into cdr_'|| i.ext_date ||'(cdr_date, cdr_time, cdr_duration, cdr_a_number, cdr_unverified_cli, cdr_b_number, cdr_c_number, cdr_switch_id, cdr_sequence_number, cdr_connection_id, cdr_record_owner_id, cdr_stream_id, cdr_status, cdr_format,cdr_fil_id) values ('||i.ext_date||','||i.ext_time||','||i.ext_duration||','''||i.ext_a_number||''','''||i.ext_unverified_cli||''','''||i.ext_b_number||''', '''||i.ext_c_number||''','''||i.ext_cdr_switch_id||''','''||i.ext_sequence_number||''','''||i.ext_connection_id||''','''||i.ext_record_owner_id||''','||i.ext_stream_id||','||i.ext_status||','||i.ext_format||',(select max(fil_id) from cdrfile))';
                 commit;
     end loop;
    !!!sorry for not using bind variables but I'm just testing if it works...

    but i got the ORA-00908 MISSING KEYWORD.
    normally using multi table inserts i have add the subquery definition at the end.

    like select * from tablename, but i use the query for the loop.

    Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width