SQL & PL/SQL

Consider using PL/Scope and/or compiler warnings to determine this.

Alternatively some IDEs such as TOAD (and probably others) can report such.

Hello,

as suggested by padders you can use PL/Scope, an example:

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

CREATE OR REPLACE PACKAGE ui_test1 AS

    global_number   NUMBER := 9;

FUNCTION get_number

    RETURN NUMBER;

END ui_test1;

/

CREATE OR REPLACE PACKAGE BODY ui_test1 AS

PROCEDURE setNull

    (

     p_varchar          IN OUT VARCHAR2

    )

IS

BEGIN

    p_varchar := NVL(p_varchar,'NULL');

END setNull;

FUNCTION get_number

    RETURN NUMBER

IS

    FUNCTION setZero

        (

         p_number       IN NUMBER

        )

        RETURN NUMBER

    IS

    BEGIN

        RETURN NVL(p_number,0);

    END setZero;

BEGIN

    RETURN global_number;

END get_number;

END ui_test1;

/

SELECT  DISTINCT

        object_name

       ,object_type

       ,name

       ,type

       ,line

       ,col

FROM    all_identifiers obj

WHERE   obj.owner = USER

AND     obj.usage = 'DECLARATION'

AND     obj.object_name = 'UI_TEST1'

AND     NOT EXISTS (

            SELECT  1

            FROM    all_identifiers with_rh

            WHERE   obj.signature = with_rh.signature

            AND     with_rh.usage IN ('REFERENCE','CALL','ASSIGNMENT')

            )

ORDER BY TYPE

        ,object_name

        ,object_type

        ,name;

OBJECT_NAME  OBJECT_TYPE   NAME       TYPE       LINE COL

------------ ------------- ---------- ---------- ---- ---

UI_TEST1     PACKAGE       GET_NUMBER FUNCTION     11  10

UI_TEST1     PACKAGE BODY  SETZERO    FUNCTION     35  14

UI_TEST1     PACKAGE       UI_TEST1   PACKAGE       1   9

UI_TEST1     PACKAGE BODY  SETNULL    PROCEDURE    12  11

Regards

Marcus

Thanks for the PL/Scope query example!

In general, if you want to be slightly more aggressive in finding and cleaning up the messy code, you should remove 'ASSIGNMENT' from the uses you're filtering out. While you can't blindly remove any lines returned by the resulting query, in most cases if a local variable only appears on the LHS of an assignment, it is useless and should be removed.

SELECT  DISTINCT

        object_name

       ,object_type

       ,name

       ,type

       ,line

       ,col

FROM    all_identifiers obj

WHERE   obj.owner = USER

AND     obj.usage = 'DECLARATION'

AND     obj.object_name = 'UI_TEST1'

AND     NOT EXISTS (

            SELECT  1

            FROM    all_identifiers with_rh

            WHERE   obj.signature = with_rh.signature

            AND     with_rh.usage IN ('REFERENCE','CALL')

            )

ORDER BY TYPE

        ,object_name

        ,object_type

        ,name;