Trace_Message

Overview

The Trace_Message procedure is the primary interface for sending trace messages from PL/SQL code to OmniView. Messages are enqueued to the Oracle Advanced Queue and delivered asynchronously to registered subscribers.

Procedure Signature

Omni_Tracer.sql:258-270

PROCEDURE Trace_Message (
    message_    IN CLOB,
    log_level_  IN VARCHAR2 DEFAULT 'INFO') 
IS
    calling_process_ VARCHAR2(100);
BEGIN
    calling_process_ := 'OMNI_TRACER_API';
    Enqueue_Event___(
        process_name_   => calling_process_,
        log_level_      => log_level_,
        payload         => message_
    );
END Trace_Message;

Parameters

message_

CLOB

required

The message content to trace. Can be any text content, including JSON, XML, or plain text. Maximum size is limited by CLOB (4GB).

log_level_

VARCHAR2

default:"INFO"

The severity level of the message. Common values include:

INFO: Informational messages
WARNING: Warning messages
ERROR: Error messages
DEBUG: Debug messages

Log Levels

While the package doesn’t enforce specific log levels, the following conventions are recommended:

INFO

Use for general informational messages about normal operations.

OMNI_TRACER_API.Trace_Message('User login successful', 'INFO');

WARNING

Use for potentially problematic situations that don’t prevent execution.

OMNI_TRACER_API.Trace_Message('Retry attempt 3 of 5', 'WARNING');

ERROR

Use for error conditions that affected the operation.

OMNI_TRACER_API.Trace_Message('Failed to process record: ' || SQLERRM, 'ERROR');

DEBUG

Use for detailed debugging information.

OMNI_TRACER_API.Trace_Message('Variable value: ' || v_debug_var, 'DEBUG');

Usage Examples

Basic Message

BEGIN
    OMNI_TRACER_API.Trace_Message('Processing started');
END;
/

Message with Log Level

BEGIN
    OMNI_TRACER_API.Trace_Message('Critical error occurred', 'ERROR');
END;
/

Tracing JSON Data

DECLARE
    v_json_data CLOB;
BEGIN
    v_json_data := '{
        "operation": "insert",
        "table": "employees",
        "rows_affected": 10
    }';
    
    OMNI_TRACER_API.Trace_Message(v_json_data, 'INFO');
END;
/

Exception Handling with Trace

DECLARE
    v_error_msg VARCHAR2(4000);
BEGIN
    -- Your business logic here
    PERFORM_OPERATION();
    
    OMNI_TRACER_API.Trace_Message('Operation completed successfully', 'INFO');
EXCEPTION
    WHEN OTHERS THEN
        v_error_msg := 'Error in operation: ' || SQLERRM || ' at ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
        OMNI_TRACER_API.Trace_Message(v_error_msg, 'ERROR');
        RAISE;
END;
/

Tracing in a Loop

DECLARE
    v_counter NUMBER := 0;
BEGIN
    FOR rec IN (SELECT * FROM orders WHERE status = 'PENDING') LOOP
        -- Process the order
        PROCESS_ORDER(rec.order_id);
        
        v_counter := v_counter + 1;
        
        -- Trace every 100 records
        IF MOD(v_counter, 100) = 0 THEN
            OMNI_TRACER_API.Trace_Message('Processed ' || v_counter || ' orders', 'INFO');
        END IF;
    END LOOP;
    
    OMNI_TRACER_API.Trace_Message('Completed processing ' || v_counter || ' total orders', 'INFO');
END;
/

How It Works

Message Enqueuing Flow

Create JSON message

The procedure constructs a JSON object containing the message metadata and payload.

Omni_Tracer.sql:218-223

message_ := JSON_OBJECT_T();
message_.PUT('MESSAGE_ID', TO_CHAR(OMNI_tracer_id_seq.NEXTVAL));
message_.PUT('PROCESS_NAME', process_name_);
message_.PUT('LOG_LEVEL', log_level_);
message_.PUT('PAYLOAD', payload);
message_.PUT('TIMESTAMP', TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'));

Convert to BLOB

The JSON CLOB is converted to a BLOB for storage in the queue payload object.

Omni_Tracer.sql:225-227

json_payload_ := message_.TO_CLOB();
temp_blob_ := Clob_To_Blob___(json_payload_);
payload_object_ := OMNI_TRACER_PAYLOAD_TYPE(temp_blob_);

Enqueue to AQ

The message is enqueued with IMMEDIATE visibility, making it available to subscribers without waiting for a commit.

Omni_Tracer.sql:216

enqueue_options_.visibility := DBMS_AQ.IMMEDIATE;

Omni_Tracer.sql:229-235

DBMS_AQ.ENQUEUE (
    queue_name          => TRACER_QUEUE_NAME,
    enqueue_options     => enqueue_options_,
    message_properties  => message_properties_,
    payload             => payload_object_,
    msgid               => message_handle_
);

Cleanup

Temporary LOBs are freed to prevent memory leaks.

Omni_Tracer.sql:237-243

IF temp_blob_ IS NOT NULL AND DBMS_LOB.ISTEMPORARY(temp_blob_) = 1 THEN
    DBMS_LOB.FREETEMPORARY(temp_blob_);
END IF;

IF json_payload_ IS NOT NULL AND DBMS_LOB.ISTEMPORARY(json_payload_) = 1 THEN
    DBMS_LOB.FREETEMPORARY(json_payload_);
END IF;

Messages are enqueued with DBMS_AQ.IMMEDIATE visibility, meaning they are available to subscribers immediately without waiting for the calling transaction to commit.

Message Structure

Each traced message is automatically wrapped in a JSON structure:

{
  "MESSAGE_ID": "12345",
  "PROCESS_NAME": "OMNI_TRACER_API",
  "LOG_LEVEL": "INFO",
  "PAYLOAD": "Your message content",
  "TIMESTAMP": "2026-03-03T10:30:45.123+00:00"
}

MESSAGE_ID

string

Automatically generated from OMNI_TRACER_ID_SEQ.NEXTVAL

PROCESS_NAME

string

Set to “OMNI_TRACER_API” by default (currently not customizable)

LOG_LEVEL

string

The log level passed to the procedure

PAYLOAD

string

The exact message content provided

TIMESTAMP

string

ISO 8601 formatted timestamp with timezone (e.g., “2026-03-03T10:30:45.123+00:00”)

Performance Considerations

Sharded Queue Architecture

The OMNI_TRACER_QUEUE is configured as a sharded queue with 4 shards by default. This enables:

Parallel enqueuing: Multiple sessions can enqueue messages simultaneously without contention
Parallel dequeuing: Subscribers can process messages in parallel from different shards
High throughput: Suitable for high-volume tracing scenarios

The shard count can be adjusted in the Initialize procedure based on your workload (Omni_Tracer.sql:150-154).

IMMEDIATE Visibility

Messages use IMMEDIATE visibility mode, which means:

Messages are visible to subscribers instantly
No need to wait for the enqueuing transaction to commit
Ideal for real-time monitoring and debugging

If the calling transaction rolls back, the traced message will still exist in the queue. This is by design for audit and debugging purposes.

Error Handling

The procedure includes comprehensive error handling:

Omni_Tracer.sql:244-254

EXCEPTION
    WHEN OTHERS THEN
        IF temp_blob_ IS NOT NULL AND DBMS_LOB.ISTEMPORARY(temp_blob_) = 1 THEN
            DBMS_LOB.FREETEMPORARY(temp_blob_);
        END IF;

        IF json_payload_ IS NOT NULL AND DBMS_LOB.ISTEMPORARY(json_payload_) = 1 THEN
            DBMS_LOB.FREETEMPORARY(json_payload_);
        END IF;

        RAISE;

Ensures temporary LOBs are freed even on error
Re-raises the original exception for calling code to handle

OmniView Processing

Once messages are enqueued, OmniView’s TracerService processes them:

Bulk dequeue

TracerService dequeues messages in batches using Dequeue_Array_Events.

tracer_service.go:78

messages, msgIDs, count, err := ts.db.BulkDequeueTracerMessages(*subscriber)

Parse JSON

Each message is unmarshaled from JSON into a QueueMessage domain object.

tracer_service.go:88-92

var msg domain.QueueMessage
if err := json.Unmarshal([]byte(messages[i]), &msg); err != nil {
    log.Printf("failed to unmarshal message ID %s: %v", msgIDs[i], err)
    continue
}

Handle message

TracerService outputs the message to the console (extensible for other outputs).

tracer_service.go:100-102

func (ts *TracerService) handleTracerMessage(msg domain.QueueMessage) {
    fmt.Printf("[%s] [%s] %s: %s \n", msg.Timestamp, msg.LogLevel, msg.ProcessName, msg.Payload)
}

OMNI_TRACER_API Package

Overview of the complete package

Subscriber Management

Learn how OmniView subscribes to messages

PL/SQL Package

Go Services

Overview

Procedure Signature

Parameters

Log Levels

Usage Examples

Basic Message

Message with Log Level

Tracing JSON Data

Exception Handling with Trace

Tracing in a Loop

How It Works

Message Enqueuing Flow

Message Structure

Performance Considerations

Sharded Queue Architecture

IMMEDIATE Visibility

Error Handling

OmniView Processing

OMNI_TRACER_API Package

Subscriber Management

Build docs developers (and LLMs) love

PL/SQL Package

Go Services

​Overview

​Procedure Signature

​Parameters

​Log Levels

​Usage Examples

​Basic Message

​Message with Log Level

​Tracing JSON Data

​Exception Handling with Trace

​Tracing in a Loop

​How It Works

​Message Enqueuing Flow

​Message Structure

​Performance Considerations

​Sharded Queue Architecture

​IMMEDIATE Visibility

​Error Handling

​OmniView Processing

​Related Topics

OMNI_TRACER_API Package

Subscriber Management

Build docs developers (and LLMs) love

Overview

Procedure Signature

Parameters

Log Levels

Usage Examples

Basic Message

Message with Log Level

Tracing JSON Data

Exception Handling with Trace

Tracing in a Loop

How It Works

Message Enqueuing Flow

Message Structure

Performance Considerations

Sharded Queue Architecture

IMMEDIATE Visibility

Error Handling

OmniView Processing

Related Topics