13.0.0.28 - Code Analysis: Wrong positive of Rule 5809

Toad for Oracle - Beta
1

in_dummy is marked with 5809: Ensure elements in the SELECT LIST … are qualified by a table/view name …

CREATE PROCEDURE tst( in_dummy IN dual.dummy%TYPE )
AS
v_dummy dual.dummy%TYPE;
BEGIN
SELECT d.dummy
INTO v_dummy
FROM dual d
WHERE d.dummy IN (
SELECT d2.dummy
FROM dual d2
WHERE d2.dummy = in_dummy
);
END tst;

2

Hi Peter,

What if in_dummy was a column of the outer table (not dual for this matter) ? Then it would throw the parameter out of the game. That’s what this rule is about.

It’s easy enough to write the following to shut up the rule:

WHERE d2.dummy = tst.in_dummy

Would it make sense?

Thanks,
Andre

3

Hi Andre,

I understand the problem, but I will not change my code like this, especially with longer method names.

Also, the implementation is not really consistent. Your argument would still be valid for the following procedure where in_dummy is not highlighted:

CREATE TABLE my_dual AS ( SELECT dummy, dummy AS in_dummy FROM dual );

CREATE OR REPLACE PROCEDURE tst( in_dummy IN dual.dummy%TYPE )
AS
v_dummy dual.dummy%TYPE;
BEGIN
SELECT d.dummy
INTO v_dummy
FROM my_dual d
WHERE d.dummy = in_dummy;
END tst;

If you follow this through, each parameter used in a query has to be fully qualified.

Thanks, Peter