Re: find columns used in procedures

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Mon, 20 Dec 2004 10:49:42 +0100
Message-ID: <cq677n$did$1@news.BelWue.DE>

Denis Do wrote:
> On 2004-12-17, Mark D Powell <Mark.Powell_at_eds.com> wrote:
>

>>Why go to all that trouble when you can just query
>>dba_dependencies to find the list of views, packages, functions, stored
>>procedures, and synonyms dependend on a table?
>>

>
> Mark, thanks for your comment - I agree that on table level it is useless.
> (and really crazy - so I stated it right in my previous post:-))
>
> But I can use that "invalidation" technique to help finding column dependencies
> in procedures.
>
> I just checked - dba_dependencies will show you table, referenced by program,
> but not column. If I create table, procedure and than start dropping *columns*,
> than invalidation of procedures will show me in which programs that dropped
> column was used.

And this is plain wrong. Your test just showed what happens if you make sure that the dropped column actually was referenced. How about testing what happens if you've got a procedure that references the table but not that particular column? (Demonstration at the end of this post).

Oracle does not check for the column but rather determines that the object changed, therefor all cursors must be recompiled and that is achieved by invalidating the procedures/packages that reference the changed object.

Just do a 'alter procedure xxx compile' after you dropped a unused column and you'll see that procedures that referenced the dropped column won't compile, however, every other will.

And just to backup my claims:

baer_at_DEMO10G>select object_name, object_type, status from user_objects;

OBJECT_NAME                                        OBJECT_TYPE         STATUS
-------------------------------------------------- ------------------- -------
DEMO_V                                             VIEW                VALID
T3                                                 TABLE               VALID
TEST_T3                                            PROCEDURE           VALID

baer_at_DEMO10G>desc t3
  Name                                                  Null?    Type
  ----------------------------------------------------- -------- ------------------------------------
  ID                                                             NUMBER
  COL2                                                           VARCHAR2(10)

baer_at_DEMO10G>select text from user_source where name = 'TEST_T3';

TEXT

procedure test_t3
as
begin

for i in (select id from t3) loop
dbms_output.put_line (to_char(i.id)); end loop;
end;

7 rows selected.

baer_at_DEMO10G>alter table t3 drop (col2);

Table altered.

baer_at_DEMO10G>select object_name, object_type, status from user_objects;

OBJECT_NAME                                        OBJECT_TYPE         STATUS
-------------------------------------------------- ------------------- -------
DEMO_V                                             VIEW                VALID
T3                                                 TABLE               VALID
TEST_T3                                            PROCEDURE           INVALID

Whoops - my procedure nowhere references col2, yet it still got invalidated.

QED However, if you take your advice one step further and after dropping the column *recompile* the function, then everything that's left invalid actually used the column - provided there is no dynamic sql involved because one of the drawbacks of dynamic sql is that it breaks the dependency chain.

Regards,
Holger Received on Mon Dec 20 2004 - 03:49:42 CST