Assistance for newbie creating a package using DBMS_SCHEDULER and SYS.DBA_AUDIT_TRAIL
I'm very new to PL/SQL (and programming in general). I would like to create a report that shows the # of logins per hour for a separate application and it will be based on this program. For this program, DB1 will be my production database. The user can provide a readonly, no sys privs, login to DB1. I am writing all code on DB2.
I am using SYS.DBA_AUDIT_TRAIL for finding the accumulated logins.
I am trying to create a program (and I want it packaged) that aggregates logins by hour and inserts the results into this table.
For the above column definitions:
access_date = date of the aggregated logins
access_hour = the hour of the logins
login_count = count of logins
audit_timestamp = when the aggregation occurred
This program needs to run on a schedule of no more than once per hour. I'm making no assumption that this program will run every hour, for instance: I realize there is a possibility that the database could become inaccessible to me for a few hours.
If my program just looks at ‘the last hour’ then I will have lost all data for the inaccessible hours so I need to create it so as to aggregate all logins since the last successful aggregation.
With my table, I am trying to create an audit process log that will log any errors that have occurred. I intend to only log errors (not necessarily every run) in this table.
I am going to use DB privs, dbms_scheduler exec privs, CREATE JOB priv.
So far I have generated a query that I am going to use in the package for retrieving a login date, login hour, and a count
I have a functional procedure made that I am going to eventually insert into my package as well:
Finally I have this non-functional proc I began to work on but didn't compile the first run (will be glad for any tips):
My intention is to take my main query and turn it into a standalone procedure. Didn't compile, so please tell me what you see I'm doing wrong. Thanks!
I am using SYS.DBA_AUDIT_TRAIL for finding the accumulated logins.
I am trying to create a program (and I want it packaged) that aggregates logins by hour and inserts the results into this table.
(Table created on DB2)
CREATE TABLE access_log (
access_date DATE
,access_hour NUMBER
,login_count NUMBER
,audit_timestamp TIMESTAMP
)For the above column definitions:
access_date = date of the aggregated logins
access_hour = the hour of the logins
login_count = count of logins
audit_timestamp = when the aggregation occurred
This program needs to run on a schedule of no more than once per hour. I'm making no assumption that this program will run every hour, for instance: I realize there is a possibility that the database could become inaccessible to me for a few hours.
If my program just looks at ‘the last hour’ then I will have lost all data for the inaccessible hours so I need to create it so as to aggregate all logins since the last successful aggregation.
With my table, I am trying to create an audit process log that will log any errors that have occurred. I intend to only log errors (not necessarily every run) in this table.
I am going to use DB privs, dbms_scheduler exec privs, CREATE JOB priv.
So far I have generated a query that I am going to use in the package for retrieving a login date, login hour, and a count
SELECT
TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
,COUNT(*)
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY TRUNC(TIMESTAMP, 'DD')
,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1 , 2
--on db1I have a functional procedure made that I am going to eventually insert into my package as well:
CREATE OR REPLACE FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS
/* variables */
L_LOGGED_TIMESTAMP TIMESTAMP;
BEGIN
/* query to return MAX timestamp */
SELECT
MAX(AUDIT_TIMESTAMP)
INTO L_LOGGED_TIMESTAMP
FROM ACCESS_LOG;
/* returning MAX timestamp value*/
RETURN L_LOGGED_TIMESTAMP;
END;Finally I have this non-functional proc I began to work on but didn't compile the first run (will be glad for any tips):
create or replace procedure access_date_hour_count IS
L_QUERY VARCHAR2(4000);
begin
SELECT
TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
,COUNT(*)
INTO L_QUERY
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY TRUNC(TIMESTAMP, 'DD')
,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1 , 2;
end access_date_hour_count;My intention is to take my main query and turn it into a standalone procedure. Didn't compile, so please tell me what you see I'm doing wrong. Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So here is where I am so far:
and the body
Now I need to work this query into my package:
CREATE OR REPLACE PACKAGE PKG_AUDIT_ACCESS_TABLES IS
/*
Original Author: Me
Created Date: 23-Aug-2012
Purpose: For storing procedures to drop, create, and archive new tables
*/
/* START WITH THE HEADER THEN WORK ON THE IMPLEMENTATION */
/* Package specification functions and procedure declarations */
PROCEDURE W(STR VARCHAR2);
FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP;
END PKG_AUDIT_ACCESS_TABLES;and the body
CREATE OR REPLACE PACKAGE BODY PKG_AUDIT_ACCESS_TABLES IS
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
/* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
PROCEDURE W(STR VARCHAR2) IS
BEGIN
/* setting variable to simpler to use STR variable for outputting */
DBMS_OUTPUT.PUT_LINE(STR);
END;
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS
/* variables */
L_LOGGED_TIMESTAMP TIMESTAMP;
BEGIN
/* query to return MAX timestamp */
SELECT MAX(AUDIT_TIMESTAMP)
INTO L_LOGGED_TIMESTAMP
FROM ACCESS_LOG;
/* returning MAX timestamp value*/
RETURN L_LOGGED_TIMESTAMP;
END;
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
BEGIN
NULL;
END PKG_AUDIT_ACCESS_TABLES;Now I need to work this query into my package:
SELECT
TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
,COUNT(*)
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY TRUNC(TIMESTAMP, 'DD')
,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1 , 2;ASKER
thanks
ASKER
Silly question but perhaps I've been staring too long and you'll see it right away:
CREATE OR REPLACE PROCEDURE CALLING_ACCESS_LOG IS
BEGIN
INSERT INTO ACCESS_LOG
(SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
,COUNT(*)
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
AND RETURN_MAX_AUDIT_TIME > A.TIMESTAMP
GROUP BY TRUNC(TIMESTAMP, 'DD')
,TO_CHAR(TIMESTAMP, 'HH24')
ORDER BY 1
,2);
END CALLING_ACCESS_LOG;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Silly me, got it now
CREATE OR REPLACE PROCEDURE CALLING_ACCESS_LOG IS
BEGIN
INSERT INTO ACCESS_LOG
(SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
,COUNT(*)
,A.TIMESTAMP
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
AND RETURN_MAX_AUDIT_TIME > A.TIMESTAMP
GROUP BY TRUNC(TIMESTAMP, 'DD')
,TO_CHAR(TIMESTAMP, 'HH24') );
END CALLING_ACCESS_LOG;ASKER
Thought I was done but nothing is logging to my table ACCESS_LOG. Here is my spec and body:
and my test window:
CREATE OR REPLACE PACKAGE PKG_AUDIT_ACCESS_TABLES IS
/*
Original Author: Me
Created Date: 23-Aug-2012
Purpose: For storing procedures to drop, create, and archive new tables
*/
/* START WITH THE HEADER THEN WORK ON THE IMPLEMENTATION */
/* Package specification functions and procedure declarations */
PROCEDURE W(STR VARCHAR2);
FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP;
PROCEDURE CALLING_ACCESS_LOG;
END PKG_AUDIT_ACCESS_TABLES;CREATE OR REPLACE PACKAGE BODY PKG_AUDIT_ACCESS_TABLES IS
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
/* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
PROCEDURE W(STR VARCHAR2) IS
BEGIN
/* setting variable to simpler to use STR variable for outputting */
DBMS_OUTPUT.PUT_LINE(STR);
END;
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
/* function returns the MAX timestamp from table ACCES_LOG */
FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS
/* variables */
L_LOGGED_TIMESTAMP TIMESTAMP;
BEGIN
/* query to return MAX timestamp */
SELECT MAX(AUDIT_TIMESTAMP)
INTO L_LOGGED_TIMESTAMP
FROM ACCESS_LOG;
/* returning MAX timestamp value*/
RETURN L_LOGGED_TIMESTAMP;
COMMIT;
END;
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
/* procedure inserts into log table the current values when called */
PROCEDURE CALLING_ACCESS_LOG IS
BEGIN
INSERT INTO ACCESS_LOG
(SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
,COUNT(*)
,A.TIMESTAMP
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
AND RETURN_MAX_AUDIT_TIME > A.TIMESTAMP
GROUP BY TRUNC(TIMESTAMP, 'DD')
,TO_CHAR(TIMESTAMP, 'HH24'));
COMMIT;
END CALLING_ACCESS_LOG;
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
BEGIN
NULL;
END PKG_AUDIT_ACCESS_TABLES;and my test window:
begin
myschema.pkg_audit_access_tables.CALLING_ACCESS_LOG;
end;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
1 8/1/2012 0 1 24-AUG-12 02.59.52.291884 PM
2 8/2/2012 1 2 24-AUG-12 03.00.17.372953 PMare currently in my ACCESS_LOG table though
btw, here is an updated body, adjusted for scoping.
CREATE OR REPLACE PACKAGE BODY PKG_AUDIT_ACCESS_TABLES IS
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
/* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
PROCEDURE W(STR VARCHAR2) IS
BEGIN
/* setting variable to simpler to use STR variable for outputting */
DBMS_OUTPUT.PUT_LINE(STR);
END;
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
/* procedure inserts into log table the current values when called. Placing it here according to recent scoping standards */
PROCEDURE CALLING_ACCESS_LOG IS
/* variables */
L_MAX_AUDIT_TIME VARCHAR2(4000);
/* function returns the MAX timestamp from table ACCES_LOG */
FUNCTION RETURN_MAX_AUDIT_TIME RETURN TIMESTAMP IS
/* variables */
L_LOGGED_TIMESTAMP TIMESTAMP;
BEGIN
/* query to return MAX timestamp */
SELECT MAX(AUDIT_TIMESTAMP)
INTO L_LOGGED_TIMESTAMP
FROM ACCESS_LOG;
W('from the return max function');
/* returning MAX timestamp value*/
RETURN L_LOGGED_TIMESTAMP;
END;
BEGIN
L_MAX_AUDIT_TIME := RETURN_MAX_AUDIT_TIME;
INSERT INTO ACCESS_LOG
(SELECT TRUNC(TIMESTAMP, 'DD') ACCESS_DATE
,TO_CHAR(TIMESTAMP, 'HH24') ACCESS_HOUR
,COUNT(*)
,SYSTIMESTAMP
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
AND L_MAX_AUDIT_TIME > A.TIMESTAMP
GROUP BY TRUNC(A.TIMESTAMP, 'DD')
,TO_CHAR(A.TIMESTAMP, 'HH24'));
W('from the calling access log proc');
COMMIT;
END CALLING_ACCESS_LOG;
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
BEGIN
NULL;
END PKG_AUDIT_ACCESS_TABLES;
did you consider HH24 for truncating by the hour ?
select trunc(timestamp, 'HH24') x, au.*
from SYS.DBA_AUDIT_TRAIL au
where rownum < 200;
you could have used that returned datetime as 1 column
select trunc(timestamp, 'HH24') x, au.*
from SYS.DBA_AUDIT_TRAIL au
where rownum < 200;
you could have used that returned datetime as 1 column
ASKER