1

I'm currently using PL/SQL Developer to run some queries at my job, and my colleague uses TOAD to run the EXECs for procedures, since TOAD does not autocommit them. So, whenever I need to run a procedure, I must send him the codes and wait for him response.

Is there a way to stop PL/SQL Developer from autocommiting them?

I've already looked in the preferences and couldn't find any option to stop this from happening. The option "autocommit after Execute" is disabled, as well as all other autocommit functions I could find.

I'm running the procedures using

    Begin
       PROCEDURE
    End

since neither EXEC or EXECUTE seem to work

Here's the code I'm supposed to run (and works perfectly with TOAD)

    exec PWSYS.MAINT.MOVE_INCIDENT('here goes some data','here goes more data');

It doesn't run with EXEC on PL/SQL Developer (it displays error ORA-00900: invalid SQL statement), so I tried this

    Begin
       PWSYS.MAINT.MOVE_INCIDENT('data here','data here as well');
    End

It runs perfectly, but it also autocommits, and THAT'S my problem.

So, in summary, I need a way to stop it from autocommiting, or a way to rollback the data I've inserted/changed.

Thanks in advance

Dannyl
  • 115
  • 2
  • 3
  • 8
  • have you tried looking at the tables from another session? Is that how you have determined that it auto-commits? Or is there a commit inside the procedure you're running? – Boneist May 07 '19 at 14:38
  • [Do you mean this?](https://stackoverflow.com/a/19165463/266304) (Incidentally, `exec[ute]` is a SQL\*Plus/SQLcl/SQL Developer shorthand for an anonymous block, and it seems Toad has copied that; so there's no real difference between what you and your colleague are running.) – Alex Poole May 07 '19 at 14:57
  • @Boneist I know it autocommited because after I ran the procedure I checked our web-based system, and it changed there. About the commit within the procedure, I don't know for sure, but when my colleague runs the EXEC on TOAD it allows him to roll it back, so I don't think it has. – Dannyl May 07 '19 at 15:01
  • @AlexPoole Yes! that's not ticked on mine, since I run normal INSERTS and I still have to commit it manually. Also, thanks for the explanation about the EXEC – Dannyl May 07 '19 at 15:03
  • @Dannyl have you asked your colleague if he does anything to the procedure before he runs it? – Boneist May 07 '19 at 15:06
  • @Boneist Just asked him, and he says he doesn't. This is really bugging me – Dannyl May 07 '19 at 15:48
  • What happens if you run the procedure in SQL*Plus? – Boneist May 08 '19 at 07:58

0 Answers0