SQL error on script run in editor (bug)

Toad for Oracle & DevOps Toolkit
1

Toad 12.10.0.30 x64

Next sql is in script called sql_binds and I call it inside Editor like:

"@sql_binds 1 75undgc5qufbm 10"
where call is made of:
@sql_binds [iid] [sql_id] [last_number_of_days]

I got error (and in sqlplus it run without any errors)

SELECT iid,
b.sql_id,
PLAN_HASH_VALUE PLAN_HASH_V,
b.name,
b.position,
'"'||value_string||'"' value_string,
EXECS,
A_ROWS,
ELA_SECS,
ELAPSED_TIME_TOTAL,
snap_id,
to_char (begin_interval_time, 'DD.MM hh24:mi:ss,ssss') begin_interval_time,
to_char (end_interval_time, 'hh24:mi:ss,ssss') end_interval_time
FROM (
SELECT sh.instance_number iid,
sql_id,
PLAN_HASH_VALUE,
sh.snap_id,
begin_interval_time,
end_interval_time,
bind_data,
EXECUTIONS_DELTA EXECS,
ROWS_PROCESSED_DELTA A_ROWS,
CASE EXECUTIONS_DELTA
WHEN 0 THEN -1
ELSE ELAPSED_TIME_DELTA/1000000/EXECUTIONS_DELTA
END ELA_SECS,
ELAPSED_TIME_TOTAL/1000000/60 ELAPSED_TIME_TOTAL
FROM dba_hist_sqlstat sql, dba_hist_snapshot sh
WHERE 1 = 1
-- AND bind_data is not null
AND sql_id = '75'
AND sh.instance_number=1
AND SQL.INSTANCE_NUMBER=sh.instance_number
AND SQL.SNAP_ID = SH.SNAP_ID
AND begin_interval_time >= sysdate - undgc5qufbm
) x,
TABLE (DBMS_SQLTUNE.extract_binds (x.bind_data)) xx,
DBA_HIST_SQL_BIND_METADATA b
where b.sql_id = x.sql_id
AND b.position = xx.position
ORDER BY iid, snap_id, PLAN_HASH_VALUE, b.position

                                              *

Error at line 36
ORA-00904: "UNDGC5QUFBM": invalid identifier
Script Terminated on line 20 of u:\sql\sql_binds.SQL.

What i see is that second parameter "75undgc5qufbm" is not passed correctly to sql. It is represented as
AND sql_id = '75'

so what results with wrong bind on third part
AND begin_interval_time >= sysdate - undgc5qufbm

I hope this helps and is enough to find a bug

2

To me it looks like you are trying to subtract a string value (75undgc5qufbm) from sysdate. I am not sure how that would work since your string is not a numeric value.

Maybe you could provide more details about what this string represents?

-Mark

3

No!

Toad misinterpreted pram values passed to script.

please look better in mine description

4

Did you try putting the string variables inside of quotes?

@sql_binds 1 “75undgc5qufbm” 10

5

Yes I did.
No error but no result as well:

no rows selected.Elapsed: 00:00:00.07

Here is the whole script-parameters explained on the begining

col name for a10
col VALUE_STRING for a35
col BEGIN_INTERVAL_TIME for a19 HEADING BEGIN_INTERVAL
col END_INTERVAL_TIME for a13 HEADING END_INTERVAL
col position for 90 HEADING POS
col A_ROWS HEADING ROWS
col EXECS for 999G990
col A_ROWS for 999G990
col ELA_SECS for 99G990D9990
col ELAPSED_TIME_TOTAL HEADING TOTAL_MIN
– col ELAPSED_TIME_HEADING DELTA ELA_DELTA
set pages 1000
set linesize 180

break on iid on sql_id on PLAN_HASH_VALUE on snap_id on EXECS on A_ROWS on ELA_SECS on ELAPSED_TIME_TOTAL on begin_interval_time on end_interval_time

SELECT iid,
b.sql_id,
PLAN_HASH_VALUE PLAN_HASH_V,
b.name,
b.position,
‘"’||value_string||’"’ value_string,
EXECS,
A_ROWS,
ELA_SECS,
ELAPSED_TIME_TOTAL,
snap_id,
to_char (begin_interval_time, ‘DD.MM hh24:mi:ss,ssss’) begin_interval_time,
to_char (end_interval_time, ‘hh24:mi:ss,ssss’) end_interval_time
FROM (
SELECT /*+ USE_MERGE(sh,sql) ORDERED */
sh.instance_number iid,
sql_id,
PLAN_HASH_VALUE,
sh.snap_id,
begin_interval_time,
end_interval_time,
bind_data,
EXECUTIONS_DELTA EXECS,
ROWS_PROCESSED_DELTA A_ROWS,
CASE EXECUTIONS_DELTA
WHEN 0 THEN -1
ELSE ELAPSED_TIME_DELTA/1000000/EXECUTIONS_DELTA
END ELA_SECS,
ELAPSED_TIME_TOTAL/1000000/60 ELAPSED_TIME_TOTAL
FROM dba_hist_sqlstat sql, dba_hist_snapshot sh
WHERE 1 = 1
– AND bind_data is not null
AND sql_id = ‘&&2’
AND sh.instance_number=&&1
AND SQL.INSTANCE_NUMBER=sh.instance_number
AND SQL.SNAP_ID = SH.SNAP_ID
AND begin_interval_time >= sysdate - &&3
) x,
TABLE (DBMS_SQLTUNE.extract_binds (x.bind_data)) xx,
DBA_HIST_SQL_BIND_METADATA b
where b.sql_id = x.sql_id
AND b.position = xx.position
ORDER BY iid, snap_id, PLAN_HASH_VALUE, b.position;

6

I got it to work with single quotes:

@sql_binds 1 ‘75undgc5qufbm’ 10

7

Thx-work.

But then this is different than SQLPlus parameters.

Any rule here to know for the future use?

8

That’s not my part of Toad - let’s wait for one of the other devs to answer.