DBMS_OUTPUT: Comparison with orafce and SET SERVEROUTPUT implementation

[rophy]

Background

IvorySQL has a native DBMS_OUTPUT implementation. This issue documents a comparison with orafce's implementation and identifies the remaining gap: SET SERVEROUTPUT ON support.

Architecture Comparison

Aspect	IvorySQL	orafce
Integration	Built into server (contrib extension)	Standalone extension
Package style	True Oracle CREATE PACKAGE syntax	PostgreSQL schema with functions
Buffer structure	Linked list of lines	Single contiguous char buffer
Memory management	Dedicated MemoryContext with per-line alloc/free	Single buffer in TopMemoryContext

Buffer Design

IvorySQL (Linked List)

typedef struct DbmsOutputLine {
    struct DbmsOutputLine *next;
    int len;           // -1 for NULL
    char data[FLEXIBLE_ARRAY_MEMBER];
} DbmsOutputLine;

• ✅ O(1) append and pop
• ✅ Memory freed immediately when lines consumed
• ✅ No buffer compaction needed
• ✅ Supports true NULL lines (distinct from empty string)

orafce (Contiguous Buffer)

static char *buffer = NULL;
static int buffer_size, buffer_len, buffer_get;

• ✅ Simpler implementation
• ❌ Must track "already retrieved" offset
• ❌ No memory reclaim until buffer reset
• ❌ NULL lines not distinguishable from empty

Feature Comparison

Feature	IvorySQL	orafce
PUT_LINE	✅	✅
PUT / NEW_LINE	✅	✅
GET_LINE	✅	✅
GET_LINES	✅	✅
ENABLE / DISABLE	✅	✅
SERVEROUTPUT	❌	✅ (function call)
NULL line support	✅	❌
Max line length (32767)	✅ ORU-10028	❌
Max buffer size	Unlimited option	1MB cap
ORU-10027 (buffer overflow)	✅	✅
DISCARD ALL support	✅	❌
True PACKAGE syntax	✅	❌

SERVEROUTPUT Differences

Implementation	Syntax	Mechanism
Oracle	SET SERVEROUTPUT ON	SQL*Plus client command
orafce	SELECT dbms_output.serveroutput(true)	Function that auto-flushes via NOTICE messages
IvorySQL	N/A	Requires explicit GET_LINE/GET_LINES calls

Summary

IvorySQL's implementation is more Oracle-compatible and more memory-efficient:

• ✅ True CREATE PACKAGE syntax (not just schema-qualified functions)
• ✅ Proper NULL line handling (distinct from empty strings)
• ✅ Oracle-compatible error codes (ORU-10027, ORU-10028)
• ✅ 32,767 byte line length limit enforcement
• ✅ DISCARD ALL/PACKAGES support via ProcessUtility hook
• ✅ Better memory efficiency with linked-list design

The only missing feature is SET SERVEROUTPUT ON which would require psql client modifications to match Oracle SQL*Plus syntax exactly.

Proposed SET SERVEROUTPUT Implementation

Oracle's syntax:

SET SERVEROUT[PUT] {OFF | ON} [SIZE {n | UNLIMITED}] [FORMAT {WRAPPED | WORD_WRAPPED | TRUNCATED}]

Implementation approach:

1. Parse SET SERVEROUTPUT in psql's Oracle mode lexer (ora_psqlscanslash.l)
2. Track state in psql settings (serveroutput_enabled, serveroutput_size)
3. After each statement, if enabled, call dbms_output.get_lines() and print results
4. Call dbms_output.enable(size) when turning ON

This matches Oracle's behavior where SERVEROUTPUT is a client-side feature, not a server feature.

References

• orafce source: https://github.com/orafce/orafce
• orafce putline.c: https://github.com/orafce/orafce/blob/master/putline.c
• Oracle DBMS_OUTPUT docs: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_OUTPUT.html
• Oracle SET SERVEROUTPUT docs: https://www.oreilly.com/library/view/oracle-sql-plus-the/0596007469/re85.html