LILAM

LILAM API Reference

Version: 2.0

📖 Content - [Quick Start](#quick-start) - [In-Session Mode](#in-session-mode) - [Decoupled Server Mode](#decoupled-server-mode) - [Core Concepts](#core-concepts) - [Process Progress vs. Metrics](#process-progress-vs-metrics) - [Events vs. Traces](#events-vs-traces) - [Functions and Procedures](#functions-and-procedures) - [Session Handling](#session-handling) - [Process Control](#process-control) - [Logging](#logging) - [Metrics](#metrics) - [Server Control](#server-control) - [Dispatcher Mode](#dispatcher-mode) - [Appendix](#appendix) - [Parameter Requirements](#parameter-requirements-1) - [Log Levels](#log-levels) - [Record Type t_session_init](#record-type-t_session_init) - [Record Type t_process_rec](#record-type-t_process_rec) - [Procedure IS_ALIVE](#procedure-is_alive) - [JSON API Interface](#json-api-interface)

[!TIP] This document is the LILAM API reference. If you are new to LILAM, start with architecture and concepts.md for the underlying concepts. The examples in the demo folder show how the LILAM API can be integrated into applications.

Quick Start

In-Session Mode

Use in-session mode when logging and monitoring should be handled directly within the current database session.

The following example initializes LILAM, writes a log entry and records two occurrences of an event. With the default table prefix, LILAM uses these tables:

DECLARE
  l_processId   NUMBER;
  l_sessionInit lilam.t_session_init;
BEGIN
  -- 1. Configure the session
  l_sessionInit.processName := 'MY_FIRST_SYNC';
  l_sessionInit.logLevel    := lilam.logLevelInfo; -- default: logLevelMonitor

  -- 2. Initialize LILAM
  l_processId := lilam.new_session(
    p_session_init => l_sessionInit
  );

  -- 3. Write a log entry
  lilam.info(
    p_processId => l_processId,
    p_logText   => 'LILAM is up and running!'
  );

  -- 4. Record an event twice
  lilam.mark_event(
    p_processId  => l_processId,
    p_actionName => 'DATA_LOAD'
  );

  dbms_session.sleep(1);

  lilam.mark_event(
    p_processId  => l_processId,
    p_actionName => 'DATA_LOAD'
  );

  -- 5. Finalize the session
  lilam.close_session(l_processId);
END;
/

[!NOTE] LILAM uses autonomous transactions. Logging and monitoring data is therefore persisted independently of the calling application’s main transaction, even if that transaction is rolled back.

Decoupled Server Mode

Use decoupled mode when clients should send their logging and monitoring data to a LILAM server.

A server is identified by its pipe name and can optionally belong to a group. A client can either connect to any available server or restrict the server selection to a specific group.

Step 1: Start the Server

Start the server in a separate database session. START_SERVER blocks this session as long as the server is running. In production, CREATE_SERVER starts a server via DBMS_SCHEDULER.

BEGIN
  lilam.start_server(
    p_pipeName  => 'MY_FIRST_LILAM_SERVER',
    p_groupName => NULL,
    p_password  => 'SECURE PASSWORD'
  );
END;
/

Step 2: Run the Client

DECLARE
  l_processId NUMBER;
BEGIN
  -- Connect to an available server
  l_processId := lilam.server_new_session(
    p_processName => 'DECOUPLED_SYNC',
    p_logLevel    => lilam.logLevelInfo
  );

  lilam.info(
    p_processId => l_processId,
    p_logText   => 'LILAM initialized'
  );

  -- Discrete event
  lilam.mark_event(
    p_processId  => l_processId,
    p_actionName => 'DATA_LOAD'
  );

  -- Timed transaction
  lilam.trace_start(
    p_processId  => l_processId,
    p_actionName => 'NEXT_STATION'
  );

  dbms_session.sleep(1);

  lilam.trace_stop(
    p_processId  => l_processId,
    p_actionName => 'NEXT_STATION'
  );

  lilam.proc_step_done(p_processId => l_processId);

  -- Flush remaining buffered data
  lilam.close_session(l_processId);
END;
/

Step 3: Shut Down the Server

A client first has to connect to the server. The server pipe can then be determined with GET_SERVER_PIPE and passed to SERVER_SHUTDOWN.

DECLARE
  l_processId NUMBER;
  l_serverPipe VARCHAR2(100);
BEGIN
  l_processId := lilam.server_new_session(
    p_processName => 'SHUT DOWN SERVER',
    p_logLevel    => lilam.logLevelInfo
  );

  l_serverPipe := lilam.get_server_pipe(l_processId);

  lilam.server_shutdown(
    l_processId,
    l_serverPipe,
    'SECURE PASSWORD'
  );

  lilam.close_session(l_processId);
END;
/

Core Concepts

Process Progress vs. Metrics

[!IMPORTANT] Process progress and metrics are independent concepts.

Use SET_PROC_STEPS_TODO, PROC_STEP_DONE and SET_PROC_STEPS_DONE to represent the overall progress of a process.

Use MARK_EVENT, TRACE_START and TRACE_STOP to record measurable activities within that process.

The number of process steps therefore does not have to match the number of metric events or traces.

Events vs. Traces

A simple rule of thumb:

A metric is identified by the combination of p_actionName and p_contextName.

If a trace is started with a context, it must be stopped with the same combination of action and context.

Functions and Procedures

Parameter Requirements

The following markers are used for parameters:

Session Handling

Session handling controls the life cycle of a LILAM process.

API Purpose
NEW_SESSION Starts a LILAM process in in-session mode
SERVER_NEW_SESSION Starts a process connected to a LILAM server
CLOSE_SESSION Ends a process and writes buffered data
FLUSH Writes all buffered data of the database session immediately; the processes stay open

Function NEW_SESSION / SERVER_NEW_SESSION

Both functions start a LILAM process and return its process ID. This ID is required for all subsequent API calls.

Each parameter always has the same position. All parameters except p_processName have a default and can therefore be omitted or passed by name.

FUNCTION NEW_SESSION(
  p_processName   VARCHAR2,
  p_logLevel      PLS_INTEGER DEFAULT logLevelMonitor,
  p_procStepsToDo PLS_INTEGER DEFAULT NULL,
  p_daysToKeep    PLS_INTEGER DEFAULT NULL,
  p_tabNameMaster VARCHAR2    DEFAULT 'LILAM',
  p_baselineScope VARCHAR2    DEFAULT NULL,
  p_groupName     VARCHAR2    DEFAULT NULL,
  p_syncLevel     PLS_INTEGER DEFAULT logLevelError
) RETURN NUMBER

FUNCTION NEW_SESSION(
  p_session_init t_session_init
) RETURN NUMBER

FUNCTION SERVER_NEW_SESSION(
  p_processName   VARCHAR2,
  p_groupName     VARCHAR2    DEFAULT NULL,
  p_logLevel      PLS_INTEGER DEFAULT logLevelMonitor,
  p_procStepsToDo PLS_INTEGER DEFAULT NULL,
  p_daysToKeep    PLS_INTEGER DEFAULT NULL,
  p_tabNameMaster VARCHAR2    DEFAULT 'LILAM',
  p_baselineScope VARCHAR2    DEFAULT NULL,
  p_syncLevel     PLS_INTEGER DEFAULT logLevelError
) RETURN NUMBER

FUNCTION SERVER_NEW_SESSION_JSON(
  p_jsonObject VARCHAR2
) RETURN NUMBER

SERVER_NEW_SESSION_JSON accepts the same parameters as a JSON object (keys see table).

Parameters

Parameter JSON Default Description
p_processName process_name – Name identifying the process
p_groupName group_name NULL SERVER_NEW_SESSION: restricts the server selection to the given group; NULL = any available server. NEW_SESSION: the process uses the active rule set of this group from LILAM_RULES; NULL = no rules
p_logLevel log_level logLevelMonitor Level of detail, see Log Levels
p_procStepsToDo steps_todo NULL Planned number of process steps
p_daysToKeep days_to_keep NULL NULL = no automatic cleanup. Otherwise, completed processes of the same name older than the given number of days are deleted at startup, including their logs and metrics (except processes with procImmortal = 1)
p_tabNameMaster tabname_master 'LILAM' Prefix of the PROC, LOG and MON tables
p_baselineScope baseline_scope NULL Scope of the averages (EWMA) of traces and events: NULL = process name, i.e. shared by all processes with this name; '#NONE' = only within the individual process; otherwise a freely chosen name that can also be shared by several applications
p_syncLevel sync_level logLevelError Entries up to this level are written synchronously, all others are buffered. logLevelWarn makes WARN synchronous as well, logLevelSilent switches synchronous writing off completely. See Synchronous Writing

Return value: NUMBER, the process ID.

If SERVER_NEW_SESSION cannot create a process, it raises no exception but returns a negative value. All further API calls with this ID are ignored without error; the application keeps running, only without logging and monitoring for this process. The cause is logged in LILAM_LOG_INTERNAL.

Constant Value Meaning
NUM_ERR_SESSION_TIMEOUT -20110 The server did not answer in time
NUM_ERR_SESSION_THROTTLED -20120 The server rejected the request (overload)
NUM_COMM_ERR -20003 Communication error, e.g. no active server found 
l_processId := lilam.server_new_session('IMPORT_CUSTOMERS', 'BATCH');
if l_processId < 0 then
  -- optional: own reaction, e.g. notify operations
  null;   -- l_processId = lilam.NUM_ERR_SESSION_TIMEOUT, ...
end if;

[!NOTE] If the client waits for the answer in vain, the server will not create the process later either. The client passes an expiry time; if the request reaches the server only after that, it is discarded. This avoids orphaned processes that are never closed.

[!NOTE] Thanks to the baseline scope, even an application that is restarted frequently builds a stable reference for its run times. The averages are stored in the tables LILAM_SCOPES and LILAM_BASELINES.

Examples

-- name only, all other values by default
l_processId := lilam.new_session('IMPORT_CUSTOMERS');

-- log level INFO and 500 planned steps
l_processId := lilam.new_session('IMPORT_CUSTOMERS', lilam.logLevelInfo, 500);

-- single parameters by name
l_processId := lilam.new_session('IMPORT_CUSTOMERS', p_daysToKeep => 30);
l_processId := lilam.new_session('IMPORT_CUSTOMERS', p_baselineScope => '#NONE');

-- In-Session with the rules of the group BATCH
l_processId := lilam.new_session('IMPORT_CUSTOMERS', p_groupName => 'BATCH');

-- also write WARN immediately and durably
l_processId := lilam.new_session('IMPORT_CUSTOMERS', p_syncLevel => lilam.logLevelWarn);

-- decoupled: any available server or a server of the group BATCH
l_processId := lilam.server_new_session('IMPORT_CUSTOMERS');
l_processId := lilam.server_new_session('IMPORT_CUSTOMERS', 'BATCH', lilam.logLevelInfo);

Procedure CLOSE_SESSION

Ends a LILAM process. Optionally, final process information, status and progress can be passed.

[!IMPORTANT] Always call CLOSE_SESSION when a process ends. LILAM buffers data for performance reasons. CLOSE_SESSION makes sure that remaining buffered data is persisted.

CLOSE_SESSION should therefore also be part of the final exception handling. If the process is to continue after the exception (e.g. an AJAX page keeps working), use FLUSH instead.

PROCEDURE CLOSE_SESSION(
  p_processId     NUMBER,
  p_processInfo   VARCHAR2    DEFAULT NULL,
  p_processStatus PLS_INTEGER DEFAULT NULL,
  p_procStepsDone PLS_INTEGER DEFAULT NULL,
  p_procStepsToDo PLS_INTEGER DEFAULT NULL
)
Parameter Description
p_processId Process ID from NEW_SESSION or SERVER_NEW_SESSION
p_processInfo Final information about the process
p_processStatus Final status
p_procStepsDone Number of completed steps
p_procStepsToDo Number of planned steps

Parameters left NULL do not change the current value of the process.

lilam.close_session(l_processId);
lilam.close_session(l_processId, 'Import finished', 1);
lilam.close_session(l_processId, 'Import finished', 1, 500);

Example for exception handling:

EXCEPTION
  WHEN OTHERS THEN
    lilam.close_session(
      p_processId     => l_proc_id,
      p_processInfo   => SQLERRM,
      p_processStatus => -1
    );
    RAISE;

Procedure FLUSH

For performance reasons, LILAM buffers logging, monitoring and process data and writes them time-controlled (see When are metrics and process data written?).

FLUSH immediately writes all buffered data of all open processes of the current database session, including baselines. Unlike CLOSE_SESSION, FLUSH does not end a process: the processes stay open, open traces keep running, and counters and averages keep counting.

PROCEDURE FLUSH

Typical uses:

BEGIN
  lilam.flush;
END;
/

[!IMPORTANT] FLUSH only affects the database session it is called from. For processes in decoupled mode (server, dispatcher) FLUSH has no effect: their buffers are held by the LILAM server, which writes them time-controlled itself.

A FLUSH costs one commit (about 1.5 to 3.5 ms on the test system).

Process Control

The process control APIs manage the overall progress and status of a process.

API Purpose
SET_PROCESS_STATUS Updates the process status and optional process information
SET_PROC_STEPS_TODO Sets the planned number of process steps
PROC_STEP_DONE Increments the number of completed process steps
SET_PROC_STEPS_DONE Sets the number of completed process steps explicitly
GET_PROC_STEPS_DONE Returns the number of completed process steps
GET_PROC_STEPS_TODO Returns the planned number of process steps
GET_PROCESS_START Returns the start time of the process
GET_PROCESS_END Returns the end time of the process
GET_PROCESS_STATUS Returns the process status
GET_PROCESS_INFO Returns the process information
SET_PROC_IMMORTAL Protects a process from automatic cleanup
GET_PROCESS_DATA Returns all process data in a record
GET_PROCESS_DATA_JSON Returns all process data as JSON

[!NOTE] Changes to process data implicitly update the lastUpdate value of the process record.

Process data is buffered and written time-controlled, see When are metrics and process data written?.

Procedure SET_PROCESS_STATUS

Updates the application-specific numeric process status and optionally the process information.

The meaning of the status value is not defined by LILAM but by the calling application.

PROCEDURE SET_PROCESS_STATUS(
  p_processId   NUMBER,
  p_status      PLS_INTEGER,
  p_processInfo VARCHAR2 DEFAULT NULL
)

Procedure SET_PROC_STEPS_TODO

Sets the planned number of steps for the overall process.

PROCEDURE SET_PROC_STEPS_TODO(
  p_processId     NUMBER,
  p_procStepsToDo NUMBER
)

Procedure PROC_STEP_DONE

Increments the number of completed process steps.

PROCEDURE PROC_STEP_DONE(
  p_processId NUMBER
)

Procedure SET_PROC_STEPS_DONE

Sets the number of completed process steps explicitly.

A call overwrites a progress value previously built up with PROC_STEP_DONE.

PROCEDURE SET_PROC_STEPS_DONE(
  p_processId     NUMBER,
  p_procStepsDone NUMBER
)

Procedure SET_PROC_IMMORTAL

Marks a process to be kept permanently (1) or removes the mark (0). Processes with procImmortal = 1 are not deleted by the automatic cleanup via p_daysToKeep. The value can also be set at startup via t_session_init.procImmortal.

PROCEDURE SET_PROC_IMMORTAL(
  p_processId NUMBER,
  p_immortal  NUMBER
)

Function GET_PROC_STEPS_DONE

FUNCTION GET_PROC_STEPS_DONE(
  p_processId NUMBER
) RETURN PLS_INTEGER

Returns the number of process steps already completed.

Function GET_PROC_STEPS_TODO

FUNCTION GET_PROC_STEPS_TODO(
  p_processId NUMBER
) RETURN PLS_INTEGER

Returns the planned number of process steps.

Function GET_PROCESS_START

FUNCTION GET_PROCESS_START(
  p_processId NUMBER
) RETURN TIMESTAMP

Returns the time at which the process was started by NEW_SESSION or SERVER_NEW_SESSION.

Function GET_PROCESS_END

FUNCTION GET_PROCESS_END(
  p_processId NUMBER
) RETURN TIMESTAMP

Returns the time at which the process was ended by CLOSE_SESSION.

Function GET_PROCESS_STATUS

FUNCTION GET_PROCESS_STATUS(
  p_processId NUMBER
) RETURN PLS_INTEGER

Returns the current application-specific numeric process status.

Function GET_PROCESS_INFO

FUNCTION GET_PROCESS_INFO(
  p_processId NUMBER
) RETURN VARCHAR2

Returns the information text stored with the process.

Function GET_PROCESS_DATA

Use this function when several properties of a process are needed at the same time.

This avoids several individual getter calls. The function returns a complete t_process_rec record.

FUNCTION GET_PROCESS_DATA(
  p_processId NUMBER
) RETURN t_process_rec

[!NOTE] GET_PROCESS_DATA is also the documented way to retrieve the process name and tabNameMaster together with the other process attributes.

The name of the process table is derived from the master table name by appending _PROC.

Function GET_PROCESS_DATA_JSON

Returns the same data as GET_PROCESS_DATA as a JSON object with the keys process_id, process_name, log_level, process_start, process_end, last_update, process_info, process_status, steps_todo, steps_done and tabname_master.

FUNCTION GET_PROCESS_DATA_JSON(
  p_processId NUMBER
) RETURN VARCHAR2

Logging

The logging APIs write messages to the LILAM log according to the active log level.

API Severity
ERROR ERROR
WARN WARN
INFO INFO
DEBUG DEBUG

All logging procedures follow the same signature pattern:

PROCEDURE ERROR(
  p_processId NUMBER,
  p_logText   VARCHAR2
)

PROCEDURE WARN(
  p_processId NUMBER,
  p_logText   VARCHAR2
)

PROCEDURE INFO(
  p_processId NUMBER,
  p_logText   VARCHAR2
)

PROCEDURE DEBUG(
  p_processId NUMBER,
  p_logText   VARCHAR2
)

ERROR has the highest priority and is always stored unless logging has been switched off completely with logLevelSilent.

When an entry is actually in the table is described under Synchronous Writing.

LILAM always handles internal errors silently and logs them in LILAM_LOG_INTERNAL; the application never receives an exception. If logLevelDebug is active, LILAM additionally writes such errors as ERROR to the log of the affected process.

The complete mapping can be found under Log Levels.

Synchronous Writing (p_syncLevel)

LILAM buffers log entries for performance reasons. Entries up to the sync level of the process, however, are written immediately and durably. The sync level is set with p_syncLevel when the process is started; the default is logLevelError.

Mode Entries up to the sync level All other entries
In-Session are committed in an autonomous transaction before the call returns. LILAM also writes all other buffered data of the database session. stay in the buffer for up to about 1.5 seconds, longer if the session does not call LILAM again (log calls, MARK_EVENT, TRACE_STOP and process control trigger the write-back, see When are metrics and process data written?)
Decoupled go to the server as usual and are written to the work table there. As a safety net, the client additionally writes them itself in an autonomous transaction before the call returns, always into LILAM_LOG of the client’s schema (created if missing), with the process ID and the value -1 in column NO. are sent to the server via the pipe and buffered there

A synchronously written entry therefore survives an abort of the session and, in decoupled mode, a failure of the LILAM server. Buffered entries are lost if a session ends without CLOSE_SESSION or FLUSH. Therefore call CLOSE_SESSION in the central exception handler, or FLUSH if the process is to continue.

[!NOTE] In decoupled mode, synchronous entries are normally stored twice: in the work table (written by the server) and in LILAM_LOG of the client’s schema (NO = -1). If the LILAM server fails, the entry can still be found in LILAM_LOG. LILAM_LOG is used because the work table may be in the server’s schema, which the client cannot reach.

On the test system a synchronous call costs about 1.5 to 3.5 ms instead of about 0.1 ms, mostly for the commit. Details, measurements and failure scenarios are in Architecture and Concepts.

Function GET_COUNTER_WARN / GET_COUNTER_ERROR

FUNCTION GET_COUNTER_WARN(
  p_processId NUMBER
) RETURN PLS_INTEGER

FUNCTION GET_COUNTER_ERROR(
  p_processId NUMBER
) RETURN PLS_INTEGER

Return the number of calls of WARN and ERROR for the process since it was started.

[!NOTE] Counting takes place in the database session that calls WARN or ERROR (in decoupled mode, i.e. on the client). For unknown or already closed processes, both functions return 0.

Metrics

Metrics record events and logical transactions within a process.

[!IMPORTANT] p_actionName and p_contextName together identify a metric.

A trace started with a context must be stopped with the same combination of action and context.

When are metrics and process data written?

LILAM buffers metrics and process data (status, progress) as well. In in-session mode, MARK_EVENT, TRACE_STOP and the process control procedures (SET_PROCESS_STATUS, SET_PROC_STEPS_TODO, SET_PROC_STEPS_DONE, PROC_STEP_DONE, SET_PROC_IMMORTAL) – like every log call – trigger the time-controlled write-back: data of a process older than about 1.5 seconds is written, and cross-process baselines (LILAM_BASELINES) are synchronized about every 1.5 seconds as well. At least 500 ms pass between two check runs of the same database session, so a single call usually costs only one time comparison. TRACE_START does not trigger a write-back. This way measured values and progress reach the database promptly even for pure monitoring applications that never log. API queries (e.g. GET_PROC_STEPS_DONE) in the same session read the current state from the buffer anyway. With FLUSH you write the buffer immediately without ending the process.

[!IMPORTANT] In in-session mode there is no timer. Data is only written when the session calls LILAM. Whatever is still buffered after the last call stays there until the session calls LILAM again. Data is only guaranteed to be written by CLOSE_SESSION (the process ends) or FLUSH (the process stays open).

AJAX and connection pools (e.g. APEX/ORDS): An in-session process only lives in the database session that called NEW_SESSION. In a pool, the next request usually runs in a different session; there the process ID is unknown and LILAM silently ignores the calls. A CLOSE_SESSION on a final page then does not reach the process, and its buffer stays in the original pool session.

Procedure MARK_EVENT

Use MARK_EVENT for a single event at a specific point in time within the process flow.

For repeated markers with the same action and context, LILAM tracks the time interval, the number of occurrences, the average duration and significant deviations in time.

PROCEDURE MARK_EVENT(
  p_processId   NUMBER,
  p_actionName  VARCHAR2,
  p_contextName VARCHAR2 DEFAULT NULL,
  p_timestamp   TIMESTAMP DEFAULT NULL
)

Procedure TRACE_START

Starts a logical transaction whose duration is measured.

PROCEDURE TRACE_START(
  p_processId   NUMBER,
  p_actionName  VARCHAR2,
  p_contextName VARCHAR2 DEFAULT NULL,
  p_timestamp   TIMESTAMP DEFAULT NULL
)

Procedure TRACE_STOP

Ends the corresponding logical transaction.

PROCEDURE TRACE_STOP(
  p_processId   NUMBER,
  p_actionName  VARCHAR2,
  p_contextName VARCHAR2 DEFAULT NULL,
  p_timestamp   TIMESTAMP DEFAULT NULL
)

[!IMPORTANT] When the session ends, open traces are checked. A trace that was not completed is logged as a warning.

Therefore also call CLOSE_SESSION in the final exception handling so that this check can take place.

Function GET_METRIC_AVG_DURATION

Returns the average duration for the given metric.

FUNCTION GET_METRIC_AVG_DURATION(
  p_processId   NUMBER,
  p_actionName  VARCHAR2,
  p_contextName VARCHAR2 DEFAULT NULL
) RETURN NUMBER

Function GET_METRIC_STEPS

Returns the number of occurrences of the given metric.

FUNCTION GET_METRIC_STEPS(
  p_processId   NUMBER,
  p_actionName  VARCHAR2,
  p_contextName VARCHAR2 DEFAULT NULL
) RETURN NUMBER

Server Control

In decoupled mode, a LILAM server receives client requests and handles logging and monitoring centrally.

Servers are identified by their pipe names and can optionally be assigned to groups.

[!IMPORTANT] Server pipe names must be unique within the database instance. Each server additionally creates a control pipe with the suffix _CTL (e.g. LILAM_SRV1_CTL for LILAM_SRV1); these names must not be used otherwise either.

A server uses two pipes:

Server selection: A client without a dispatcher and a dispatcher choose a server of the group for every new process by these criteria:

  1. fewest open processes (CURRENT_PROCESSES; the server updates the value right after each new and each closed process),
  2. lowest message rate (messages per second in the last housekeeping window, in buckets of 100 messages/s; a value older than 1.5 s counts as 0),
  3. the server that has been inactive the longest.

If the first two criteria are equal, the caller alternates between the servers (round robin per database session). This spreads even processes created in quick succession evenly. Dispatchers are never chosen.

Server loop and eco mode: After a message, the server checks the pipe once without waiting. If it is empty, it waits 1 s, then 2 s, then 5 s each time; an arriving message wakes it immediately. DBMS_PIPE only knows whole seconds, hence the integer steps. Housekeeping (registry with message rate, writing the buffers) runs every 500 ms, also while the server is busy; when idle, at the next wake-up. If the pipe is empty and a worker still holds unwritten logs, metrics or process data, it writes them at once (idle flush, at most every 200 ms; never on a dispatcher). After a pause, new entries therefore usually reach the table within a few to a few hundred milliseconds.

API Purpose
START_SERVER Starts a LILAM server in the current session
CREATE_SERVER Starts a LILAM server via DBMS_SCHEDULER
SERVER_SHUTDOWN Shuts down a server
GET_SERVER_PIPE Returns the server pipe of a connected client
SERVER_UPDATE_RULES Activates a rule set for a server group
SET_DISPATCHER_PIPE Configures a dispatcher for automatic routing and reconnect

Procedure START_SERVER

Starts a LILAM server.

The password has to be given again when the server is shut down later.

PROCEDURE START_SERVER(
  p_pipeName     VARCHAR2,
  p_groupName    VARCHAR2,
  p_password     VARCHAR2,
  p_isDispatcher PLS_INTEGER DEFAULT 0,
  p_perfServer   PLS_INTEGER DEFAULT NULL
)

Parameters

| Parameter | Type | Meaning | | ——— | — | ——— | | p_pipeName | varchar2 | Unique pipe name of the server | | p_groupName | varchar2 | Optional group for server selection | | p_password | varchar2 | Password required again for SERVER_SHUTDOWN | | p_isDispatcher | pls_integer | 1 starts the server in dispatcher mode (see Dispatcher Mode), 0 (default) starts a regular server | | p_perfServer | pls_integer | Performance level of the server, see Performance Level. NULL (default) = C_SERVER_PERF_MID |

Performance Level (p_perfServer)

To keep a client from flooding the server with messages, the client briefly synchronizes with the server after a certain number of messages per process and second and waits until the server has caught up. p_perfServer sets this limit. The server passes it to the client with SERVER_NEW_SESSION (and on automatic reconnect); no extra call is needed in the application.

Constant Value Use
C_SERVER_PERF_LOW 500 Less powerful environments
C_SERVER_PERF_MID 1500 Default; typical servers
C_SERVER_PERF_HIGH 2500 Powerful servers

Any other value is allowed. 0 disables the synchronization; NULL or negative values count as C_SERVER_PERF_MID.

[!NOTE] The limit applies per process. If many applications send to the same server at the same time, its total throughput is lower than the sum of the individual values; then rather choose C_SERVER_PERF_LOW or C_SERVER_PERF_MID, or start further servers of the same group.

Function CREATE_SERVER

Starts a LILAM server via DBMS_SCHEDULER and returns server information as VARCHAR2.

FUNCTION CREATE_SERVER(
  p_pipeName     VARCHAR2,
  p_groupName    VARCHAR2,
  p_password     VARCHAR2,
  p_isDispatcher PLS_INTEGER DEFAULT 0,
  p_perfServer   PLS_INTEGER DEFAULT NULL
) RETURN VARCHAR2

Parameters identical to START_SERVER.

-- Example: server of the group BATCH with medium performance level
dbms_output.put_line(lilam.create_server('LILAM_SRV1', 'BATCH', 'secret', p_perfServer => lilam.C_SERVER_PERF_MID));

Procedure SERVER_SHUTDOWN

The client must already be connected to the server.

Shutting down requires the process ID, the server pipe and the password given at server start.

PROCEDURE SERVER_SHUTDOWN(
  p_processId NUMBER,
  p_pipeName  VARCHAR2,
  p_password  VARCHAR2
)

On shutdown, the server first unregisters in the registry and is no longer chosen from then on. It then processes the messages that clients have already sent (drain phase) until the pipe stays empty for 1 s, at most about 5 s. Afterwards it writes all buffers and terminates.

Function GET_SERVER_PIPE

Returns the server pipe associated with the connected client process.

FUNCTION GET_SERVER_PIPE(
  p_processId NUMBER
) RETURN VARCHAR2

Procedure SERVER_UPDATE_RULES

Rule sets are stored as JSON objects in LILAM_RULES, each for a server group (GROUP_NAME, name, version). The same rule set can be stored for several groups. Exactly one rule set per group is active (IS_ACTIVE = 1); it applies to all servers of the group.

PROCEDURE SERVER_UPDATE_RULES(
  p_groupName      VARCHAR2,
  p_ruleSetName    VARCHAR2,
  p_ruleSetVersion PLS_INTEGER
)

Steps:

  1. The rule set of the group is checked completely in the calling session. If it is missing for the group or a rule is invalid, the call ends with the exception NUM_ERR_RULE_SET (-20130) and a reason; nothing is changed.
  2. The rule set becomes active for the group, the previously active one inactive.
  3. Running servers of the group receive the instruction to reload directly in their pipe, so no running process is needed and the dispatcher is bypassed. Dispatchers do not evaluate rules.

A group without running servers is not an error: every server loads the active rule set of its group at startup, including a newly added one. If a server rejects a rule set at startup (e.g. because it was changed directly in the table in the meantime), it keeps its previous rules (at startup: none) and logs the reason to LILAM_LOG_INTERNAL and to the log of the server process.

INSERT INTO LILAM_RULES (group_name, set_name, version, created, author, rule_set)
VALUES ('METRO', 'METRO_RULES', 2, systimestamp, 'Dirk', '{"rules":[ ... ]}');

exec LILAM.SERVER_UPDATE_RULES('METRO', 'METRO_RULES', 2);

Rules are evaluated by servers only, not in in-session mode. Structure of rule sets and operators: Rules Engine.

Dispatcher Mode

A server started with p_isDispatcher => 1 (dispatcher) does not process requests itself but forwards them unchanged to a suitable server.

For NEW_SESSION/SERVER_NEW_SESSION, the dispatcher uses the same load-based mechanism as the regular server selection and forwards the request to the control pipe of the selected server; for all other requests, it determines the server responsible for the application’s process from the already assigned process_id and forwards the request there.

The answer of the responsible server goes directly back to the client, not via the dispatcher.

A dispatcher is marked in the server registry (IS_DISPATCHER = 1) and is never chosen as a target of the server selection. Workers and dispatchers can therefore run in the same group: clients without dispatcher configuration always get a worker directly.

[!TIP] A dispatcher is mainly relevant for applications that do not keep their physical database connection permanently – typically Oracle APEX applications with connection pooling. A follow-up page may then run in a different physical session than the page that originally started the process. A configured dispatcher allows LILAM to restore the connection to the responsible worker automatically in this case, without the application having to control this itself.

Applications with a permanent database session (classic in-session or decoupled operation without connection pooling) do not need a dispatcher.

Automatic Reconnect

If a dispatcher is configured, LILAM automatically and transparently tries to restore a connection via the dispatcher for every API call with a process_id unknown to the current physical session. If this fails (no dispatcher configured, dispatcher not reachable, or the process no longer exists), the call behaves like any other call with an unknown process_id: it is ignored without an error message.

The following applies:

Prewarming

The automatic reconnect attempt costs a one-time pipe round trip. Without prewarming, the first API call after a session change carries this additional latency. If p_processId is passed, this round trip already takes place when SET_DISPATCHER_PIPE is called – typically while the page is rendered, before the application reacts.

Procedure SET_DISPATCHER_PIPE

Tells LILAM via which pipe a dispatcher can be reached. This information is kept exclusively in the memory of the current physical database session.

[!IMPORTANT] Since the configuration only applies to the current physical session, SET_DISPATCHER_PIPE must be called again for every new connection – with connection pooling potentially on every page, not just once on the first page call.

PROCEDURE SET_DISPATCHER_PIPE(
  p_pipeName  VARCHAR2,
  p_groupName VARCHAR2 DEFAULT 'DEFAULT_DISPATCHER',
  p_processId NUMBER   DEFAULT NULL
)

Parameters

| Parameter | Type | Meaning | | ——— | — | ——— | | p_pipeName | varchar2 | Pipe name of the dispatcher | | p_groupName | varchar2 | Optional identifier if several dispatchers are used in parallel. Automatic Reconnect only uses the default identifier ‘DEFAULT_DISPATCHER’ | | p_processId | number | Optional. If a process_id is already known, LILAM restores the connection to it immediately (see Prewarming) instead of at the next API call |

-- Example: APEX "Before Header" process
BEGIN
  lilam.set_dispatcher_pipe(
    p_pipeName  => 'LILAM_DISPATCHER_SALES',
    p_processId => :G_LILAM_PROCESS_ID  -- NULL on the very first page call
  );
END;
/

Appendix

Parameter Requirements

Marker Meaning
M Mandatory
O Optional
N Nullable
D Default value

Log Levels

The active log level determines which log messages are written.

Level Value Behavior
logLevelSilent 0 No log details
logLevelError 1 ERROR
logLevelWarn 2 WARN and ERROR
logLevelMonitor 3 Enables the monitoring functions
logLevelInfo 4 INFO, WARN and ERROR
logLevelDebug 8 DEBUG, INFO, WARN and ERROR 
logLevelSilent  CONSTANT PLS_INTEGER := 0;
logLevelError   CONSTANT PLS_INTEGER := 1;
logLevelWarn    CONSTANT PLS_INTEGER := 2;
logLevelMonitor CONSTANT PLS_INTEGER := 3;
logLevelInfo    CONSTANT PLS_INTEGER := 4;
logLevelDebug   CONSTANT PLS_INTEGER := 8;

Record Type t_session_init

Use t_session_init to combine the initialization settings and pass them to the record-based NEW_SESSION overload.

TYPE t_session_init IS RECORD (
  processName   VARCHAR2(100),
  logLevel      PLS_INTEGER := logLevelMonitor,
  stepsToDo     PLS_INTEGER,
  daysToKeep    PLS_INTEGER,                    -- NULL = no automatic cleanup
  procImmortal  PLS_INTEGER := 0,
  tabNameMaster VARCHAR2(100) DEFAULT 'LILAM',
  baselineScope VARCHAR2(100),                  -- NULL = process name, '#NONE' = per process only
  groupName     VARCHAR2(50),                   -- group for the active rule set; NULL = no rules
  syncLevel     PLS_INTEGER := logLevelError    -- write synchronously up to this level
);

Record Type t_process_rec

t_process_rec contains the process data returned by GET_PROCESS_DATA.

TYPE t_process_rec IS RECORD (
  id            NUMBER(19,0),
  processName   VARCHAR2(100),
  logLevel      PLS_INTEGER,
  processStart  TIMESTAMP,
  processEnd    TIMESTAMP,
  lastUpdate    TIMESTAMP,
  stepsTodo     PLS_INTEGER,
  stepsDone     PLS_INTEGER,
  status        PLS_INTEGER,
  info          VARCHAR2(4000),
  procImmortal  PLS_INTEGER := 0,
  tabNameMaster VARCHAR2(100)
);

Procedure IS_ALIVE

Simple function test after installation: creates the process LILAM Life Check in in-session mode, writes a DEBUG entry and closes the process. On the first call LILAM creates its tables; missing privileges therefore show up immediately (entries in LILAM_LOG_INTERNAL).

exec lilam.is_alive;

JSON API Interface

With CALL_BY_JSON, the most important API calls can be passed as JSON, e.g. from applications that create JSON more easily than PL/SQL calls.

PROCEDURE CALL_BY_JSON(
  p_callObject IN  VARCHAR2,      -- JSON_OBJ_LILAM
  p_respObject OUT VARCHAR2
)

PROCEDURE CALL_BY_JSON(
  p_callObject IN  JSON_OBJECT_T,
  p_respObject OUT JSON_OBJECT_T
)

LILAM JSON requests consist of a header and a parameter object. The header contains the call in api_call; the header parameters version and client_id are currently not used.

api_call corresponds to Parameters (params)
NEW_SESSION NEW_SESSION (record) process_name, log_level, steps_todo, days_to_keep, process_immortal, tabname_master, baseline_scope
SERVER_NEW_SESSION SERVER_NEW_SESSION_JSON as SERVER_NEW_SESSION, see table there
CLOSE_SESSION CLOSE_SESSION process_id
FLUSH FLUSH none
SET_PROCESS_STATUS SET_PROCESS_STATUS process_id, process_status, process_info
SET_STEP_TODO SET_PROC_STEPS_TODO process_id, steps_todo
SET_STEPS_DONE SET_PROC_STEPS_DONE process_id, steps_done
PROC_STEP_DONE PROC_STEP_DONE process_id
SET_PROC_IMMORTAL SET_PROC_IMMORTAL process_id, process_immortal
INFO, DEBUG, WARN, ERROR Logging process_id, process_info (log text)
MARK_EVENT, TRACE_START, TRACE_STOP Metrics process_id, action_name, context_name, timestamp
SERVER_SHUTDOWN SERVER_SHUTDOWN process_id, pipe_name, password

The answer contains the header of the request, status (SUCCESS or ERROR) and a payload with returns and value, e.g. "returns": "PROCESS_ID", "value": 4711. For an unknown api_call, value is NUM_ERR_ILLEGAL_REQ (-20010). If p_callObject is not valid JSON, the call ends with the exception -20005.

Example for SERVER_NEW_SESSION:

{
  "header": {
    "version": "v1.x.x",
    "client_id": "GATE_15",
    "api_call": "SERVER_NEW_SESSION"
  },
  "params": {
    "process_name": "Your Process Name",
    "log_level": 3,
    "steps_todo": 100,
    "days_to_keep": 20,
    "tabname_master": "GATES"
  }
}
This site is open source. Improve this page.