Using ROLLBACK FUNCTION

Hello,

I'm trying to ROLLBACK records after a delete and it appears that it not working as expected. I'm am inserting in approx 20 tables then deleting the records from the source tables. I am then running an IF statement and if the criteria is not meant (or an error occurs) then rollback.

Do you have any ideas?

Thanks
lulubell-bAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Look through all the code.  I bet different functions/procedures/pieces of it are doing commits that you don't know about.
0
 
slightwv (䄆 Netminder) Commented:
Define: "not working as expected".

Rollback will 'undo' all non-commited transactions.  Are you doing intermittent commits?
0
 
sdstuberCommented:
are you simply waiting for the rollback to complete?

you can monitor the amount of rollback used per transaction and extrapolate how long it might take to complete


SELECT d.tablespace_name,r.NAME,s.username, s.SID,s.serial#,p.spid os_pid , t.addr,t.used_ublk
FROM v$transaction t, v$rollname r, v$session s,v$process p,dba_rollback_segs d
WHERE t.addr = s.taddr
AND p.addr = s.paddr
AND t.xidusn = r.usn
AND d.segment_name = r.NAME
ORDER BY d.tablespace_name,r.NAME,s.username;
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lulubell-bAuthor Commented:
I don't think so, I ran my procedure with the rollback imbedded and then looking at the table No data was rolled back. I'm confused, it works when I remove the delete statements, but doesnt seem to work when I add the delete statments back into the PL/SQL.
0
 
lulubell-bAuthor Commented:
What are intermittent commits? I'm unsure.
0
 
sdstuberConnect With a Mentor Commented:
these would be intermittent commits,  

delete yourtable where id = 1;
commit;
delete yourtable where id = 2;
commit;
delete yourtable where id = 3;
commit;
delete yourtable where id = 4;
commit;

rollback;  -- this won't do anything

the commits could be in a loop too, they don't have to spaced literally between sql statements like that


0
 
sdstuberCommented:
are you doing any DDL?  alter/create/drop/truncate, etc?  DDL  does an implicit commit
0
 
lulubell-bAuthor Commented:
Within my procedure I'm doing

INSERTs
DELETE
THEN CALLING ANOTHER PROCEDURE WHICH IS SELECTING AND RETURN A VALUE.

I'm confused.

0
 
ajexpertCommented:
Do you have COMMIT at any point in your procedure?

Possiblity is that your logic met all the CRITERIA written in IF condition so it didnt rollback
0
 
lulubell-bAuthor Commented:
I do not have any commits in the procedures and I'm positive that the value is returning correctly from the second procedure.
0
 
sdstuberCommented:
can you post the code? and sample data/results that demonstrate the problem ?

the symptoms you describe and the mini-pseudocode snippets don't coincide
0
 
sdstuberConnect With a Mentor Commented:
and remember, the word "commit" doesn't need to be in the code.

any DDL statement will commit for you.

and some built in packages will commit for you too
0
 
lulubell-bAuthor Commented:
Thank you, still unsure why the rollback isn't working correctly. I will revisit later. Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.