APEX OOW 2013 Meetup

Each year the Oracle APEX community gets together at Oracle Open World (OOW) to have a few beers and catch up. This is a great informal event and its your chance to network with a lot of people in the APEX community and meet some of the gurus and members of the core APEX development team.

Normally Dimitri Gielis organizes the meetup, however he is expecting his third child and understandably he will not be coming to OOW. As such I've been handed the torch to help organize the event this year. To be honest by "I" I really mean Lauren Prezby at ODTUG has done the planning and found our venue for this year :-)

We'll be having the annual APEX OOW Meetup at Johnny Foleys on Tuesday at 7:30 onwards.

243 O'Farrell Street
San Francisco, CA 94102

ODTUG will also be giving away some cool APEX swag!

Looking forward to seeing everyone there!

Martin

Logger 2.1.0 - Released!

I'm pleased to announce that Logger 2.1.0 has officially been released!

The major changes have been captured in the blog articles that I have posted this week:

• Text Length > 4000 Characters
• Documentation Overhaul
• Demo Scripts
• ok_to_log
• ins_logger_logs
• 10g Sequence Fixed

You can download a copy of Logger 2.1.0 from the releases page. As there have been some significant changes I highly recommend that you review the change log (especially if you have code that manually inserts into LOGGER_LOGS).

As always, your input and feedback is welcome. Feel free to submit any enhancement requests or bugs on the project's issues page. 

Logger 2.1.0 - 10g Sequence Fixed

In preparation for the release of Logger 2.1.0 I've decided to write a few posts highlighting some of the new features.

Apparently not too many people who use Logger are still running Oracle 11gR1 or lower since I was only notified of this issue now.  Patrick Jolliffe was kind enough to point out that there was an issue when installing Logger in a 10g. It turns out that  ":new.id := logger_logs_seq.nextval;" doesn't work in 10g. It only works in 11gR2 and above. Clearly it's been a while since I've used 10g, or better yet 11gR1.

The ability to reference sequences nextval in PL/SQL is mentioned in the 11gR2 New Features guide.

Logger 2.1.0 address this issue by only supporting the old "select into..." technique for various reasons. At first I was skeptical about performance impacts of having the context switch, however it appears that behind the scenes if called from PL/SQL it does an internal select into to obtain the value. Connor McDonald has some good slides about this here (see page 59 onwards).

Thanks to Dan McGhan and Scott Wesley for helping sort this issue out.

Logger 2.1.0 - ins_logger_logs

In preparation for the release of Logger 2.1.0 I've decided to write a few posts highlighting some of the new features.

Prior to 2.1.0, Logger used a trigger when inserting into the LOGGER_LOGS table. Though their was never a compelling reason to remove the trigger a bug request and an unrelated enhancement request prompted me to reexamine the need for it.

Starting with 2.1.0 there is no trigger on the primary Logger table: LOGGER_LOGS. Instead all insert statements are handled by a single procedure:  ins_logger_logs. I'm sure this will make Tom Kyte happy as there's one less trigger in the world now.

On the performance perspective, initial testing has shown a ~15% performance increase when using the standard logging procedures. Though this may not seem like a lot, in large systems where Logger is called often it can start to add up.

As you can imagine, if you have a direct insert into LOGGER_LOGS and leveraged some of the features that the trigger had (i.e. obtaining the sequence for the ID column) then your application will have a run time error. I was very hesitant about removing the trigger for this sole reason but am able to justify it by the fact that directly inserting into the table has never been supported by Logger. Regardless, if you do have insert statements into the table you can simply replace them with calls to the ins_logger_logs procedure.

ins_logger_logs should not be used often and only makes sense in very specific cases. A few things to note about this procedure:
- It is an autonomous transaction procedure. This means that an explicit commit occurs at the end of the procedure. Since it is an autonomous transaction it will not affect (i.e. not commit) your current session.
- It will always insert the value into the LOGGER_LOGS table. No check is performed to factor in the current logging level. For this reason you're better off using the standard Logger procedures.

On a side note, you may be wondering why you would manually do a direct insert into the LOGGER_LOGS table? In rare occasions you may need the ID that was generated from a log statement for other purposes. None of primary log procedures returns the ID. One situation that I've seen in the past is to log all the APEX info on the APEX error handling page. On that page the user would see an error code that developers can reference. That unique code is actually the ID from Logger.

Here is an example of the new procedure:

set serveroutput on

declare
  l_id logger_logs.id%type;
begin
  -- Note: Commented out parameters not used for this demo (but still accessible via API)
  logger.ins_logger_logs(
    p_logger_level => logger.g_debug,
    p_text => 'Custom Insert',
    p_scope => 'demo.logger.custom_insert',
--    p_call_stack => ''
    p_unit_name => 'Dynamic PL/SQL',
--    p_line_no => ,
--    p_extra => ,
    po_id => l_id
  );
  
  dbms_output.put_line('ID: ' || l_id);
end;
/

ID: 2930650

Logger 2.1.0 - ok_to_log

In preparation for the release of Logger 2.1.0 I've decided to write a few posts highlighting some of the new features.

A while ago, someone asked me to add a feature into logger to determine if a log statement would actually be logged. I fought this off like the plague as I wanted to avoid the following situation:

...
if can_log then
  logger.log('some message');
end if;
...

if can_log then
  logger.log('another message');
end if;
...

Unfortunately I've seen this type of coding style many times and it makes code difficult to read and maintain. This also ruins one of the key goals behind Logger which is to make it easy and quick for developers to use.

Reluctantly, I finally caved in. As a result there's a new function called ok_to_log. ok_to_log tells you if what you're about to log will actually get logged. It is not meant to be used like the example above. I can't emphasize this enough.

The main use case for ok_to_log is for situations where you only want to do something for logging purposes that will slow down the system. A good example is when you want to log the values of an array. Since it will take time to loop through the array there's no point in doing it if Logger won't actually log anything. The following example highlights this:

select *
declare
  type typ_array is table of number index by pls_integer;
  l_array typ_array;
begin
  -- Load test data
  for x in 1..100 loop
    l_array(x) := x;
  end loop;

  -- Only log if logging is enabled
  if logger.ok_to_log('DEBUG') then
    for x in 1..l_array.count loop
      logger.log(l_array(x));
    end loop;
  end if;
end;
/ 

If the logging level is set to DEBUG or higher (as it would be in development environments) then logger will loop through the array and log the values. If the logging level is set to ERROR (as it normally would be in production instances) it won't loop over the array since it now knows ahead of time that Logger won't log the values.

Note that the level passed into ok_to_log should correspond to the logging statements used inside the block of code that you want to log. If they don't, it really won't make sense and could result in unnecessary performance hits.

To summarize the ok_to_log function:
- I was reluctant to add it in for obvious reasons, please don't make me regret it.
- Only use in situations where you need to perform an expense block of code only for logging purposes.
- Make sure the level passed in ok_to_log matches the log statements using inside the associated block of code.

Logger 2.1.0 - Demo Scripts

In preparation for the release of Logger 2.1.0 I've decided to write a few posts highlighting some of the new features.

The new Logger documentation contains a lot of sample code. Though this is good for documentation purposes I found that I still needed to build a set of demos for various presentations and demonstrations about Logger. Instead of recreating demos each time I've started to keep them in a demo folder (included in each release). This can also be useful for individuals looking to demo Logger at an organization for the first time.

Though not complete, expect the list of demos to expand over time.  If you have a demo script you want added, please feel free to send it to me or make a pull request.

Logger 2.1.0 - Documentation Overhaul

In preparation for the release of Logger 2.1.0 I've decided to write a few posts highlighting some of the new features.

As Logger expands, so does its documentation. We have reached a point that it was no longer feasible to continue with a one page documentation structure. Besides being hard to find a specific bit of information I also found that it was hard to get a quick overview of all the functions that Logger has.

The documentation for Logger has been completely revamped. It's broken up into two main sections: the initial readme file (found on the main project page) and a wiki. The readme file contains a brief overview of logger and links to the wiki. The wiki has been broken up into four sub sections: Installation, Logger API, Change Log, and Best Practices. This is a start to make Logger's documentation similar to Oracle's documentation format which should make things consistent for developers.

If you've already used Logger for a while, take a look at the Logger API page. I think you'll find some functions that you didn't know existed.

The wiki documents are stored in a separate repository on GitHub. This means that they aren't synced with a release is tagged. To get around this issue, each build now contains a folder called "wiki". In it, you will find all the wiki files (in markdown format) generated at the time of the build.

On a side note, the documentation restructuring took a lot of time. I didn't have enough time to document all the functions and they will all be updated in the near future once I have more time. Of course you're encouraged to fork the project and update the documentation if you want to help out.

Logger 2.1.0 - Text Length > 4000 Characters

In preparation for the release of Logger 2.1.0 I've decided to write a few posts highlighting some of the new features.

I must thank Juergen Schuster for suggestion the following change to Logger. He sent me an email with this request and though it took a while to get in, I'm glad to finally see it in this release of Logger.

In the main Logger procedures the first parameter, p_text, is a varchar2 data type. This means that in PL/SQL you can pass in strings up to 32767 characters however the max table size (pre 12c) for a varchar2 is 4000 characters. As you can imagine it caused some people some unexpected runtime errors when they passed in large blocks of text. The only way to get around this was either check the size of text before logging it (if you were unsure of it's final size) or store it in the EXTRA (clob) column.

Starting in 2.1.0, Logger will gracefully handle text entries larger than 4000 characters. If the text is larger than 4000 characters it will automatically be appended to the EXTRA column and a message, "*** Content moved to EXTRA column ***" will be displayed in the TEXT column. I hope this resolves some unforeseen runtime errors that may have occurred in the past.

With 12c finally released (and its ability to have varchar2 columns of 32767 length) this functionality is bound to change / become obsolete. A new issue has been created to address this in a future release of Logger.