# AW: Toad for Oracle 12.1: PLSQL-Code with Cursor: Within Cursor: Shift-Enter: How to disable "Edit SQL" window ?

**URL:** <https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [January 15, 2014, 9:28am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368 "2014-01-15T09:28:37Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![m.reichelt](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@m.reichelt](https://forums.toadworld.com/u/m.reichelt)\
**Post date:** [January 15, 2014, 9:28am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/1 "2014-01-15T09:28:37Z")

</div>

Hello Mr. Staszewski,

here an sample Cursor PL/SQL code (see appended file).

In earlier TOAD versions we did add an empty line before and after the “cursor SQL” and did only

Strg-Enter within one “cursor SQL” line to direct test this SQL without needing to give in any parameter.

Thanks and Regards

Martin Reichelt

Oracle DBA

IS International Sourcing AG

Stockwiesenstr. 1

CH-8240 Thayngen

+41 52 6453 804

m.reichelt@is-int-sourcing.ch

**Von:** Michael Staszewski [[mailto:bounce-mstaszew@toadworld.com](mailto:bounce-mstaszew@toadworld.com)]

**Gesendet:** Mittwoch, 15. Januar 2014 18:10

**An:** [toadoracle@toadworld.com](mailto:toadoracle@toadworld.com)

**Betreff:** RE: [Toad for Oracle - Discussion Forum] Toad for Oracle 12.1: PLSQL-Code with Cursor: Within Cursor: Shift-Enter: How to disable “Edit SQL” window ?

**[ RE: Toad for Oracle 12.1: PLSQL-Code with Cursor: Within Cursor: Shift-Enter: How to disable “Edit  
SQL” window ?](http://www.toadworld.com/products/toad-for-oracle/f/10/p/20918/43609.aspx#43609)**

Reply by Michael Staszewski

Toad is trying to intelligently execute that SQL by converting references to PL/SQL identifiers into bind variables and stripping any INTO and RETURNING clause. It sounds like in your case it’s  
making binds where there should not be. For 12.5 it’s gotten better although there are still a couple of outstanding issues that I’m working on. If you’d like to email me your cursor SQL as well as the SQL that appears in the Edit SQL dialog after Shift+F9  
I’ll take a look.

[michael.staszewski@quest.com](mailto:michael.staszewski@quest.com)

To reply, please reply-all to this email.

[Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/20918.aspx?subscribe=false) on this subject.

Or [  
Unsubscribe from Toad for Oracle - General](http://www.toadworld.com/products/toad-for-oracle/f/10.aspx?subscribe=false) notifications altogether.

Toad for Oracle - Discussion Forum

[Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/20918/43609.aspx?AbuseContentId=5b2795c1-91fb-4fc9-b123-1b1bf13f8beb&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true)  
this post as spam/abuse.

![](http://gyk.r.mailjet.com/o/04j0/5e15e200/pllq0lme.gif)

---

<div class="post-metadata">

**Author:** ![rrothlisberger](https://avatars.discourse-cdn.com/v4/letter/r/7cd45c/32.png) [@rrothlisberger](https://forums.toadworld.com/u/rrothlisberger)\
**Post date:** [November 18, 2015, 10:41am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/2 "2015-11-18T10:41:54Z")

</div>

I see this is still an issue in 12.8, Any idea if it will get corrected?

---

<div class="post-metadata">

**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\
**Post date:** [December 2, 2015, 8:16am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/3 "2015-12-02T08:16:37Z")

</div>

I tried to replicate with an example having similar sub-selects and it’s executing without prompting for params. Please send a screenshot of what you’re seeing and include the sample SQL you’re using if it differs from what you’ve already attached. This is my test case using user\_objects.

DECLARE  
i\_count PLS\_INTEGER := 0;

CURSOR cur  
IS  
SELECT uo1.object\_name,  
(SELECT uo2.object\_type  
FROM sys.user\_objects uo2  
WHERE uo2.object\_id = uo1.object\_id)  
object\_type  
FROM sys.user\_objects uo1;  
BEGIN  
i\_count := 0;  
DBMS\_OUTPUT.put\_line (  
'ProdAuftraege aendern: ’ || TO\_CHAR (SYSDATE, ‘HH24:MI:SS’));

FOR rec IN cur  
LOOP  
–update pa\_auftraege\_s pa  
–set PA.SAISON\_ID = (select s.saison\_id from saisons s where s.saison = ‘214’)  
–where pa.prodauftrags\_id = rec.prodauftrags\_id;  
icount := icount + 1;  
END LOOP cur;

DBMS\_OUTPUT.put\_line ('ProdAuftraege geaendert: ’ || icount);  
DBMS\_OUTPUT.put\_line ('Fertig: ’ || TO\_CHAR (SYSDATE, ‘HH24:MI:SS’));  
END;

---

<div class="post-metadata">

**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\
**Post date:** [December 2, 2015, 8:17am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/4 "2015-12-02T08:17:45Z")

</div>

I just noticed that you are not the original poster. Please send me a sample that reproduces the issue. One that uses standard data dictionary views would be ideal.

Thanks,

Michael

---

<div class="post-metadata">

**Author:** ![rrothlisberger](https://avatars.discourse-cdn.com/v4/letter/r/7cd45c/32.png) [@rrothlisberger](https://forums.toadworld.com/u/rrothlisberger)\
**Post date:** [December 2, 2015, 11:13am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/5 "2015-12-02T11:13:41Z")

</div>

I get the Edit SQL with the data posted below the query when I click Shift-F9 while the cursor is in the query. If I comment out the declare the query runs as it should, So it appears the parser is looking at the declare and deciding to do something else.

Sorry the query is part of eBusiness Suite, I’ll see if I can re-create it using standard objects, my 1st try did not have the same results as the one below.

Thanks

– Duplicate addresses

declare

cursor record is

select q.\*

from (

select p.party\_number, ps.party\_id, ps.party\_site\_number, ps.party\_site\_id, ps.identifying\_address\_flag, ps.object\_version\_number, l.creation\_date, ps.attribute1, ps.attribute2,

l.address1, l.address2, l.city, l.state, l.country, l.postal\_code, ps.status,

row\_number() over (partition by ps.party\_id, l.address1, l.address2, l.city, l.state, l.country,

decode( l.country, ‘US’, substr(l.postal\_code,1,5), l.postal\_code) order by ps.party\_id, ps.identifying\_address\_flag desc ) rn

from hz\_party\_sites ps, hz\_locations l, hz\_parties p

where ps.party\_id \> 0

and l.location\_id = ps.location\_id

and p.party\_id = ps.party\_id

and p.party\_type = ‘ORGANIZATION’

and ps.party\_id = 15727

–33812

) q

where q.rn \> 1

order by q.party\_id, q.address1, q.address2, q.city, q.state, q.country, decode( q.country, ‘US’, substr(q.postal\_code,1,5), q.postal\_code), q.rn --, q.cssort

;

begin

null;

end;

– From Edit SQL

select q.\*

from (

select p.party\_number, ps.party\_id, ps.party\_site\_number, ps.party\_site\_id, ps.identifying\_address\_flag, ps.object\_version\_number, l.creation\_date, ps.attribute1, ps.attribute2,

l.address1, l.address2, l.city, l.state, l.country, l.postal\_code, ps.status,

row\_number() over (partition by ps.party\_id, l.address1, l.address2, l.city, l.state, l.country,

decode( l.country, ‘US’, substr(l.postal\_code,1,5), l.postal\_code) order by ps.party\_id, ps.identifying\_address\_flag desc ) rn

from hz\_party\_sites ps, hz\_locations l, hz\_parties p

where ps.party\_id \> 0

and l.location\_id = ps.location\_id

and p.party\_id = ps.party\_id

and p.party\_type = ‘ORGANIZATION’

and ps.party\_id = 15727

–33812

) q

where :var\_0 /_q.rn_/ \> 1

order by :var\_7 /_q.party\_id_/, :var\_6 /_q.address1_/, :var\_5 /_q.address2_/, :var\_4 /_q.city_/, :var\_3 /_q.state_/, :var\_2 /_q.country_/, decode( :var\_2 /_q.country_/, ‘US’, substr(:var\_1 /_q.postal\_code_/,1,5), :var\_1 /_q.postal\_code_/), :var\_0 /_q.rn_/

---

<div class="post-metadata">

**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\
**Post date:** [December 2, 2015, 11:29am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/6 "2015-12-02T11:29:27Z")

</div>

Got it. Thanks for the example. I can reproduce with this now. Since it’s not finding “object\_name” in the query it assumes it must be bound.

DECLARE

CURSOR rec

IS

SELECT q.\*

FROM (SELECT \*

FROM user\_objects uo) q

WHERE q.object\_name IS NOT NULL;

BEGIN

NULL;

END;

---

<div class="post-metadata">

**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\
**Post date:** [December 2, 2015, 11:40am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/7 "2015-12-02T11:40:05Z")

</div>

Fixed for next beta.

---

<div class="post-metadata">

**Author:** ![rrothlisberger](https://avatars.discourse-cdn.com/v4/letter/r/7cd45c/32.png) [@rrothlisberger](https://forums.toadworld.com/u/rrothlisberger)\
**Post date:** [December 2, 2015, 11:50am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/8 "2015-12-02T11:50:43Z")

</div>

Thanks

I have another oddity for you since you are fixing things, would you like it here or in a different

post? Has to do with a loop exiting prior to all the records being processed in the cursor.

---

<div class="post-metadata">

**Author:** ![mstaszew](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mstaszew/32/23_2.png) [@mstaszew](https://forums.toadworld.com/u/mstaszew)\
**Post date:** [December 2, 2015, 11:52am UTC](https://forums.toadworld.com/t/aw-toad-for-oracle-12-1-plsql-code-with-cursor-within-cursor-shift-enter-how-to-disable-edit-sql-window/28368/9 "2015-12-02T11:52:27Z")

</div>

Either way. If it’s also for Edit SQL showing at wrong time just lay it out there otherwise a new thread would be nice.
