Rollback in DBMS_PARALLEL_EXECUTE [message #631365] |
Tue, 13 January 2015 02:32 |
|
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
|
|
|
|