Link to home
Start Free TrialLog in
Avatar of AidenA
AidenA

asked on

Oracle error: ORA-01086: savepoint START_TRANSACTION never established

Hi, I'm getting the above error when I run a transaction. I've traced the error to the below queries. So, it seems that if I raise an error before the following queries run, then the rollback succeeds without any problem. However, if I raise an error after the first 'ALTER TRIGGER...' statement then I get the above error (ORA-01086) and the rollback cannot complete. Obviously the alter trigger (or execute immediate?) statement is preventing Oracle from being able to do a rollback.

So, my question is, what's going on here exactly, and how can I fix it?

Thanks, Aiden


EXECUTE IMMEDIATE 'ALTER TRIGGER ctc_table_trig DISABLE';

DELETE FROM CTC_Table;

EXECUTE IMMEDIATE 'ALTER TRIGGER ctc_table_trig ENABLE';

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

rather than disabling the triggers, put a condition in them so they don't actually do anything.

that way you don't need to execute ddl at all


something like

BEGIN
IF some_package.variable = true then
   --- your trigger code here
END IF;
END;

then, in your script, set the variable to FALSE before the delete, then reset it to true when your transaction is complete.
and lastly,  if you're trying to circumvent your triggers, maybe there shouldn't be triggers on the table to begin with.

or, if they really should be there, maybe trying to get around them isn't a good idea
Avatar of AidenA

ASKER

ok thanks. I just disable the tigger before running my transaction then and enable it afterwards.

I could change it as you suggest so that I don't have to do that, and probably should, but I don't have time to start messing around with the queries so I guess I'll just do it this way out of convenience!  :-/

thanks!