Database Pipes have been around in Oracle Database since release 7. In memory, light weight, non-persistent, fast and a little low level. Somewhat brittle: the contents of a pipe is lost when the database instances crashes. Pipes have been used to share information from within a running database session with the outside world – within or outside the database – without having to perform a commit. Debugging and logging and also transfering data to a shell program to process and possibly write to file. Messages in pipes could be no larger than 4KB until 12.1 and up to 8KB as of 12.2..
Database pipes used to work in a queue like fashion: FIFO and remove on read. Pipes could be read from in multiple sessions. However, each message could be read only once.
In Oracle Database 23ai there is now a new type of pipe. It is called “singleton pipe”. It takes a single message, that can be up to 32KB. The message can be read multiple times, by the same session and/or by different sessions.
The message disappears from the pipe:
- when its “shelflife” expires
- when it is explicitly purged from the pipe (and/or the pipe is removed)
- when a fresh message replaces it
- when the database instance is stopped (the message is still held in SGA memory)
Accessing the message in a singleton pipe is a very fast in memory operation – no diskaccess required. Because a singleton pipe message can be accessed many times from many concurrent database sessions, it remind me of global application context. These application context values are held in memory, shared across sessions and easily accessible in SQL (SYS_CONTEXT) and have a maximum size of 4000. The security around global application context is somewhat similar to the singleton pipe: either a single user (or client id) can access the global application context (albeit from multiple sessions) or all users can. There is no in between permission model. For singleton pipe: either the pipe is private (only accessible to sessions created for the same user as the one that created the pipe) or the pipe is public (accessible to all sessions).
The message in the singleton pipe can be explicitly put in the pipe by a call to dbms_pipe.send_message. Alternatively, a cache-refresh function can be specified when a call is made to dbms_pipe.receive_message. When the pipe does not contain a valid message (no message at all or one that has expired) the function is invoked to put the message into the pipe and return it to the caller.
A message in a singleton pipe can contain many different “attributes”: by multiple calls to dbms_pipe.pack_message, followed by a single call to dbms_pipe.send_message, a composite message consisting of multiple parts is put in the cache/pipe, Using dbms_pipe.unpack_message these elements can be picked out of the received message.
Alternatively (or additionally), a string representing a JSON document can be put in the singleton pipe . Obviously, this document can contain many different properties, nested collections etc.
A quick example:
- as ADMIN, grant execute on dbms_pipe to SCOTT
- as SCOTT (from here on all steps are performed as SCOTT) create a new public singleton pipe called MY_CACHE
- send a message to the pipe
- receive a message from the pipe. and again. and again. (and not just as SCOTT, also as ADMIN or any other user)
- remove the pipe
By putting a JSON document in the singleton pipe, we can cache and share a far more interesting collection of data. From multiple configuration settings to the data sets used for validation or enrichment. This data can be calculated, retrieved from HTTP sources or read from Cloud Object Storage. Or anything else you can think of.
The steps are largely the same of course, just reading and parsing the data is a little different.
Put a JSON string in the pipe:
Create a PL/SQL function to return the contents of the singleton pipe – just as a VARCHAR2:
Retrieve the pipe contents in SQL – no interpretation of the JSON yet
Retrieve individual property values – using JSON_VALUE:
Retrieve a property from a record in the nested staff collection
Finally to read the employee records from the nested collection in property staff in a SQL query:
Resources
Oracle Database 23aui Docs on Singleton Pipe – https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-singleton-pipe.html#GUID-0C8C9D05-16D9-4003-82B8-B15A5E5D2B68
Oracle Database docs SQL/JSON Path Expression Syntax – https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/json-path-expressions.html#GUID-7B610884-39CD-4910-85E7-C251D342D879
