If all you want is the number of times it has been used (from some arbitrary reset time) and the date it was last used, create a couple of private package variables.
package body package_name as
F1_Use_Count integer := 0;
F1_Last_Used timestamp;
...
function F1 ...
F1_Use_Count := F1_Use_Count + 1;
F1_Last_Used := SysTimestamp;
... the rest of the function
end F1;
-- You also want to add
function Get_F1_Use_Count return integer is begin
return F1_Use_Count;
end;
function Get_F1_Last_Used return timestamp is begin
return F1_Last_Used;
end
proc Reset_F1_Stats is begin
F1_Use_Count := 0;
F1_Last_Used := null;
end;
-- Or all three above could be done with
proc Get_Reset_F1_Stats( Use_count out integer, Use_Date out timestamp ) is begin
Use_count := F1_Use_Count;
Use_Date := F1_Last_Used;
F1_Use_Count := 0;
F1_Last_Used := null;
end;
end package_name;
EDIT:
To "session-proof" the action, write the values into a table instead of package variables.
CREATE TABLE Access_Stats(
Proc_Id VARCHAR2( 32 ) NOT NULL,
Access_Count INTEGER DEFAULT 0 NOT NULL,
Access_Date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY( Proc_Id )
);
Inside the package body:
Proc Set_Stats( PName Access_Stats.Proc_Id%type ) is begin
MERGE INTO Access_Stats a
USING(
SELECT 1 FROM dual ) tt
ON( a.Proc_Id = Upper( PName ))
WHEN MATCHED THEN
UPDATE
SET access_count = access_count + 1,
access_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT( Proc_Id, access_count )
VALUES( Upper( PName ), 1 );
Commit;
END;
Then in all the functions and procedures you want to track, just make sure they start out with a call to the Set_Stats proc:
Proc WhatEver...
Set_Stats( 'whatever' );
...
The getters would also have to be changed to read from the table. I would change the Get_Reset_F1_Stats proc to a more general Get_Reset_Stats version:
proc Get_Reset_Stats(
PName Access_Stats.Proc_Id%type,
Use_count out integer,
Use_Date out timestamp )
is begin
Select Access_Count, Access_Date
into Use_count, Use_Date
From Access_Stats
where Proc_Id = Upper( PName );
update Access_Stats
set Access_Count = 0
where Proc_Id = Upper( PName );
end;
The Get_Reset_Stats could just delete the row for the procedure being tracked, but by resetting the count to zero, the date of the reset is maintained. If you add other fields such as the user who executed the proc, the person who last executed (or reset) the procedure/function being tracked can also be maintained.
My standard caveat applies: the code shown above is designed more for illustration and is not presented as production-ready code. Modify to fit your own particular standards.
