Toad World® Forums

Strange INTO binds combinations

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):

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

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.

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.