LILA-Logging

LILA - LILA Integrated Logging Architecture

Lila Logger Logo

LILA is a lightweight logging and monitoring framework designed for Oracle PL/SQL applications. It provides a fast, concurrent way to track processes. Its simple API allows for seamless integration into existing applications with minimal overhead.

LILA utilizes autonomous transactions to ensure that all log entries are persisted, even if the main process performs a rollback.

LILA is developed by a developer who hates over-engineered tools. Focus: 5 minutes to integrate, 100% visibility.

Key features

Lightweight: One Package, two Tables, one Sequence. That’s it!
Concurrent Logging: Supports multiple, simultaneous log entries from the same or different sessions without blocking
Monitoring: You have the option to observe your applications via SQL or via the API
Data Integrity: Uses autonomous transactions to guarantee log persistence regardless of the main transaction’s outcome
Smart Context Capture: Automatically records ERR_STACK, ERR_BACKTRACE, and ERR_CALLSTACK based on log level—deep insights with zero manual effort
Optional self-cleaning: Automatically purges expired logs per application during session start—no background jobs or schedulers required
Future Ready: Built for the latest Oracle 26ai (2026), and fully tested with existing 19c environment
Small Footprint: Less than 700 lines of logical PL/SQL code ensures simple quality and security control, fast compilation, zero bloat and minimal Shared Pool utilization (reducing memory pressure and fragmentation)

Fast integration

Setting up LILA means creating a package by copy&paste (refer documentation file “setup.md”)
Only a few API calls are necessary for the complete logging of a process (refer documentation file “API.md”)
Analysing or monitoring your process requires simple sql statements or API requests

LILA comes ready to test right out of the box, so no custom implementation or coding is required to see the framework in action immediately after setup. Also please have a look to the sample applications ‘learn_lila’: https://github.com/dirkgermany/LILA-Logging/tree/main/demo/first_steps.

Demo

Execute the following statement in the SQL editor (optionally activate dbms-output for your session beforehand):

exec lila.is_alive;
select * from lila_log;

If you have activated dbms output, you will receive an additional message there.

Logging

LILA persists different information about your processes. For simplicity, all logs are stored in two tables.

The master table contains data about the process itself (the live-dashboard). Always exactly one record per process. This table frees you from complex queries such as “group by,” “max(timestamp),” etc., which you would otherwise have to run on thousands or millions of rows to see the current status of your process.
The table with typical detailed log information (the process-history). This second table enables rapid monitoring because the constantly growing number of entries has no impact on the master table.

Process information

Process name
Process ID
Timestamps process_start
Timestamp process_end
Timestamp last_update (at end of your process identical with timestamp of process_end)
Steps todo and steps done
Any info
(Last) status

Detailed information

Process ID
Serial number
Any info
Log level
Session time
Session user
Host name
Error stack (when exception was thrown)
Error backtrace (depends to log level)
Call stack (depends to log level)

How to log

A code snippet:

procedure MY_DEMO_PROC
as
  -- process ID related to your logging process
  lProcessId number(19,0);

begin
  -- begin a new logging session
  -- the last parameter refers to killing log entries which are older than the given number of days
  -- if this param is NULL, no log entry will be deleted
  lProcessId := lila.new_session('my application', lila.logLevelWarn, 30);

  -- write a log entry whenever you want
  lila.info(lProcessId, 'Start');
  -- for more details...
  lila.debug(lProcessId, 'Function A');
  -- e.g. information when an exception was raised
  lila.error(lProcessId, 'An error occurred');

  -- also you can change the status during your process runs
  lila.set_process_status(lProcessId, 1, 'DONE');

  -- last but not least end the logging session
  -- optional you can set the numbers of steps to do and steps done 
  lila.close_session(lProcessId, 100, 99, 'DONE', 1);

end MY_DEMO_PROC;

Monitoring

Monitor your processes according to your requirements:

Real-time Progress: Query the master table for a single-row snapshot of any running process (steps_todo, steps_done, status, timestamps).
Deep Dive (Details): Query the detail table for the full chronological history and error stack of a process.
API Access: Use the built-in getter functions to retrieve status and progress directly within your PL/SQL logic or UI components.

How to monitor

Three options:

Real-time Progress

Live-dashboard data

SELECT id, status, last_update, ... FROM lila_log WHERE process_name = ... (provides the current status of the process)

ID PROCESS_NAME PROCESS_START PROCESS_END LAST_UPDATE STEPS_TO_DO STEPS_DONE STATUS INFO

1 my application 12.01.26 18:17:51,… 12.01.26 18:18:53,… 12.01.26 18:18:53,… 100 99 2 ERROR

ID	PROCESS_NAME	PROCESS_START	PROCESS_END	LAST_UPDATE	STEPS_TO_DO	STEPS_DONE	STATUS	INFO
1	my application	12.01.26 18:17:51,…	12.01.26 18:18:53,…	12.01.26 18:18:53,…	100	99	2	ERROR

Deep Dive

Historical data

SELECT * FROM lila_log_detail WHERE process_id = ...

PROCESS_ID NO INFO LOG_LEVEL SESSION_TIME SESSION_USER HOST_NAME ERR_STACK ERR_BACKTRACE ERR_CALLSTACK

1 1 Start INFO 13.01.26 10:… SCOTT SERVER1 NULL NULL NULL

1 2 Function A DEBUG 13.01.26 11:… SCOTT SERVER1 NULL NULL ”— PL/SQL …”

1 3 I made a fault ERROR 13.01.26 12:… SCOTT SERVER1 ”— PL/SQL …” ”— PL/SQL …” ”— PL/SQL …”

PROCESS_ID	NO	INFO	LOG_LEVEL	SESSION_TIME	SESSION_USER	HOST_NAME	ERR_STACK	ERR_BACKTRACE	ERR_CALLSTACK
1	1	Start	INFO	13.01.26 10:…	SCOTT	SERVER1	NULL	NULL	NULL
1	2	Function A	DEBUG	13.01.26 11:…	SCOTT	SERVER1	NULL	NULL	”— PL/SQL …”
1	3	I made a fault	ERROR	13.01.26 12:…	SCOTT	SERVER1	”— PL/SQL …”	”— PL/SQL …”	”— PL/SQL …”

API

The API provides all process data which belongs to the process_id (see Logging).

...
FUNCTION getStatus(p_processId NUMBER) RETURNS VARCHAR2
...
lProcessStatus := lila.get_process_status(p_processId);
lProcessInfo := lila.get_process_info(p_processId);
lStepsDone := lila.get_steps_done(p_processId);
...
return 'ID = ' || id || '; Status: ' || lProcessStatus || '; Info: ' || lProcessInfo || '; Steps completed: ' || lStepsDone;

SELECT my_app.getStatus(1) proc_status FROM dual;
> ID = 1; Status: OK; Info: 'just working'; Steps completed: 42

Support the Project 💜

Do you find LILA useful? Consider sponsoring the project to support its ongoing development and long-term maintenance.

This site is open source. Improve this page.