Unique id for each execution

[at-ng]

It's nice to have an identifier for each separate execution (job execution, web service call etc). When a lot of things happens at the same time it can get a bit annoying to follow along in the log. You can filter on SID but then you have to also add an interval if you also want to isolate a specific execution.

I'm already using this by just having a package that uses "PRAGMA SERIALLY_REUSABLE" to be able to get an unique id. Then I have a function that sets it if it's not already set that I can call before I start logging. Currently I do this by setting client_info since I'm not using that for anything else but ideally you would want a separate column for this instead and move the call into logger so that you don't have to manually call this set id function.

Example:

CREATE OR REPLACE PACKAGE BODY exec_guid_pkg
IS
  PRAGMA SERIALLY_REUSABLE;
  g_exec_guid   VARCHAR2 (32 BYTE);

  FUNCTION get_exec_guid
    RETURN VARCHAR2
  IS
  BEGIN
    IF g_exec_guid IS NULL
    THEN
      g_exec_guid := RAWTOHEX (SYS_GUID ());
    END IF;

    RETURN g_exec_guid;
  END;
END exec_guid_pkg;

There is a disadvantage with this approach though, SERIALLY_REUSABLE excludes SQL so something else might be needed if you want it to work for that as well.