Home » SQL & PL/SQL » SQL & PL/SQL » Rollback in DBMS_PARALLEL_EXECUTE (ORACLE 11G)
Rollback in DBMS_PARALLEL_EXECUTE [message #631365] Tue, 13 January 2015 02:32 Go to next message
aravind.peddola@gmail.com
Messages: 2
Registered: January 2015
Location: Hyderbad
Junior Member

Hi,
I am Using DBMS_PARALLEL_EXECUTE for Update table, as Bellow,

DECLARE

  l_task     VARCHAR2(30) := 'DTC_SERVICE_FLG71';
  l_sql_stmt VARCHAR2(3500);
  l_try      NUMBER;
  l_status   NUMBER;
  l_attempts    PLS_INTEGER := 1;
  retries_in  PLS_INTEGER := 2;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => l_task,
                                               table_owner => 'SCOTT',
                                               table_name  => 'CUSTOMER_02',
                                               by_row      => FALSE,
                                               chunk_size  => 1000);

 l_sql_stmt :='UPDATE CUSTOMER_02 i
      SET   i.UPDATE_DATE = systimestamp
            ,i.UPDATE_USER = ''-1''
            , i.flag = CASE
                WHEN EXISTS (
                       SELECT 1
                         FROM DEALER d
                        WHERE find_distance (i.LATITUDE,
                                                                  i.LONGITUDE,
                                                                  d.LATITUDE,
                                                                  d.LONGITUDE
                                                                 ) < 0.25)
                   THEN ''Y''
                 ELSE ''N''
             END
             WHERE i.flag IS NULL AND rowid BETWEEN :start_id AND :end_id' ;

  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 10);

 
  DBMS_PARALLEL_EXECUTE.drop_task(l_task);

   EXCEPTION WHEN OTHER THEN
  ROLLBACK;
END ;

Please give me solution when i want to rollback if any one the transaction is failed among the all transaction it not roll-backing



[Edit MC: add code tags]

[Updated on: Tue, 13 January 2015 02:48] by Moderator

Report message to a moderator

Re: Rollback in DBMS_PARALLEL_EXECUTE [message #631368 is a reply to message #631365] Tue, 13 January 2015 02:47 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

Quote:
Please give me solution when i want to rollback if any one the transaction is failed among the all transaction it not roll-backing


It is not possible, this is the principle of transactions: they are independent.

Quote:
EXCEPTION WHEN OTHER THEN ROLLBACK;


WHEN OTHERS, if ever needed, should always be ended b "RAISE;", read WHEN OTHERS.

Previous Topic: convert comma separated string to IN clause
Next Topic: Table Access Full
Goto Forum:
  


Current Time: Fri Mar 29 10:17:22 CDT 2024