CODEWITHSUNDEEP
plsqloracle11g
Obenland
Obenland

Reputation: 866

Trigger on procedure call

my team and I started working on an legacy Oracle DB project. The whole business logic is written in PL/SQL without any documentation.

Now we want to get an overview of everything, especially which procedures / functions / packages get called. The problem is there are packages with suffix _v1, _v2, _v1_org, _v2_new and we have no idea which of them are really in use.

We don't want to change everything by adding a logging statement in the beginning of each function or procedure.

Is there some kind of trigger which fires on function call?

For example calling this code

CREATE OR REPLACE FUNCTION helloWorld AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello world!');
END;

would output something like this (line order is irrelevant)

Function "helloWorld" was called.
Hello world!

Upvotes: 0

Views: 54

Answers (2)

gobnepla
gobnepla

Reputation: 644

You could make use of DBMS_TRACE or DBMS_PROFILER to analyze what is happening.

Upvotes: 1

Dmitriy
Dmitriy

Reputation: 5565

The question is quite broad. In your place, I would start from the following:

  • Identify enter points (if possible): which procedures/functions/packages are being called from a database client application.

  • Add an initialisation section into packages:

    create or replace package body my_package as 
...
-- add this after all functions, procedures, etc:
begin
  dbms_output.put_line('Package my_package is initialised');
end;

    This will allow you to define which packages are used. An initialisation section is executed once on the first package call in the session.

  • Use DBA_DEPENDENCIES system view to check dependencies between database objects.

  • Also, there is an Audit feature, quite powerful. See example on Ask Tom. Maybe it is the best thing in your case.

Upvotes: 2

Related Questions