Skip to main content

​
Overview

The OMNI_TRACER_API package is the core PL/SQL component that OmniView deploys to Oracle databases. It provides functionality for:
  • Tracing messages from PL/SQL processes
  • Managing Oracle Advanced Queuing (AQ) for asynchronous message delivery
  • Subscriber registration and management
  • Bulk message dequeuing for efficient processing
The package is automatically deployed by OmniView’s TracerService when a connection is established to a new database.

​
Package Structure

The package consists of several components:

​
Database Objects

  • OMNI_TRACER_ID_SEQ: Sequence for generating unique message IDs
  • OMNI_TRACER_PAYLOAD_TYPE: Object type containing JSON message data (BLOB)
  • OMNI_TRACER_PAYLOAD_ARRAY: Table of payload objects
  • OMNI_TRACER_RAW_ARRAY: Table of RAW(16) message IDs
  • OMNI_TRACER_QUEUE: Sharded queue for message distribution

​
Core Procedures and Functions

Initialize

Set up the queue infrastructure

Trace_Message

Send trace messages to the queue

Register_Subscriber

Register a new subscriber to the queue

Dequeue_Array_Events

Bulk dequeue messages for processing

​
Package Specification

Omni_Tracer.sql:98-117
CREATE OR REPLACE PACKAGE OMNI_TRACER_API AS 
    TRACER_QUEUE_NAME CONSTANT VARCHAR2(30) := 'OMNI_TRACER_QUEUE';

    -- Core Methods
    PROCEDURE Initialize;
    PROCEDURE Trace_Message(message_ IN CLOB, log_level_ IN VARCHAR2 DEFAULT 'INFO');
    PROCEDURE Dequeue_Array_Events(
        subscriber_name_ IN  VARCHAR2,
        batch_size_      IN  INTEGER,
        wait_time_       IN  NUMBER DEFAULT DBMS_AQ.NO_WAIT,
        messages_        OUT OMNI_TRACER_PAYLOAD_ARRAY,
        message_ids_     OUT OMNI_TRACER_RAW_ARRAY,
        msg_count_       OUT INTEGER
    );
    
    -- Subscriber Management
    PROCEDURE Register_Subscriber(subscriber_name_ IN VARCHAR2);

END OMNI_TRACER_API;

​
Initialize

The Initialize procedure sets up the Oracle Advanced Queue infrastructure required for message tracing.

​
Functionality

  • Creates a sharded queue with multiple consumers enabled
  • Configures 4 shards for parallel message processing
  • Uses autonomous transaction to ensure queue creation is committed independently
  • Handles race conditions when multiple instances try to initialize simultaneously

​
Queue Configuration

​
queue_name
VARCHAR2
OMNI_TRACER_QUEUE
​
multiple_consumers
BOOLEAN
TRUE - Allows named subscribers to receive messages
​
queue_payload_type
VARCHAR2
OMNI_TRACER_PAYLOAD_TYPE - Custom object type containing BLOB JSON data
​
SHARD_NUM
NUMBER
4 - Number of shards for parallel processing (can be adjusted based on load)

​
Usage

BEGIN
    OMNI_TRACER_API.Initialize;
END;
/
OmniView automatically calls Initialize during the deployment process. You typically don’t need to call this manually.

​
Error Handling

The procedure handles the following scenarios:
  • Queue already exists: Silently succeeds (idempotent operation)
  • Race condition (ORA-24001): Commits and returns successfully
  • Other errors: Rolls back and raises the exception

​
Deployment

The OMNI_TRACER_API package is deployed by OmniView’s TracerService using the following workflow:

​
Deployment Process

1

Check package existence

TracerService queries user_objects to determine if OMNI_TRACER_API is already deployed and valid.
tracer_service.go:125
*exists, err = ts.db.PackageExists("OMNI_TRACER_API")
2

Deploy SQL file

If the package doesn’t exist, TracerService reads the embedded Omni_Tracer.sql file and deploys it to the database.
tracer_service.go:136-142
omniTracerSQLPackage, err := assets.GetSQLFile("Omni_Tracer.sql")
if err != nil {
    return fmt.Errorf("failed to read Omni tracer package file: %w", err)
}

if err := ts.db.DeployFile(string(omniTracerSQLPackage)); err != nil {
    return fmt.Errorf("failed to deploy Omni tracer package: %w", err)
}
3

Initialize the queue

After deployment, TracerService calls the Initialize procedure to set up the queue infrastructure.
tracer_service.go:150-156
omniInitInsFile, err := assets.GetInsFile("Omni_Initialize.ins")
if err != nil {
    return fmt.Errorf("failed to read Omni initialize file: %w", err)
}

if err := ts.db.ExecuteStatement(string(omniInitInsFile)); err != nil {
    return fmt.Errorf("failed to deploy Omni initialize file: %w", err)
}

​
Deployment Execution Order

The SQL file is deployed in the following order:
  1. Sequences: OMNI_TRACER_ID_SEQ for message ID generation
  2. Types: OMNI_TRACER_PAYLOAD_TYPE, OMNI_TRACER_PAYLOAD_ARRAY, OMNI_TRACER_RAW_ARRAY
  3. Package Specification: Public interface definition
  4. Package Body: Implementation of all procedures and functions
The package types are created as NONEDITIONABLE in edition-enabled databases to ensure compatibility with Oracle Advanced Queuing sharded queues.

​
Message Format

Messages in the queue follow this JSON structure: 
{
  "MESSAGE_ID": "12345",
  "PROCESS_NAME": "OMNI_TRACER_API",
  "LOG_LEVEL": "INFO",
  "PAYLOAD": "Your message content here",
  "TIMESTAMP": "2026-03-03T10:30:45.123+00:00"
}
​
MESSAGE_ID
string
Unique identifier from OMNI_TRACER_ID_SEQ
​
PROCESS_NAME
string
Name of the calling process (default: “OMNI_TRACER_API”)
​
LOG_LEVEL
string
Severity level: INFO, WARNING, ERROR, DEBUG
​
PAYLOAD
string
The actual message content (CLOB)
​
TIMESTAMP
string
ISO 8601 timestamp with timezone

Trace_Message

Learn how to send trace messages from PL/SQL

Subscriber Management

Understand subscriber registration and message dequeuing

Build docs developers (and LLMs) love

Get started for freeTalk to us