I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing.

-- Package Name : users_granted_role
-- This package was created to spool user and their respective Privs from oracle data Dictionary.
CREATE OR REPLACE PACKAGE users_granted_role
IS
 procedure write_op (pv_str in varchar2);
 function user_or_role(pv_grantee in dba_users.username%type) return varchar2;
 function role_pwd(pv_role in dba_roles.role%type)   return varchar2;
 procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number);
 procedure get_role (pv_role in varchar2);
 Procedure extract_user_role_details;
END users_granted_role;
/
create or replace package body users_granted_role
IS
 output_method varchar2(1) :='S';
 skip_user varchar2(1) := 'N';
 user_to_skip varchar2(20) :='TEST%';
 --
 lg_fptr utl_file.file_type;
 lv_file_or_screen varchar2(1):='S';
 v_tag      VARCHAR2(8);
 v_filename VARCHAR2(30);
 v_today    VARCHAR2(8);
 --
 cursor find_all_roles is
 Select    role
 from    dba_roles;
 --
procedure write_op (pv_str in varchar2) is
 begin
 v_today    := TO_CHAR(sysdate,'YYYYMMDD');
 v_tag      := 'UserPriv';
 v_filename := 'User_Privileges'|| v_today;

 if lv_file_or_screen='S' then
 dbms_output.put_line(v_tag || v_filename||' '||pv_str);
 else
 utl_file.put_line(lg_fptr,pv_str);
 end if;
 exception
 when utl_file.invalid_path  then
 dbms_output.put_line('invalid path');
 when utl_file.invalid_mode  then
 dbms_output.put_line('invalid mode');
 when utl_file.invalid_filehandle  then
 dbms_output.put_line('invalid filehandle');
 when utl_file.invalid_operation  then
 dbms_output.put_line('invalid operation');
 when utl_file.read_error  then
 dbms_output.put_line('read error');
 when utl_file.write_error  then
 dbms_output.put_line('write error');
 when utl_file.internal_error  then
 dbms_output.put_line('internal error');
 when others then
 dbms_output.put_line('ERROR (write_op) => '||sqlcode);
 dbms_output.put_line('MSG (write_op) => '||sqlerrm);

end write_op;
 --
function user_or_role(pv_grantee in dba_users.username%type) return varchar2 is
 --
 cursor c_use (cp_grantee in dba_users.username%type) is
 select  'USER' userrole
 from    dba_users u
 where   u.username=cp_grantee
 union
 select  'ROLE' userrole
 from    dba_roles r
 where   r.role=cp_grantee;
 --
 lv_use c_use%rowtype;
 --
 begin
 open c_use(pv_grantee);
 fetch c_use into lv_use;
 close c_use;
 return lv_use.userrole;
 exception
 when others then
 dbms_output.put_line('ERROR (user_or_role) => '||sqlcode);
 dbms_output.put_line('MSG (user_or_role) => '||sqlerrm);
end user_or_role;
 -----------------
function role_pwd(pv_role in dba_roles.role%type)   return varchar2 is
 --
 cursor c_role(cp_role in dba_roles.role%type) is
 select    r.password_required
 from    dba_roles r
 where    r.role=cp_role;
 --
 lv_role c_role%rowtype;
 --
 begin
 open c_role(pv_role);
 fetch c_role into lv_role;
 close c_role;
 return lv_role.password_required;
 exception       
 when others then null;
 --dbms_output.put_line('ERROR (role_pwd) => '||sqlcode);
 --dbms_output.put_line('MSG (role_pwd) => '||sqlerrm);
end role_pwd;
 --
procedure get_users(pv_grantee in dba_roles.role%type,pv_tabstop in out number) is
 --
 lv_tab varchar2(50):='';
 lv_loop number;
 lv_user_or_role dba_users.username%type;
 --
 cursor c_user (cp_username in dba_role_privs.grantee%type) is
 select  d.grantee,
 d.admin_option
 from    dba_role_privs d
 where   d.granted_role=cp_username;
 --
 begin
 pv_tabstop:=pv_tabstop+1;
 for lv_loop in 1..pv_tabstop loop
 lv_tab:=lv_tab||chr(9);
 end loop;

 for lv_user in c_user(pv_grantee) loop
 lv_user_or_role:=user_or_role(lv_user.grantee);
 if lv_user_or_role = 'ROLE' then
 if lv_user.grantee = 'PUBLIC' then
 write_op(lv_tab||'Role => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option
 ||'|PWD = '||role_pwd(lv_user.grantee)||')');
 else
 write_op(lv_tab||'Role => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option
 ||'|PWD = '||role_pwd(lv_user.grantee)||')'
 ||' which is granted to =>');
 end if;
 get_users(lv_user.grantee,pv_tabstop);
 else
 if upper(skip_user) = 'Y' and lv_user.grantee like upper(user_to_skip) then
 null;
 else
 write_op(lv_tab||'User => '||lv_user.grantee
 ||' (ADM = '||lv_user.admin_option||')');
 end if;
 end if;
 end loop;
 pv_tabstop:=pv_tabstop-1;
 lv_tab:='';
 exception
 when others then
 dbms_output.put_line('ERROR (get_users) => '||sqlcode);
 dbms_output.put_line('MSG (get_users) => '||sqlerrm);       
end get_users;
----
procedure get_role (pv_role in varchar2) is
 --
 cursor c_main (cp_role in varchar2) is
 select    p.grantee,
 p.admin_option
 from    dba_role_privs p
 where    p.granted_role=cp_role;
 --
 lv_userrole dba_users.username%type;
 lv_tabstop number;
 --
 --
begin
 lv_tabstop:=1;
 for lv_main in c_main(pv_role) loop   
 lv_userrole:=user_or_role(lv_main.grantee);
 if lv_userrole='USER' then
 if upper(skip_user) = 'Y' and lv_main.grantee like upper(user_to_skip) then
 null;
 else
 write_op(chr(9)||'User => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option||')');
 end if;
 else
 if lv_main.grantee='PUBLIC' then
 write_op(chr(9)||'Role => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option
 ||'|PWD = '||role_pwd(lv_main.grantee)||')');
 else
 write_op(chr(9)||'Role => '||lv_main.grantee
 ||' (ADM = '||lv_main.admin_option
 ||'|PWD = '||role_pwd(lv_main.grantee)||')'
 ||' which is granted to =>');
 end if;
 get_users(lv_main.grantee,lv_tabstop);
 end if;
 end loop;
 exception
 when others then
 dbms_output.put_line('ERROR (get_role) => '||sqlcode);
 dbms_output.put_line('MSG (get_role) => '||sqlerrm);
end get_role;

Procedure extract_user_role_details is
begin
 write_op('Users_granted_role: Release 1.0 - Author : Sagar PATIL on '|| sysdate);
 for role_to_find in find_all_roles loop
 lv_file_or_screen:= upper(output_method);
 write_op(chr(10));
 write_op('Investigating Role => '||upper(role_to_find.role)||' (PWD = '
 ||role_pwd(upper(role_to_find.role))||') which is granted to =>');
 write_op('====================================================================');
 get_role(upper(role_to_find.role));
 end loop;
exception
 when others then
 dbms_output.put_line('ERROR (main) => '||sqlcode);
 dbms_output.put_line('MSG (main) => '||sqlerrm);
end extract_user_role_details;
end;
/

Run it as below

SQL> spool list_of_users.lst
SQL> set serveroutput on size 20000;
SQL> exec users_granted_role.extract_user_role_details;

UserPrivUser_Privileges20101026 Investigating Role => CONNECT (PWD = NO) which is granted to =>
====================================================================
UserPrivUser_Privileges20101026         User => WMSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_CSW_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => SYSMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => GRIDCONTROL (ADM = NO)
UserPrivUser_Privileges20101026         User => RMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => MDDATA (ADM = NO)
UserPrivUser_Privileges20101026         User => OWBSYS (ADM = YES)
UserPrivUser_Privileges20101026         User => SYSMAN_MDS (ADM = NO)
UserPrivUser_Privileges20101026         User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => MDSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_WFS_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => APEX_030200 (ADM = YES)
UserPrivUser_Privileges20101026         User => SCOTT (ADM = NO)
UserPrivUser_Privileges20101026

UserPrivUser_Privileges20101026 Investigating Role => RESOURCE (PWD = NO) which is granted to =>
====================================================================
UserPrivUser_Privileges20101026         User => WMSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SCOTT (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_CSW_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => RMAN (ADM = NO)
UserPrivUser_Privileges20101026         Role => LOGSTDBY_ADMINISTRATOR (ADM = NO|PWD =NO) which is granted to =>
UserPrivUser_Privileges20101026                 User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => EXFSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SPATIAL_WFS_ADMIN_USR (ADM = NO)
UserPrivUser_Privileges20101026         User => CTXSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => OLAPSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => MDSYS (ADM = NO)
UserPrivUser_Privileges20101026         User => SYSMAN_MDS (ADM = NO)
UserPrivUser_Privileges20101026         User => XDB (ADM = NO)
UserPrivUser_Privileges20101026         User => APEX_030200 (ADM = YES)
UserPrivUser_Privileges20101026         User => SYS (ADM = YES)
UserPrivUser_Privileges20101026         User => SYSMAN (ADM = NO)
UserPrivUser_Privileges20101026         User => OUTLN (ADM = NO)
UserPrivUser_Privileges20101026         User => MDDATA (ADM = NO)

PL/SQL procedure successfully completed.
SQL> spool off;

Example 1 : Running Execute immediate with an OUT parameter.

I wanted to output a count of tables to see if there is a data difference between Live/Test. I would have created a SQL script but it is hard to share with other developers so I found creating a procedure handy.

Create or replace procedure update_row_count as
l_count number;
l_sql_error_message varchar2(100) :=NULL;
SQL_STMT varchar2(200) :=NULL;
TYPE table_name_type IS VARRAY(7) OF VARCHAR2(30);
table_names table_name_type
:= table_name_type
(‘EMP’,
‘DEPT’,
‘SALARY’);
BEGIN
FOR i IN Table_Names.FIRST..Table_Names.LAST
LOOP
SQL_STMT := ‘SELECT COUNT(*) FROM SCOTT.’||table_names(i);
EXECUTE IMMEDIATE sql_stmt INTO l_count;
l_sql_error_message := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE(‘TABLE : ‘||upper(table_names(i)) || ‘ ‘|| l_count);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Others Error Message : ‘||l_sql_error_message);
END;
set serverout on size 10000;
/
exec update_row_count;

For an IN and OUT parameters you would do

EXECUTE IMMEDIATE ‘BEGIN SCOTT.’|| Table_Names(i)||'(:1,:2,:3);END;’

USING parameter1,OUT parameter2,OUT parameter3;

Example 2 : Running Execute immediate with number of IN parameters. In an example below I am sending IN parameters Emp_id , Dept_id, Dept_name,location and retrieving Salary & Emp_rec

Create or replace procedure update_row_count as
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := ‘PERSONNEL’;
location VARCHAR2(13) := ‘DALLAS’;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := ‘SELECT * FROM emp WHERE empno = :id’;
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := ‘BEGIN emp_pkg.raise_salary(:id, :amt); END;’;
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := ‘UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2’;
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE ‘DELETE FROM dept WHERE deptno = :num’
USING dept_id;
EXECUTE IMMEDIATE ‘ALTER SESSION SET SQL_TRACE TRUE’;
END;

Oracle 10g’s DBMS_SCHEDULER provides significant improvements over DBMS_JOB for scheduling jobs and tasks. Let’s take an overview of new functionalities that the Oracle Scheduler provides for DBAs.

There are three basic components to DBMS scheduler: Programs,Schedules & Jobs

Programs :	A program defines what Scheduler will execute. A program’s attributes include its name, its type (e.g. a PL/SQL procedure or anonymous block), and the action it is expected to perform.
Schedules :	A schedule defines when and at what frequency the Scheduler will execute a particular set of tasks.
Jobs :	A job assigns a specific task to a specific schedule. A job therefore tells the schedule which tasks – either one-time tasks created “on the fly,” or predefined programs – are to be run.

Job Classes: The Scheduler provides the capability to group together jobs that have similar resource demands into job classes.

Windows : Most businesses have periods of peak and off-peak. The scheduler provides concept of windows to assign resources to job classes.

Window Groups : The Scheduler also allows windows with similar scheduling properties – for example, normal business weekday off-peak time, weekends and holidays – to be collected within window groups for easier management of jobs and scheduled tasks.

Window Overlaps: It is possible to have windows overlap each other, and it does provide a simple conflict-resolution method to insure that the appropriate jobs do get the appropriate resources.

DBMS_JOB	DBMS_SCHEDULER
— Schedule an Analyze job at 10PM BEGIN DBMS_JOB.SUBMIT ( job => :jobno ,what => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”ORACLEDBA”);END;’ ,next_date => ’29/01/2009 22:00:00′ ,interval => ‘TRUNC(SYSDATE) + 1 + 22/24’); END;	BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => ‘REFRESH_STATS_ORACLEDBA_SCHEMA’ ,job_type => ‘PLSQL_BLOCK’ ,job_action => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”ORACLEDBA”);END;’ ,start_date => ”29/01/2009 10:00 PM’ ,repeat_interval => ‘FREQ=DAILY’ ,enabled => TRUE ,comments => ‘Refreshes the ORACLEDBA Schema every night at 10 PM’); END;

Let’s put the theory into practise. One of the common task as a DBA is setting up ANALYZE job for number of Oracle Database users.Let’s try and achieve it using the new DBMS_SCHEDULER API

————– Above task could be scheuled in a single API as below
BEGIN
DBMS_SCHEDULER.DROP_JOB ( job_name => ‘ANALYZE_USERS’ ,force => TRUE);
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘ANALYZE_USERS’
,job_type => ‘PLSQL_BLOCK’
,job_action => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”SALES”);DBMS_STATS.GATHER_SCHEMA_STATS(”Marketing”);DBMS_STATS.GATHER_SCHEMA_STATS(”HR”);END;’
,start_date => ’09/10/2009 22:00:00.000000′
,repeat_interval => ‘FREQ=DAILY’
,enabled => FALSE
,comments => ‘Refreshes the Schemas every night at 10 PM’
);
END;

Let’s try and use new feaures and strengths of 10g Scheduler

We need to create
1. A schedule object that controls when the various schema refreshes should run
2. A program object that handles calling appropriate procedure for Analyze refresh
3..A job object that invoke the program at the scheduled time

1. Creating Schedule Object: I want to schdule schema refresh on Sundays after 22:00 hours (off-peak time).

————– SQL for Creating a Schedule Object
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE( schedule_name => ‘RefreshSchemas’ ,force => TRUE );
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => ‘RefreshSchemas’
,start_date => ’25/10/2009 22:00:00′
,repeat_interval => ‘FREQ=WEEKLY’
,comments => ‘Weekly schema statistics refresh’ );
END;

2. Creating Program Objects : I will create a program object named REFRESHHRSCHEMA without any arguments that will handle refreshes of just the one HR schema. I have multiple schmas so I could use following SQL for each one of them.

————– SQL for Creating a Simple Program Object
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (program_name => ‘ReFreshSchema’
,program_type => ‘PLSQL_BLOCK’
,program_action => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”HR”);END;’
,number_of_arguments => 0
,enabled => TRUE
,comments => ‘Freshens statistics for all objects in HR schema only’
);
END;

3. Creating Job Objects: Finally, I will add a job object that assigns specific task within a specific schedule. I will need to create one job object for each schema for which statistics need to be refreshed.

————– SQL for Creating Job Using Existing Schedule and Program Objects
BEGIN
DBMS_SCHEDULER.DROP_JOB (
job_name => ‘HR_REFRESHSCHEMA’
,force => TRUE);

DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘HR_REFRESHSCHEMA’
,program_name => ‘REFRESHSCHEMA’
,schedule_name => ‘REFRESHSCHEMAS’
,enabled => FALSE
,comments => ‘Refreshes the HR Schema every week’
);
END;

Now we have new scheduler and added a Analyze job but how would we locate Success /Failure of Scheduler jobs?

————– Show Scheduler database objects
TTITLE ‘Scheduler Objects:’
COL owner FORMAT A08 HEADING ‘Owner’
COL object_type FORMAT A12 HEADING ‘Object|Type’
COL object_name FORMAT A20 HEADING ‘Name’
COL created FORMAT A20 HEADING ‘Created On’
COL status FORMAT A12 HEADING ‘Status’

SELECT
owner ,object_type ,object_name ,created ,status
FROM dba_objects
WHERE object_type IN (‘PROGRAM’, ‘JOB’, ‘JOB CLASS’, ‘SCHEDULE’, ‘WINDOW’)
ORDER BY object_type, OBJECT_name;

————– Show Schedule objects
TTITLE ‘Schedules:’
COL owner FORMAT A08 HEADING ‘Owner’
COL schedule_name FORMAT A20 HEADING ‘Schedule’
COL start_date FORMAT A20 HEADING ‘Starts|On’
COL end_date FORMAT A20 HEADING ‘Ends|On’
COL repeat_interval FORMAT A45 HEADING ‘Interval’

SELECT
owner ,schedule_name ,to_char(start_date, ‘mm/dd/yyyy hh24:mi:ss’) start_date ,to_char(end_date, ‘mm/dd/yyyy hh24:mi:ss’) end_date ,repeat_interval
FROM dba_scheduler_schedules;

————– Show Program objects : What program objects are available?
TTITLE ‘Programs:’
COL owner FORMAT A08 HEADING ‘Owner’
COL program_name FORMAT A20 HEADING ‘Program’
COL program_type FORMAT A16 HEADING ‘Type’
COL program_action FORMAT A32 HEADING ‘Action’

SELECT
owner
,program_name
,program_type
,program_action
FROM dba_scheduler_programs;

— What program’s arguments are attached?
TTITLE ‘Program Arguments:’
COL owner FORMAT A08 HEADING ‘Owner’
COL program_name FORMAT A20 HEADING ‘Program’
C
OL argument_name FORMAT A12 HEADING ‘Arg Name’
COL argument_position FORMAT 999 HEADING ‘Arg|Pos’
COL argument_type FORMAT A12 HEADING ‘Arg Type’
COL default_value FORMAT A12 HEADING ‘Default|Value’
COL out_argument FORMAT A06 HEADING ‘Out|Arg?’

SELECT owner ,program_name ,argument_name ,argument_position ,argument_type ,default_value ,out_argument
FROM dba_scheduler_program_args;

————– Show Job objects
COL owner FORMAT A08 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL program_name FORMAT A20 HEADING ‘Program|Name’
COL job_class FORMAT A24 HEADING ‘Job Class’
COL job_type FORMAT A12 HEADING ‘Job|Type’
COL job_action FORMAT A12 HEADING ‘Job|Action’

TTITLE ‘Jobs:’
SELECT
owner ,job_name ,state ,job_class ,job_type ,job_action
FROM dba_scheduler_jobs;

COL owner FORMAT A08 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL program_name FORMAT A20 HEADING ‘Program|Name’
COL schedule_name FORMAT A20 HEADING ‘Schedule|Name’
COL enabled FORMAT A08 HEADING ‘Enabled?’
COL state FORMAT A08 HEADING ‘State’
COL restartable FORMAT A08 HEADING ‘Restart|-able?’
COL start_date FORMAT A32 HEADING ‘Start|Date’

TTITLE ‘Job Components:’
SELECT
owner ,job_name ,program_name ,schedule_name ,enabled ,state ,restartable ,start_date
FROM dba_scheduler_jobs;

————– What are a Job’s arguments?
COL job_name FORMAT A20 HEADING ‘Job’
COL argument_name FORMAT A12 HEADING ‘Arg Name’
COL argument_position FORMAT 999 HEADING ‘Arg|Pos’
COL value FORMAT A32 HEADING ‘Argument Value’

TTITLE ‘Job Arguments:’
SELECT job_name ,argument_name ,argument_position ,value
FROM dba_scheduler_job_args;

————– Show Scheduled Tasks History: Show a high-level view of scheduled task execution history
COL log_id FORMAT 9999 HEADING ‘Log#’
COL log_date FORMAT A32 HEADING ‘Log Date’
COL owner FORMAT A06 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL status FORMAT A10 HEADING ‘Status’

TTITLE ‘Scheduled Tasks History:’
SELECT
log_id ,log_date ,owner ,job_name ,status
FROM dba_scheduler_job_log;

————– What scheduled tasks failed during execution, and Error details
COL log_id FORMAT 9999 HEADING ‘Log#’
COL log_date FORMAT A32 HEADING ‘Log Date’
COL owner FORMAT A06 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL status FORMAT A10 HEADING ‘Status’
COL actual_start_date FORMAT A32 HEADING ‘Actual|Start|Date’
COL error# FORMAT 999999 HEADING ‘Error|Nbr’

TTITLE ‘Scheduled Tasks That Failed:’
SELECT
log_id ,log_date ,owner ,job_name ,status ,actual_start_date ,error#
FROM dba_scheduler_job_run_details
WHERE status <> ‘SUCCEEDED’
ORDER BY actual_start_date;

————– Which jobs are running right now ?
COL owner FORMAT A06 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL session_id FORMAT 999999 HEADING ‘Session’
COL running_instance FORMAT A10 HEADING ‘Running|Instance’
COL elapsed_time FORMAT A12 HEADING ‘Elapsed|Time’

TTITLE ‘Scheduled Tasks Running Right Now:’
SELECT
owner ,job_name ,session_id ,running_instance ,elapsed_time
FROM dba_scheduler_running_jobs;

Log4plsql is an open source tool that uses PL/SQL framework to log into PL/SQL code package, function, web application, procedure, trigger and various oracle tools such as reports, forms etc. Before we proceed with Log4plsql for PL/SQL Logging, it is important to understand the concept behind the importance of logging and logs in the development environment.
Logging is very important part of development lifecycle for any process or application. These logs are important for tracing functional events, data and operations. Logs are also used for code debugging and in testing or maintenance phase to understand unforeseen events such as system crash, quit or launch. It is considered as a very time-consuming tedious job as all the logs during the development strategy have to be considered which utilizes vital computer memory or resources.

What Is LOG4PLSQL ?

LOG4PLSQL is a PL/SQL framework for logging in all PL/SQL code
·        Package,
·        Procedure,
·        Function,
·        Trigger,
·        PL/SQL Web application.
·        Oracle Tools (form, report, …)
Purpose:
·        Easy implementation and use
·        Possibility to log apart from transaction
·        Possibility to adapt the level of logging depending on user requirements
·        Ability to use all LOG4J feature.
·        Provide a complete set of  PL/SQL development tools (debug variable, SQLERRM, assert, call stack, … ).

Log destination:
·        table TLOG
·        alert.log file
·        trace file
·        standard output
·        session information (view V$SESSION)
·        advanced queue read by Log4JBackgroundProcess. :
o       Async,
o       JDBC,
o       JMS,
o       NTEventLog,
o       SMTP,
o       Socket,
o       SocketHub,
o       Syslog,
o       Telnet,
o       Writer

Log4plsql was born from the log4J spirit (and use it).

Architecture

 

Package PLOG  It is the main package of the log functionality. Your PL/SQL applications call its public functions to create a log event (info, debug …)

Package PLOGPARAM Package containing default values for the logging context

Package PLOG_INTERFACE  Dispatcher package built dynamically during the installation of the schema (for compilation raison). Its role is to call the dedicated packages for output according to the configuration and installation choices.

Package SL_PLOG_OUT_ALERT Optional package writing log information into the Oracle alert file alert.log. The function DBMS_SYSTEM. Ksdwrt() function is used.

Package SL_PLOG_OUT_TRACE Optional package writing log information into the Oracle trace file ora.trc. The function DBMS_SYSTEM. Ksdwrt() function is used.

Package SL_PLOG_OUT_AQ Optional package writing log information in a multi-consumer advanced queue consumed by the Java background process. The messages are enqueued using the package DBMS_AQ.

Package SL_PLOG_OUT_DBMS_OUTPUT Optional package writing log information into the standard output. The package DBMS_OUTPUT is used.

Package SL_PLOG_OUT_TLOG Package writing the log information in the table SL_TLOG. The package is part of the basis installation.

 

Installation of Log4plsql for PL/SQL Logging is very easy. Just follow these simple steps:

• Download Log4Sql from sourceforge.
• Unzip the zip file at the location where you want Log4plsql for PL/SQL Logging to be installed.
• Run the executable install file in cmd directory.
• Basic installation of Log4plsql for PL/SQL Logging is complete and now you need to proceed only if you want to functions or features.

An advanced user has more requirements or desires from Log4plsql for PL/SQL Logging as compared to normal end users. Some of these advanced requirements are:

• He or she needs to test logging level for which they code it before logging with any of the IsEnabled functions such as isInfoEnabled, isErrorEnabled, isDebugEnabled, isWarnEnabled and isFatalEnabled.
• An advanced user may want to change the default log parameters for a certain application.
• Need to create log section in hierarchical log node. You can also specify your own hierarchical log node by defining begin, end and get section.