SQL Scanner's SQL statement execution plan showing constants, defined in packages, as bind variab (4311577)

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