oracle11g - Delete and insert is not working in a block in oracle -
i want in single transaction in want delete , insert if no row delete should raise , error else insert(multiple insert , delete statement). statement deleting data not inserting it. have tried:
i have removed raise error deleting not inserting records,
if write delete statement in down inserting not deleting.
if no rows deleted have raise error block.,else should run given statement. sample contains,500(around) insert or delete script in transaction.
transaction should thrown error if transaction failed.
begin dbms_output.put_line (1); begin dbms_output.put_line (1); delete cmc_bsdl_details pdbc_pfx = 'cw6f'; commit; if sql%rowcount = 0 raise_application_error (-20101, 'no record deleted'); end if; delete cmc_bstx_sum_text pdbc_pfx = 'cw6f'; commit; if sql%rowcount = 0 raise_application_error (-20101, 'no record deleted'); end if; insert cmc_bsdl_details (pdbc_pfx, bsdl_type, bsdl_ntwk_ind, bsdl_eff_dt, bsdl_copay_amt, bsdl_dede_amt, bsdl_coin_pct, bsdl_ltlt_amt, bsdl_term_dt, bsdl_lt_type, bsdl_lt_period, bsdl_lt_counter, bsdl_tier, bsdl_cov_ind, bsdl_stoploss_amt, bsdl_stoploss_type, bsdl_beg_mmdd, bsdl_user_label1, bsdl_user_data1, bsdl_user_label2, bsdl_user_data2, bsdl_user_label3, bsdl_user_data3, bsdl_user_label4, bsdl_user_data4, bsdl_user_label5, bsdl_user_data5, bsdl_user_label6, bsdl_user_data6, bsdl_lock_token, atxr_source_id, sys_last_upd_dtm, sys_usus_id, sys_dbuser_id) values ( 'cw6f', 'aamb', 'a', to_timestamp ('01/01/1990 00:00:00', 'mm/dd/yyyy hh24:mi:ss.ff'), 250.00, 0.00, 0.00, 0.00, to_timestamp ('12/31/9999 00:00:00', 'mm/dd/yyyy hh24:mi:ss.ff'), ' ', ' ', ' ', 0, ' ', 0.00, ' ', 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 1, to_timestamp ('01/01/1753 00:00:00', 'mm/dd/yyyy hh24:mi:ss.ff'), to_timestamp ('08/17/2017 16:15:10', 'mm/dd/yyyy hh24:mi:ss.ff'), 'translator', 'translator'); commit; end; exception when others rollback; end;
perform check before commit statement, after delete.
move commit after if ... end if statement:
delete cmc_bsdl_details pdbc_pfx = 'cw6f'; if sql%rowcount = 0 raise_application_error (-20101, 'no record deleted'); end if; commit; delete cmc_bstx_sum_text pdbc_pfx = 'cw6f'; if sql%rowcount = 0 raise_application_error (-20101, 'no record deleted'); end if; commit; btw - if want these statements within single transaction, don't commit between these statements @ all. run 1 commit @ end. each commit statement ends 1 transaction, there 3 commit statements , 3 transactions in procedure, not one.
Comments
Post a Comment