Restrict Logger's Access to Your Data

Some DBAs and organizations may have issues with installing Logger in a schema with their existing data. The reason being that they don't want 3rd party code sitting alongside their data. The good news is that this concern can easily be resolved. Logger doesn't need to see your data/schema. Rather, your schema needs to see Logger.

Update 1: I've created a new issue for this so that these scripts will be available as part of Logger 3.1.0 onwards.

Update 2: This is now in Logger 3.1.0. Documentation here.

Here's how to setup Logger so that it can't see your data:

  1. Create a new user (for this example logger_user) with the appropriate permissions. You can review this in the Installation documentation. There's already a script to create a user in the install folder.

  2. Install Logger in the newly created schema.

  3. Grant execute and select permissions from logger_user to your schema:

-- Run as logger_user
grant execute on logger to my_user;
grant select on logger_logs to my_user;
grant select on logger_logs_apex_items to my_user;
grant select on logger_logs_5_min to my_user;
-- Add any additional grants to other Logger views
  1. In your schema, create the appropriate synonyms:
-- Run as my_user
create or replace synonym logger for logger_user.logger;
create or replace synonym logger_logs for logger_user.logger_logs;
create or replace synonym logger_logs_apex_items for logger_user.logger_logs_apex_items;
create or replace synonym logger_logs_5_min for logger_user.logger_logs_5_min;

After doing these steps your schema can see Logger's tables and views, and run the logger package. Assuming you don't grant public access to your tables/packages then Logger can't see your data.

OOS Utilities Package

Just like any other programming language they're some common tools/features that are not offered natively within the Oracle PL/SQL APIs. As such, most developers start compiling a list of these common tools and create their own utility packages or code snippets. 

We're looking to create a master utility package(s), currently called OOS Utils. Before we can do that we need your help! If you have a common snippet of code, procedure, or function that you think will be useful to the entire community, please go to: and submit a new issue with your suggestion. If you just have an idea but don't have the source code, please submit it anyways and we'll try to add it in.

Hint: If you want to format your code nicely when submitting a recommendation use the following template. 


Note that those are not apostrophes, rather they are backticks (key in the top left of your keyboard).

Once we have enough entries, we'll look at grouping all the suggestions into logical packages and start the development process.

We look forward to your recommendations and can't wait to launch this when it's ready! As always if you want to stay up to date with everything that's going on, please subscribe to our email list.

Speed Up Development with Logger: Part 2

Last week I wrote about how to speed up development with Logger using text expanders. Today's post will show you how to leverage Logger's method template to quickly create procedures and functions.

I'm a big fan of SQL Developer for most of my Oracle development, however I do leverage other text editors for some functionality when developing. Atom is an an open source text editor with builds for Windows, Mac, and Linux. If you've used Sublime before, it's very similar. They're a few features of Atom that I regularly use such as column editing (modifying multiple columns of text at the same time) and global text replace (in real time, not via a find & replace dialog). 

When creating a new function or procedure I usually use the Logger Template and modify its TODO items. The video below shows how I quickly start developing a new method and leverage all the features of Atom.

A few things to note about the video:

  • I used some text expanders to quickly insert my name (ome) and the date (otoday).
  • I used a lot of shortcuts in Atom which are quickly shown on the screen. A complete list of shortcuts are available here.


Speed Up Development with Logger

One of the first reactions we get when introducing Logger to developers and managers is that it will slow developers down by having to write additional logger.log statements. This couldn't be further from the truth.

Instead of manually typing out each log statement you can easily use a text expander. Text expanders allow you to register shortcuts which then auto expand when you're developing.

For example, when I type ologl (the prefixing "o" stands for Oracle shortcuts in my setup) in any editor it expands to logger.log('', l_scope);. The following video demonstrates some shortcuts that I use (full details below):

Popular ones I use are:


logger.log_error('', l_scope, null, l_params);


logger.log(logger.sprintf('TODO: %s', TODO), l_scope);


select *
from logger_logs_5_min
where 1=1
  and scope like '%%'
-- and client_identifier like 'MD%'
--  and id > TODO
order by 1 desc;

oproc - This will expand to a procedure template as defined in the Best Practices document.

Of course you can use the same technique for other code snippets that you frequently use.

They're lots of great apps out there for text expanders. Some that I recommend are:

  • Mac: aText
  • Windows: PraseExpress
  • SQL Developer: Jeff Smith as a great blog post about using the built in text expander in SQL Developer.

Feel free to recommend other text expander apps in the comments.

For more development tips please read the entire series: