Under terms of this task we'll add a subset of the system package SYS.DBMS_OUTPUT.

Overview

Oracle documentation describing DBMS_OUTPUT can be found here:
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_OUTPUT.html

The main idea of how DBMS_OUTPUT works is:

Messages submitted by DBMS_OUTPUT.PUT_LINE() are not sent to the client until the sending subprogram (or trigger) completes. There is no a way to flush output during the execution of a procedure.

Thus lines are collected into a server side buffer, which at the end of the current user statement can be fetched to the client side using another SQL statement and then can be read using a regular MariaDB Connector-C API. No changes in the client-protocol are needed.

Oracle's SQLPlus uses the procedure DBMS_PACKAGE.GET_LINES() to fetch the output to the client side as an array of strings.

JDBC users propose in various online forums the same approach. See the "Links" section of this MDEV. JDBC users say that GET_LINES() is preferrable, because it's more efficient than individual GET_LINE() calls.

Package routines

Let's implement all routines supported by Oracle except GET_LINES():

• Procedure ENABLE() - enable the routines.
• Procedure DISABLE() - disable the routines. If the package is disabled, all calls to subprograms, such as PUT() and PUT_LINE(), are ignored (or exit immediately without doing anything).
• Procedure PUT_LINE() - submit a line into the internal buffer.
• Procedure PUT() - submit a partial line into the buffer.
• Procedure NEW_LINE() - terminate a line submitted by PUT().
• Procedure GET_LINE() - read one line (the earliest) from the buffer. When a line is read by GET_LINE() it's automatically removed from the buffer.
• Procedure GET_LINES() - read all lines (as an array of strings) from the buffer - won't be implemented under terms of this MDEV.

The package should start in the disabled mode, so an explicit enabling is needed:

CALL DBMS_OUTPUT.ENABLE();

Details

If a call for GET_LINE or GET_LINES did not retrieve all lines, then a subsequent call for PUT, PUT_LINE, or NEW_LINE discards the remaining lines (to avoid confusing with the next message).

Data types used in the package DBMS_OUTPUT

Oracle implementation uses two data types:

• Lines are collected in a buffer of this data type:

  TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

• The function GET_LINES() returns an array of strings of this data type:

  TYPE DBMSOUTPUT_LINESARRAY IS VARRAY(2147483647) OF VARCHAR2(32767);

As MariaDB does not support yet arrays (the work is in progress: MDEV-34319), we can use another storage for messages for now.

A temporary table looks like a good option:

CREATE TEMPORARY TABLE sys._dbms_output_table

(

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  line VARCHAR(16380) CHARACTER SET utf8mb4

) ENGINE=HEAP;

This table can be created when a DBMS_OUTPUT routine is called for the first time in the sessions. In most cases it'll be this statement:

CALL DBMS_OUTPUT.ENABLE();

Using a HEAP table though with one record for one line is not a good choice, because HEAP does not support variable length records. Using 32K for every line will probably be as waste of memory.

Possible workarounds:

• use a temporary table with ENGINE=MyISAM
• or split long lines into short chunks, for example 80 characters each, then concatenate them back on a GET_LINE() call.

Fetching lines on the client side

MariaDB does not have array data types in C/C++ connector, so we can't take advantage of GET_LINES() even when MDEV-34319 is done.

Options how to get lines on the client side are:

• Fetch all lines in a single col and single row query:

  SELECT GROUP_CONCAT(CONCAT(line,'\n') ORDER BY id) FROM sys._dbms_output_table;

  But there is a chance to get outside of the max_allowed_packet limit.

• Fetch lines using individual 'CALL GET_LINE(?,?)' in a loop.
• Fetch lines using a single SELECT query (followed by a loop of mariadb_fetch_row()):

  SELECT line ORDER BY id FROM sys._dbms_output_table;

When implementing either of the ways, we should make sure the client program (mariadb) insert correct line endings to stdout:

• NL (on Linux)
• CR NL (on Windows)

Enabling DBMS_OUTPUT on the client side

Typing SET SERVEROUTPUT ON in Oracle's SQL*Plus client invokes:

CALL DBMS_OUTPUT.ENABLE (buffer_size => NULL/*unlimited buffer size*/);

Let's implement the same approach: Introduce a new command in MariaDB command line

SET SERVEROUTPUT ON;

It will:

• Send the CALL DBMS_OUTOUT.ENABLE() statement to the server
• Enable querying and displaying messages after each user query.

All existing MariadDB client commands do not have the word "SET". They are just:

...

nopager;

notee;

pager ... ;

print;

prompt;

quit;

rehash;

...

For Oracle compatibility purposes "SET SERVEROUTPUT ON" should probably start with the word "SET", unlike the other client commands.

Another option would be to implement a MariaDB style command first, then add "SET SERVEROUTPUT ON" if users complain.

Yet another option would be to implement both.

This will be decided during coding.

Limits:

Oracle has the following limits:

• The maximum individual line length (sent to DBMS_OUTPUT) is 32767 bytes.
• The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.

We'll also implement some limits, either using the total size of all rows:

SELECT DATA_LENGTH FROM information_schema.tables

WHERE table_schema='sys'

  AND table_name='_dbms_output_table'

  AND table_type='TEMPORARY';

or using the row count:

SELECT TABLE_ROWS FROM information_schema.tables

WHERE table_schema='sys'

  AND table_name='_dbms_output_table'

  AND table_type='TEMPORARY';

This will be decided when we chose the exact storage for messages.

Installation and privileges

Like all other bootstrap scripts, the script creating DBMS_OUTPUT:

• will be be put into a new separate file /scripts/dbms_ouput.sql in the source directory
• will be installed into /share/dbms_ouput.sql of the installation directory

The long term plan is:

• The script creating the package DBMS_OUTPUT into the database SYS should be run by a user with super privileges, presumably from mariadb-install-db.
• EXECUTE permission on this package should be granted to all users.

Note, Oracle creates a public synonym DBMS_OUTPUT, so the package can be accessed from any schema without having to add the SYS. qualifier. MariaDB does not have synonyms yet (synonyms will be added later by MDEV-19149). We have options for now:

• Add the database "SYS." into the default search patch (SET PATH is being added by MDEV-34391 and will be ready very soon).
• Or have users install DBMS_OUTPUT into every database where it's needed. In this case super privileges are not needed.