Beginner Series, Logger, Oracle

PL/SQL Instrumentation: Installing Logger

If you are writing PL/SQL, the open source tool Logger, started by Tyler Muth and now owned and maintained by Martin D’Souza is instrumental in error identification and debugging. While I am sure that there are other resources that can provide the same information, this post will show you how to get Logger installed and confirm that Logger is actually working.

As usual, you can check out the Lab here to get an idea of what I’m working on. Now let’s get to it:

First, you will need access to at least 3 database users:

  • SYS
  • Schema for Logger
  • Application Owner(s)

Fortunately, the Logger schema will be built during the install process and your application owner should probably exist already.

Download and unzip the latest release of Logger (3.1.1 at the time of this post) from this GitHub link.

Logger GitHub Screen Shot

Once unzipped, you should have a directory structure similar to this:

Logger dir Screen Shot

 

Now we can fire up SQL Developer and complete the installation.

You will need to connect to the database as the SYS user to execute this initial step:

Browse to the directory where you unzipped logger and execute the create_user.sql script. For this exercise, we will create a new user, accept all tablespace defaults, and provide a password for this users. If you environment requires special tablespaces for logging, please consult with your DBA.

Create Logger User

Logger Create User Screen ShotLogger Default TS Screen ShotLogger Create User Temp TS Screen ShotLogger Create User PW Screen Shot

As the script runs, SQL Developer will display the following message.
Logger Create User Screen Shot

If you don’t receive any errors, you are ready to move on the next step.

Logger Install

You will need to log into the database as the user you just created in the step above before executing the logger_install.sql script.

There is a lot going on in this script:

  • Granting Privs
  • Creating Tables and Sequences
  • Creating Indexes
  • Creating Functions and Packages

so take some time to review what is happening. There are few errors that popped up, but none appear to impact the Logger nor do they stop the script from executing.

If you get the following from the SQL Developer Script Output tab, you are ready for step 3

Logger install Screen Shot

Grant Privs to App Owner

This step is where the Application Owner is granted specific privileges to the objects owned by the Logger Schema. Note, that “Application Owner” in this context is just the user that is developing the PL/SQL code.

Log into the database as the Logger Schema owner in this case DM_LOG and execute the grant_logger_to_user.sql script. This script can be found in the scripts subdirectory:

Logger subdir Screen Shot

This one executed relatively quick as its only input is the Application Owner. In my example, I created a DBRYANT user for this:

Logger Grants Screen Shot

Create Synonyms

Finally, we need to run the create_logger_synonyms.sql script in the Application Owner (DBRYANT) schema. The input here is the Logger Schema (DM_LOG) owner.

Logger Synonyms Screen Shot

That’s is! Now let’s test our installation and see if we can get Logger to start capturing some information.

Check to see if Logger was successfully installed:

select * from logger_logs order by id;

This shows that we have indeed installed Logger version 3.1.1

Test logging:
exec logger.log(‘hello world’);
select * from logger_logs order by id;

As we can see in the output, passing the string ‘hello world’ to the logger.log procedure resulted in row inserted into the logger_logs table.

Try a few on your own and see what happens:
exec logger.log_info();
exec logger.log_warning();
exec logger.log_error();

describe logger_logs

Logger_Logs Table desc

Hope this get you started on the road to better instrumentation of your PL/SQL code.

Happy Coding


Also published on Medium.