duplicate inserts - why?

[petzi]

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)

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...

[DaPi]

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

[petzi]

Hi DaPi,

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

thanks.

[petzi]

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.

[balajiyes]

I think the issue is the single-quote for date format.

to_date(i_ext_date,'YYYYMMDD')||'

Change it to:

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

[petzi]

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.