Resolving Large Multi-Byte CLOB Insert Failures Between HCL Domino and Oracle Through LSXLC Streaming

Blogs
1

Executive Summary

A common yet challenging enterprise integration issue occurs when large text content generated from an HCL Domino application fails to insert into Oracle CLOB (Character Large Object) / NCLOB (National Character Large Object) columns—even when the target database is designed to handle massive data volumes.

The symptoms are often elusive:

  • Small records insert successfully.

  • Large records fail or simply vanish without being stored.

  • Error reporting is highly inconsistent or silent.

Through rigorous root-cause investigation, this behavior was traced to how multi-byte text data is buffered during transfer between Domino and Oracle. By migrating from standard text buffers to LSXLC Large Object Streaming (LCTYPE_STREAM / Type Code 6), large payloads can be written seamlessly without truncation, clipping, or buffer overflows.

The Problem Statement

The validation was performed with large text payloads containing multi-byte Unicode characters. The issue was consistently observed when large text content was transferred using standard text handling mechanisms and was resolved by using LSXLC streaming with LCTYPE_STREAM (Type Code 6).

Note: The solution approach is applicable to environments where Domino integrates with Oracle databases through LSXLC and handles large CLOB/NCLOB data. The exact behavior may vary depending on Oracle client libraries, driver versions, operating system architecture, and configuration.

Environment Details

The issue was investigated and validated in an enterprise integration environment consisting of:

  • HCL Domino Server: Version 12.x and Version 14.x (64-bit)

  • Oracle Database: Oracle Database 21c

  • Integration Layer: HCL LSXLC Oracle Connector

  • Database Column Type: Oracle CLOB (Character Large Object) / NCLOB (National Character Large Object)

Developers frequently encounter a wall when moving narrative fields (such as long free-text descriptions, audit logs, or extended system remarks) into external Oracle tables.

Scenario Result
Small Text Values Successfully inserted
Large Text Values Insert failed or data not stored
Oracle Column Type CLOB / NCLOB (Sufficient Capacity)
Error Reporting Highly Inconsistent

When the runtime environment does throw an error, it typically surfaces in the Domino console logs as:

Agent Manager: Agent 'DatabaseSyncAgent' error:
Error: Data overflow in field 'REMARK_TEXT', Connector 'oracle', Method -Insert-

In other instances, the operation completes without a critical fault code, yet the targeted row is completely missing from the Oracle table.

Deep-Dive Investigation & Findings

To isolate why a properly configured Oracle CLOB field would reject long text payloads, we analyzed the data lifecycle across four structural phases.

Phase 1: Payload & Multi-Byte Character Expansion

The problem payloads invariably consist of large volumes of multi-byte characters, such as Japanese Kanji/Kana. A classic pitfall in cross-platform architecture is treating character counts as byte sizes:

$$\text{10,000 Japanese Characters} \neq \text{10,000 Bytes}$$

Depending on the character sets and encoding schemes used during transit, a single character can expand to 2, 3, or even 4 bytes, aggressively scaling the footprint of the memory allocation buffer.

Phase 2: Dynamic SQL Construction Pitfalls

The original implementation built SQL statements dynamically via string concatenation:

' High-risk pattern for large payloads
sql = "INSERT INTO APP_DATA_TABLE VALUES ('" & recordId & "','" & largeText & "')"

When processed via standard text handling mechanisms (LCTYPE_TEXT), the connector parser tries to serialize the query payload inline. When these values exceed the standard Oracle VARCHAR2 handling boundary (approximately 4,000 bytes when evaluated as a regular SQL literal string), the database parser fails, yielding underlying driver errors like:

Error : ORA-24373: invalid length specified for statement

Phase 3: Hardware and Driver Architecture (Bitness)

When Oracle operates in a 64-bit space, deploying a legacy 32-bit Notes Client or Domino runtime introduces memory mapping and pointer translation friction within the database client layer. Aligning the entire execution pathway on a consistent architecture eliminates translation overhead during high-volume operations.

Root Cause Analysis (RCA)

The failure is not caused by the Oracle CLOB data type or storage limits. Rather, it is a transport buffer constraint at the middleware layer.

By treating the long payload as standard text (LCTYPE_TEXT), the LSXLC framework processes the payload through standard text handling and buffering mechanisms. When multi-byte expansion blows past driver limits, the system triggers a localized buffer overflow or drops the statement payload before it can be correctly written to the data stream.

The Solution: Streaming via Type Code 6 (LCTYPE_STREAM)

The resolution requires switching from traditional string allocation to Large Object Streaming. Within the LSXLC framework, this is achieved by specifying Type Code 6 (internally recognized as LCTYPE_STREAM) inside your field list compilation.

LCTYPE_TEXT vs. LCTYPE_STREAM

Feature LCTYPE_TEXT (Type Code 1) LCTYPE_STREAM (Type Code 6)
Memory Allocation Fixed-size traditional text buffer Dynamic pointer allocation chunking
Overflow Risk High (Higher risk when payload size exceeds connector, driver, or SQL statement handling limits) Low (Leverages native database LOB limits)
Payload Target Standard Alphanumeric Data Massive Multi-byte Strings / Objects

When a field is appended using Type Code 6, the connector alters its instructions to the Oracle Client. Instead of attempting to parse the string inline within the SQL statement, it instructs the Oracle driver to establish a native LOB locator channel. The massive text stream bypasses the SQL buffer parser completely, piping data cleanly straight into the database storage segment.

Key Stream Field Initialization Example

The critical change is defining the CLOB field with LSXLC streaming support (Type Code 6) before assigning the large text payload.

LotusScript code:

' Define CLOB field using LSXLC streaming
' Type Code 6 = LCTYPE_STREAM
Set fldRemarks = fldList.Append("LARGE_REMARKS", 6)

' Assign large text payload
fldRemarks.Text = largeText

Sample Code

The following LotusScript sample maps out a hardened, streaming-based insertion pattern using the native Oracle connector wrapper.

Option Public
Option Declare

UseLSX "*lsxlc"

Sub Initialize
    Dim LC_S As New LCSession
    Dim LC_Conn As LCConnection
    Dim fldList As New LCFieldList
    Dim fldDocID As LCField
    Dim fldRemarks As LCField
    Dim largeText As String
    Dim i As Integer

    On Error GoTo ErrorHandler

    ' 1. Allocate the native Oracle connection object
    Set LC_Conn = New LCConnection("oracle")

    ' Configure parameters matching your target database topology
    LC_Conn.Userid = "DB_USER"
    LC_Conn.Password = "your_secure_password"
    LC_Conn.Server = "your-oracle-server.domain.com:1521/your_service_name"

    LC_S.ClearStatus
    Print "Connecting to Oracle Database..."
    Call LC_Conn.Connect
    Print "Connected successfully!"

    ' 2. Target Table Schema Mapping
    LC_Conn.Metadata = "APP_DATA_TABLE"
    LC_Conn.Fieldnames = "RECORD_ID, LARGE_REMARKS"

    ' 3. Append fields to your operational field list
    Set fldDocID = fldList.Append("RECORD_ID", LCTYPE_TEXT)

    ' CRITICAL FIX: Type Code 6 enables LCTYPE_STREAM handling.
    ' This allows large CLOB payloads to be processed through LSXLC streaming.
    Set fldRemarks = fldList.Append("LARGE_REMARKS", 6)

    ' 4. Formulate test data payload
    fldDocID.Text = "REC" & Format(Now, "yymmddhhmmss")

    ' Dynamically build a ~10,000+ character multi-byte test pattern
    largeText = "START:"
    For i = 1 To 1000
        largeText = largeText & " [10BYT*] "
    Next
    largeText = largeText & ":END"

    fldRemarks.Text = largeText

    ' 5. Native Stream Insertion
    Print "Streaming payload to Oracle CLOB..."
    Call LC_Conn.Insert(fldList, 1, 1)
    Print "Data committed successfully!"

    ' 6. Graceful Disconnect & Resource Cleanup
    LC_Conn.Disconnect
    Set LC_Conn = Nothing
    Set fldList = Nothing

    Print "Success! Stream complete without truncation or ORA errors."
    Exit Sub

ErrorHandler:
    MsgBox "LotusScript Error: " & Error$ & " on line " & CStr(Erl), 16, "Integration Failure"

    ' Fault-tolerant emergency cleanup block
    On Error Resume Next
    If Not LC_Conn Is Nothing Then
        Call LC_Conn.Disconnect
    End If
    Exit Sub
End Sub

Validation & Verification Checklist

To deploy this architecture optimization successfully, execute these validation rules within your test environment:

Step 1: Ensure Environmental Bitness Alignment

Verify that your execution tree matches from top to bottom. Do not mix 32-bit and 64-bit execution runtimes.

Notes Client / Domino Server (64-bit)
        ↓
Oracle Client Layer (64-bit)
        ↓
System ODBC DSN / Driver (64-bit)

Step 2: Establish the Test Schema

Ensure the destination column is explicitly deployed as a Long Object type:

CREATE TABLE APP_DATA_TABLE
(
    RECORD_ID VARCHAR2(50) PRIMARY KEY,
    LARGE_REMARKS CLOB
);

Step 3: Audit Storage Post-Run

After executing the streaming script, verify that the characters were committed down to the exact byte by querying the storage length directly in Oracle SQL*Plus:

SELECT RECORD_ID, LENGTH(LARGE_REMARKS)
FROM APP_DATA_TABLE
WHERE RECORD_ID LIKE 'REC%';

Expected Output

RECORD_ID          LENGTH(LARGE_REMARKS)
-----------------------------------------
REC260623120000    10010

Performance and Memory Considerations

Using LCTYPE_STREAM for large CLOB/NCLOB data handling provides improved reliability when processing large text payloads, especially with multi-byte Unicode content.

Key considerations:

  • Streaming avoids constructing large SQL statements containing complete text payloads in memory.

  • Large CLOB data is transferred through the LSXLC streaming mechanism instead of traditional text buffering.

  • Memory consumption is more predictable compared to loading and concatenating large text values into SQL statements.

  • The approach reduces the risk of failures caused by buffer limitations during large data transfers.

Actual performance improvements depend on multiple factors, including payload size, network latency, Oracle configuration, client driver version, and available system resources.

Key Lessons Learned

Storage Capacity ≠ Transfer Capacity

Just because a destination database field can hold gigabytes of data does not mean your communication middleware can safely push that data using standard alphanumeric variables.

Account for Unicode Multi-byte Expansion

Design integrations around byte weight, not visible character counts. This is non-negotiable for enterprise deployments running CJK (Chinese, Japanese, Korean) locales.

Abandon SQL Concatenation for LOBs

String concatenation forces database query parsers to analyze long strings inline. Use explicit field lists (LCFieldList) and object mapping instead.

Isolate the Entire Infrastructure Pathway

When data vanishes, look past the target database schema. Evaluate buffer boundaries inside the application logic, connector layer, driver library, and client architecture.

4 Likes
2

Very Very informative blog.

Thank you