On running SQL Scanner against a stored procedure or package SQL Scanner is not handling constants that are defined in the package. It is instead treating the constants as bind variables. This may result in a number of false positives for Problematic and Complex SQL statements.
The execution plan in SQL Optimizer shows that it is putting a :V_ in front of the constant name.
e.g.
declare
account_id integer;
c_acct_type constant char(1) := 'A';
c_ops_sla_limit constant number := 1;
Begin
select distinct b.acct_id
into account_id
from acct a,
acct_status_cond b
where a.acct_id = b.acct_id
and a.end_date is NULL
and b.end_date is NULL
and (b.acct_cond_type_cd = c_acct_type)
and b.created_date < (sysdate - (30 + c_ops_sla_limit));
END;
/
The execution plan in SQL Scanner shows
and (b.acct_cond_type_cd = :V_c_acct_type)
and b.created_date < (sysdate - (30 + TO_NUMBER(:V_c_ops_sla_limit));
The SQL Scanner parser is only able to identify a SELECT, INSERT, UPDATE, or DELETE statement. The constant declaration is not recognized and therefore those constants are unknown to SQL Scanner. When a SQL statement is found, the Scanner will look at any non-column references and treat them as variables.
WORKAROUND
none
STATUS
Enhancement request ST49852 has been submitted to Development for consideration in a future release of Quest SQL Optimizer for Oracle
© 2025 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center