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

Toad for Oracle
1

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]

Gesendet: Mittwoch, 15. Januar 2014 18:10

An: 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 ?

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

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

2

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

3

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;

4

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

5

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/

6

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;

7

Fixed for next beta.

8

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.

9

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.