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.

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

 

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

As the script runs, SQL Developer will display the following message.

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

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:

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

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.

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

The calming effect of the preparation has allowed my life to gain quality, which even has an impact on the professional success of my husband, and since the buy ambien no prescription, we are assured of a relaxed family atmosphere. Of course, I have been in a dependency relationship with the drug since then, and it has been ten years.

Check to see if Logger was successfully installed:

select * from logger_logs order by id;

1 8 Log level set to DEBUG for client_id: <global>, include_call_stack=TRUE by Host: xxxxxxxxxxxx, IP: x.x.x.x, 
    TERMINAL: unknown, OS_USER: me, CURRENT_USER: DM_LOG, SESSION_USER: DM_LOG
2 1 Logger version 3.1.1 installed.

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;

3 16 hello world 27-DEC-16 02.42.42.623996000 PM SQL Developer DBRYANT " object line object
handle number name
0xcee50e10 1 anonymous block
" 23

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

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

Happy Coding