Toad World® Forums

Bug in recognizing out binds in SQL execution

Hi all,
Toad 12.11.0.82 beta and last Toad 12.10, all x64 on Oracle 11.2.0.3 x64

Toad cannot run query of type:
select count(S.SUBSCRIBER_NO) into :b0:b1 from

Problem is “:b0:b1” which toad see as :b0" and error is:
ORA-01036: illegal variable name/number
Brg
Damir

Hey Damir,

If “:b0:b1” isn’t 2 separate bind variables, it would appear to be an illegal name for one, as shown in SQL*Plus:

SQL> variable b0:b1 number
SP2-0553: Illegal variable name “b0:b1”.
SQL>

How are you expecting Toad to treat it? Does SQL*Plus treat it the same way as Toad?

You also reference two recent versions of Toad – did older versions work differently?

Let us know!
Rich

14:42:57 Q383211@SMOBCST1>variable B0 VARCHAR2(32)
14:43:01 Q383211@SMOBCST1>variable B1 VARCHAR2(32)
14:43:01 Q383211@SMOBCST1>
14:43:01 Q383211@SMOBCST1>select count(*) into :b0:b1 from dba_users;

COUNT(*)

   161

Elapsed: 00:00:00.17
14:43:02 Q383211@SMOBCST1>

You also reference two recent versions of Toad
Yes, two versions.

One is latest beta and one is latest regular (12.10.0.32).

SO what is wrong with versions?

and programming form “:b0:b1” is a template for all AMDOCS applications.

Interesting! But in that context, the INTO clause appears to be ignored as there are no values in either B0 or B1, as well as the output being shown on screen. I think that can be further proved by running:

SQL> VARIABLE B0 NUMBER
SQL> SELECT USER_ID INTO :B0 FROM DBA_USERS;

This statement causes TOO_MANY_ROWS exception in PL/SQL, but SQL*Plus silently ignores the INTO clause.

So are you looking for Toad to emulate SQL*Plus and also ignore it?

Rich

Disclaimer: Just because I’m learning something new today unfortunately does not mean I get to go home early. [;)]

SELECT USER_ID INTO :B0 FROM DBA_USERS;
returns more than one value and error is normal in any SQL language.

You ask me to show you SQL*Plus example, what I suggest to any user on Toad forum, and I did shown you.
:slight_smile:

The same SQL doesn’t work in Toad.

Toad versions you have-all recent versions.

What do you want from me more?

Your statement:

select count(*) into :b0:b1 from dba_users;

…is functionally equivalent to:

select count(*) from dba_users;

…in SQL*Plus v11.2.0.3, as near as I can tell, as neither B0 nor B1 is populated from that SELECT, and it does not throw an error.

So, are you looking for Toad to ignore the INTO clause, like SQL*Plus does?

i want to execute the same SQL that runs in SQL*Plus with the same result in Toad SQL editor.

why?

there are also other many things related to that…i,e. getting correct sql_ID or getting plan for such a queries.

do I have to explain more?