Question on "Execute Script" (F5)

Toad for Oracle
1

I am running Toad for Orace 9.1.0.62. A colleague wrote a procedure (proc_init_ba) that was returning different results for different individuals who ran the procedure. After spending quite some time thinking the issue was related to DB security and user accounts, we determined that:

If the command “execute proc_init_ba” was highlighted in an editor window and then run via the F5 key (execute script), it did not work. According to the DBA, a second session was being spawned and any queries were directed at the wrong session.

If the command “execute proc_init_ba” was highlighted in an editor window and then run via the F9 key (execute statement), it worked.

If the procedure proc_init_ba was highlighted in the procedure tab in the schema browswer window and run via the lightening bolt (execute procedure), it worked.

If the command “execute proc_init_ba” was highlighted in an editor window and the “execute statement” icon in the upper left corner of the gui was clicked, it worked.

If the procedure was run via F9 (run statement) or F5 (run script) in SQL Developer, it worked.

Any thoughts on why the procedure behaves differently in Toad when run as a script??

Thanks in advance for any help.

2

There’s an option to run scripts in the existing Toad session. I can’t remember
if that is checked or unchecked by default in that version. Try running the
script in the current Toad session to see if you get the desired results.

In addition, the script engine has had many upgrades since that version so it’s
possible that it’s a bug that’s already been addressed in a more recent version.

Greg

On Oct 7, 2011, at 12:58 PM, Karen Short wrote:

Message from: Short

I am running Toad for Orace 9.1.0.62. A colleague wrote a procedure
(proc_init_ba) that was returning different results for different
individuals who ran the procedure. After spending quite some time thinking
the issue was related to DB security and user accounts, we determined that:

If the command "execute proc_init_ba" was highlighted in an editor window
and then run via the F5 key (execute script), it did not work. According to
the DBA, a second session was being spawned and any queries were directed at
the wrong session.

If the command "execute proc_init_ba" was highlighted in an editor window
and then run via the F9 key (execute statement), it worked.

If the procedure proc_init_ba was highlighted in the procedure tab in the
schema browswer window and run via the lightening bolt (execute procedure),
it worked.

If the command "execute proc_init_ba" was highlighted in an editor window
and the "execute statement" icon in the upper left corner of the gui was
clicked, it worked.

If the procedure was run via F9 (run statement) or F5 (run script) in SQL
Developer, it worked.

Any thoughts on why the procedure behaves differently in Toad when run as a
script??

Thanks in advance for any help.

Historical Messages

Author: Karen Short
Date: Fri Oct 07 09:58:55 PDT 2011

I am running Toad for Orace 9.1.0.62. A colleague wrote a procedure
(proc_init_ba) that was returning different results for different
individuals who ran the procedure. After spending quite some time thinking
the issue was related to DB security and user accounts, we determined that:

If the command "execute proc_init_ba" was highlighted in an editor window
and then run via the F5 key (execute script), it did not work. According to
the DBA, a second session was being spawned and any queries were directed at
the wrong session.

If the command "execute proc_init_ba" was highlighted in an editor window
and then run via the F9 key (execute statement), it worked.

If the procedure proc_init_ba was highlighted in the procedure tab in the
schema browswer window and run via the lightening bolt (execute procedure),
it worked.

If the command "execute proc_init_ba" was highlighted in an editor window
and the "execute statement" icon in the upper left corner of the gui was
clicked, it worked.

If the procedure was run via F9 (run statement) or F5 (run script) in SQL
Developer, it worked.

Any thoughts on why the procedure behaves differently in Toad when run as a
script??

Thanks in advance for any help.

__
_______________________________________

3

Plus the editor has the drop-down near top to set the session for the connection
to appear as connected to which eliminates needing to prefix objects from
another schema with the schema name – the script manager does not offer
nor honor this setting – so results can and will be different if this is
the case

4

I’m waiting for confirmation from my power user but I did find the Toad Option (Oracle Transactions) to “execute scripts in Toad session.” I reran the script using F5 and it works!!! However, until I get confirmation from my customer, I can’t allow myself to jump for joy.

I will send a thank you for the responses and I’ll follow up after I hear back from The Man. I certainly appreciate the help. Very much.

5

Woo Hoo – Satisfied customer! He agreed that the change to the options worked. This was driving me insane. I will admit that I did look at the options but obviously not close enough. Sincere thank you!