Strange INTO binds combinations

Toad for Oracle - Beta
1

Hi,

Toad 12.9.0.18 and 12.8.x (last off. version). Oracle 11.1.0.7 and Oracle 11.2.0.3
Have AMDOCS application which has a lot INTO bind parts like:
:b0:b1

As you see, two binds are one beside another, and I do not want to expose why is it so, but has a reason (nvl concatenation...) in Java programs....

But problem with Toad is that whatever I do (run with bind values):

8176.1.png
8176.1.png1129×562 25.1 KB

What I think is that somehow Toad miss ":b1" (look the list of binds in first picture) and probably all other values are missed.

Result is that I get an error:

2781.2.png

and the only way to run that query with binds from Toad is to remove "INTO :b0:b1" part.

Brg,
Damir

P.S.
"Jump" action from the previous dialog in this case also doesn't work...like in many cases in many other casess

2

I’m not following what the expected behavior is here, Damir and I’m having trouble finding anything online about such bind usage. When I run tests in SQL*Plus and in Toad there are differences and Toad does look like it has some problem, but I’m not sure what it is exactly.

I testing using… SELECT :b0:b1 FROM dual;

SQL*Plus. I’m not sure what it did in the first SELECT, but it used the value of :b0 and displays :B0B1 as the column name. The second select where each bind is broken out works as you’d expect.

SQLPlus.png

In Toad it’s as you see it where only :b0 is found. Our underlying querying component identifies the statement as having two binds. I’ve modified Toad to locate both of them accordingly and when set and sent to Oracle I get an ORA-1036 error back.

3

Look, here is code from db server directly :

16:44:55 SQL>@sql_id 1 2s5u5h47q8978

SQL_FULLTEXT

select count(*) into :b0:b1 from CTN_STOCK CTN ,NUMBER_GROUP NGP ,NM_LOGICAL_HLR LHLR ,NM_PHYSICAL_HLR PHLR where ((((((((((((:b2=(-1) or CTN.CTN_ST
ATUS=:b3) and (:b4=(-1) or (LENGTH(RTRIM(CTN.CTN))=LENGTH(RTRIM(:b5)) and CTN.CTN>=:b5))) and (:b7=(-1) or (LENGTH(RTRIM(CTN.CTN))=LENGTH(RTRIM(:b8))
and CTN.CTN<=:b8))) and (:b10=(-1) or CTN.NGP=:b11)) and CTN.NL=:b12) and ((:b13=(-1) or (:b14=‘Y’ and CTN.PAIRED_RESOURCE is not null )) or (:b14='N
’ and CTN.PAIRED_RESOURCE is null ))) and CTN.NGP=NGP.NGP_ID) and NGP.LOGICAL_HLR=LHLR.LOGICAL_HLR) and LHLR.PHYSICAL_HLR=PHLR.PHYSICAL_HLR) and (:b16
=(-1) or PHLR.PRODUCT_TYPE=:b17)) and (:b18=(-1) or LHLR.LOGICAL_HLR=:b19)) and (:b20=(-1) or LHLR.PHYSICAL_HLR=:b21))

Currently run that sql

no rows selected

IID CHILD_NR PLAN_HASH_VALUE ROWS EXECS ELA_SECS AVG_EXEC FIRST_LOAD LAST_LOAD LAST_ACTIVE USER_IO_WAIT CPU_TIME BUFFER_GETS BUFF_EXEC BA BS

1 1 2083789168 4 4 10.184875 2.54622 01-26/12:58:34 01-26/16:26:37 26 16:29:52 .16752 .936 2835840 708960 N N

Where sql_id is a script with content where first result SQL_FULLTEXT is retrieved as:

select
sql_fulltext
from
gv$sql
where
inst_id=&&1 AND
sql_id =’&&2’
and rownum<=1
;

and second result set is retrieved from:

SELECT inst_id iid,
CHILD_NUMBER,
plan_hash_value,
rows_processed,
EXECUTIONS,
ELAPSED_TIME/1000000 ELAPSED_TIME,
case when nvl(EXECUTIONS,0)=0 then -1 else ELAPSED_TIME/EXECUTIONS/1000000 end AVG_EXEC,
SUBSTR(FIRST_LOAD_TIME,6,99)FIRST_LOAD,
SUBSTR(LAST_LOAD_TIME,6,99)LAST_LOAD,
to_char (LAST_ACTIVE_TIME, ‘dd hh24:mi:ss’) LAST_ACTIVE,
user_io_wait_time/1000000 USER_IO_WAIT_TIME, CPU_TIME/10000000 CPU_TIME,
BUFFER_GETS,
case when nvl(EXECUTIONS,0)=0 then -1 else BUFFER_GETS/EXECUTIONS end BUFF_EXEC,
IS_BIND_AWARE, IS_BIND_SENSITIVE
FROM GV$SQL
WHERE INST_ID = &&1
and SQL_ID = ‘&&2’
order by iid, CHILD_NUMBER;

and this code works as hell for ages.