DBMS_PIPE is an Oracle PL/SQL package that provides a mechanism for inter-process communication within the database. It allows different sessions to exchange data by writing to and reading from a pipe, a temporary memory area. This is particularly useful for implementing messaging systems or for synchronizing processes within the database.
Key features include:
- Send_Message: Allows a session to send data into a pipe.
- Receive_Message: Enables another session to retrieve data from the pipe.
- Timeouts: Supports waiting for a specified duration when reading messages.
- Flexible Data Types: Permits passing various data types in messages.
The DBMS_PIPE package is a versatile tool for coordinating services written in different programming languages. By acting as a messaging mechanism, it allows Oracle sessions to exchange data with external services in real-time. For instance, you can use DBMS_PIPE to send a request from the Oracle database to a service written in PHP, facilitating seamless integration between the database and the external application.
In this entry, we will explore an example of how to command a remote service written in PHP using pipes for communication.
Private and public pipes.
In DBMS_PIPE, pipes can be either private or public, which determines their visibility and accessibility. A private pipe is only accessible within the session that created it, making it ideal for scenarios where communication is confined to a single user or process. In contrast, a public pipe can be accessed by multiple sessions, enabling inter-session communication and coordination between different users or processes. By default, pipes are public unless explicitly defined as private. Proper management of pipe access and security is essential when working with public pipes to avoid unauthorized usage or data conflicts.
A basic sample of setting up, using and removing a pipe.
The following snippet demonstrates the fundamental usage of DBMS_PIPE.
DECLARE
write_result INTEGER;
read_result INTEGER;
message VARCHAR2(100);
pipe_name CONSTANT VARCHAR2(30) := 'MY_PIPE';
BEGIN
-- Step 1: Create the pipe
IF DBMS_PIPE.CREATE_PIPE(
pipename => pipe_name,
maxpipesize => 10240,
private => FALSE
) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Pipe created successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Failed to create pipe.');
RETURN;
END IF;
-- Step 2: Write to the pipe
write_result := DBMS_PIPE.PACK_MESSAGE('Hello from Oracle!');
write_result := DBMS_PIPE.SEND_MESSAGE(pipe_name);
IF write_result = 0 THEN
DBMS_OUTPUT.PUT_LINE('Message sent successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Failed to send message.');
END IF;
-- Step 3: Read from the pipe
read_result := DBMS_PIPE.RECEIVE_MESSAGE(
pipename => pipe_name,
timeout => 10 -- Wait up to 10 seconds for a message
);
IF read_result = 0 THEN
DBMS_PIPE.UNPACK_MESSAGE(message);
DBMS_OUTPUT.PUT_LINE('Message received: ' || message);
ELSE
DBMS_OUTPUT.PUT_LINE('Failed to receive message or timeout occurred.');
END IF;
-- Step 4: Remove the pipe
IF DBMS_PIPE.REMOVE_PIPE(pipe_name) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Pipe removed successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Failed to remove pipe.');
END IF;
END;
DBMS_PIPE.PACK_MESSAGE is used to add data to a pipe before sending it. You can pack multiple pieces of data (of various types) into a single message, which will then be sent as a unit using DBMS_PIPE.SEND_MESSAGE.
DBMS_PIPE.UNPACK_MESSAGE retrieves data from a pipe after receiving a message using DBMS_PIPE.RECEIVE_MESSAGE. If the message contains multiple pieces of data, they can be unpacked in the same order they were packed.
To list the pipes previously created in an Oracle database, you can query the V$DB_PIPES dynamic performance view. This view contains information about all currently existing pipes in the database.
SELECT NAME AS PIPE_NAME FROM V$DB_PIPES;
Commanding a remote PHP service.
In the following steps, we will demonstrate how to send a request to a PHP service using DBMS_PIPE. The process involves a PL/SQL package to send a message from the database to a pipe and a PHP script that listens to the pipe, processes the request, and provides a response.
This approach is based on a real-world application I developed to dynamically interact with external APIs. It facilitated seamless integration with third-party systems and legacy applications, streamlining communication and extending the functionality of existing systems.
create or replace PACKAGE BODY SII AS
-------------------------------------------------------------------------------------------
Procedure CreateSIIPipe AS
n number;
BEGIN
n := DBMS_PIPE.CREATE_PIPE(pipe_name);
dbms_output.put_line('Pipe ' || pipe_name || ' created. ' || to_char(n));
END CreateSIIPipe;
-------------------------------------------------------------------------------------------
Procedure SendSIIPipe(cCommand in varchar2) AS
n number;
begin
DBMS_PIPE.PACK_MESSAGE(cCommand);
n := DBMS_PIPE.SEND_MESSAGE(pipe_name);
end SendSIIPipe;
-------------------------------------------------------------------------------------------
Procedure RemoveSIIPipe AS
n number;
BEGIN
n := DBMS_PIPE.REMOVE_PIPE(pipe_name);
dbms_output.put_line('Pipe ' || pipe_name || ' removed. ' || to_char(n));
END RemoveSIIPipe;
-------------------------------------------------------------------------------------------
Procedure SIIRequest(VATSender in number, IssuedReleased in varchar2, Invoice in varchar2) is
n number;
Request varchar2(200);
BEGIN
Request := '{"VATSender":"' || trim(to_char(VATSender)) || '"';
Request := Request || ', "IssuedReleased":"' || IssuedReleased || '"';
Request := Request || ', "Invoice":"' || Invoice || '"}';
sii.sendsiipipe(Request);
END SIIRequest;
-------------------------------------------------------------------------------------------
Procedure SIIStopService is
begin
sii.sendsiipipe('**exit');
end SIIStopService;
END SII;
create or replace PACKAGE SII AS
pipe_name varchar2(30) := 'SII_PIPE';
Procedure CreateSIIPipe;
Procedure SendSIIPipe(cCommand in varchar2);
Procedure RemoveSIIPipe;
Procedure SIIRequest(VATSender in number, IssuedReleased in varchar2, Invoice in varchar2);
Procedure SIIStopService;
END SII;
The following PHP script acts a daemon listening request until the command **exit is found.
SII_daemon.php
<?php
require 'SIIcore.php';
function DBconnect() {
$db = "(DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = <server_name>)(PORT = 1521)) (CONNECT_DATA = (SID = <sid_name>)))";
$conn = oci_connect("<user>", "<passwd>", $db);
if (!$conn) {
$m = oci_error();
echo $m['message'], "\n";
exit;
};
return($conn);
}
echo "Starting SII service...\n";
$conn = DBconnect();
if (!$conn) {
$m = oci_error();
echo $m['message'], "\n";
exit;
};
$pipename = 'SII_PIPE';
$n = 0;
$stid = oci_parse($conn, 'BEGIN :n := DBMS_PIPE.RECEIVE_MESSAGE(:pipe_name); DBMS_PIPE.unpack_message(:message); END;');
oci_bind_by_name($stid, ':n' , $n);
oci_bind_by_name($stid, ':pipe_name', $pipename);
oci_bind_by_name($stid, ':message', $message, 100);
echo "Connected to DDBB\n";
$stop = false;
while (! $stop) {
oci_execute($stid);
if ($message == '**exit') {
$stop = true;
} else {
$request = json_decode($message);
// var_dump($request);
echo "Running request........\n";
echo " VATSender. : " . $request->VATSender . "\n";
echo " IssuedReleased : " . $request->IssuedReleased . "\n";
echo " Invoice : " . $request->Invoice . "\n";
echo "-----------------------\n";
SII($request->VATSender, $request->IssuedReleased, $request->Invoice);
echo "\n";
}
sleep(5);
}
oci_free_statement($stid);
oci_close($conn);
echo "Disconnected from DDBB\n";
echo "SII service is down\n";
?>
Conclusion and a final caveat.
Despite its utility, it’s important to note that DBMS_PIPE is generally not recommended for high-performance systems due to its reliance on in-memory structures and potential contention issues. Alternatives like DBMS_AQ (Advanced Queuing) might be better for complex or high-throughput messaging scenarios.
Oracle Essentials: Oracle Database 12c
$49.99 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Oracle 19c on Red Hat 8: Stand-Alone, Dataguard, Cluster RAC 2 nodes
$16.00 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Taming Oracle 19c RMAN Backup and Restore (Oracle Simplified Book 1)
$4.99 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)