User Guide

Oracle PL/SQL Suite - Logger User Guide
Author: Elina Burdin
Version: 1.0.1 (06/02/2016 07:30)

Table of Contents
1. Introduction
2. Installation
2.1. Download
2.2. User creation
2.2.1. Dedicated user
2.2.2. Existing user
2.3. Schema creation
2.4. Post Schema creation
2.5. Logger removal
3. Performance
4. About The Author

1. Introduction
Many modern PL/SQL applications need logging functionality to be able to track precise program
behavior. As applications become more complex, the need for fine-grained analysis tool becomes
indispensible. As developers deploy their applications across multiple environments, ability to
control logging without constantly changing the underlying PL/SQL code becomes a mandatory
requirement. Almost all big organizations have a requirement that before development team can modify
code in Production, they need to create change request ticket and get it approved. This is a time-
consuming process that can easily take between 2 to 5 business days depending on the number of
approvals one has to obtain.
Throughout the years I have found that many existing PL/SQL logging frameworks were too slow,
too cumbersome to use, lacked necessary documentation or were not capable of logging on a fine-
grained scale. This led me to the creation of this project. The initial version of the code was
created in 2002 for Oracle 8.1.5. As new versions of Oracle database became available, new features
were added to Logger to make it faster and more reliable.
Often debugging and logging are compared between each other. Debugging is a manual process of
walking through the code, checking application logic and watching values of variables. Even though
debuggers provide deeper code analysis than loggers, they are not always applicable. Logging, on the
other hand, gives you the ability to precisely see the desired output at crucial points in your code.
The current version of Logger allows you to set the level globally, for specific package or a
module within a package. Logging is not the main focus of any application. It becomes relevant as
soon as application does not behave as expected. That's the reason Logger is written with simplicty
in mind.
Logging does have certain drawbacks. If used excessively, it can grind application to a halt. To
mitigate these risks, Logger allows you to set the level of logging on a fine-grained level.

2. Installation
2.1. Download:
You can download compressed installation at the URL below:
logger.zip
Unzip the file logger.zip

2.2. User creation:
     Each of the scripts provides a "define" variable to set
     username, password and tablespace that you need for your
     environment. Logger can be installed into a dedicated or an
     existing oracle user (schema). These two options are
     explained below.

    2.2.1. Dedicated user
           This script creates a new user and grants the necessary privileges to him.
           Connect to SQL*Plus as privileged used (sysdba or DBA) and execute the
           following script:
             oracle_util_suite\logger\user_dedicated.sql

    2.2.2. Existing user
           This script assumes that the user already exists. It grants the necessary
           privileges to him. Connect to SQL*Plus as privileged used (sysdba or DBA)
           and execute the following script:
             oracle_util_suite\logger\user_existing.sql

2.3. Schema creation:
     Connect to SQL*Plus as user which you want to use for Logger objects and execute
     the following script:
       oracle_util_suite\logger\create_schema.sql

     This script performs the following steps:
     - creates the necessary schema objects
     - populates the necessary *map (static) tables
     - creates logger package
     - creates logger_tester package
     - sets up the environment for logger to run; you may have more than one environment
       where you need to use logger (Development, UAT, QA, Production). The environment
       script is called set_env.sql. You can take this script as a base script, append
       environment name to it and create several scripts for your specific environment:
       set_env_dev.sql, set_env_prod.sql, etc for each of your environments. This allows
       you to customize your environment as required:
            - set general log level
            - set log level for specific set of modules; for example, you can set
              higher log level for Dev or UAT environment than for Prod

2.4. Post-Schema setup:
    If you would like to zetup
    As the owner of logger schema, grant execute permission to the user that is going to use logger:
        grant execute on logger to <username>
    If logger was installed in a dedicated user's schema, create a synonym for logger in the schema
    of the user that is going to use it:
        create synonym logger for <username of owner of logger schema>.logger;

  2.5. Logger removal:
    To remove logger:
        run drop_schema.sql script
    It drops all schema objects related to logger.

3. Performance
Logging does have certain drawbacks. If used excessively, it can grind application to a halt.
To mitigate these risks, Logger allows you to set the level of logging on a fine-grained level.
Several performance tests were performed to show the penalty of using
logger in various situations. oracle_util_suite\sample\perf-results.txt
shows the results

4. About The Author:
Elina Burdin has worked as Developer and DBA with various technologies for over 15 years. She has
created several projects from the ground up and led them to a successful deployment.