150 likes | 360 Views
Intersession Communication Oracle Database PL/SQL 10g Programming. Chapter 11. Intersession Communication. Intersession Communication Definition DBMS_PIPE Built-in Package DBMS_ALERT Built-in Package. Intersession Communication DBMS_PIPE. Interactive intersession communication is:
E N D
Intersession CommunicationOracle Database PL/SQL 10g Programming Chapter 11
Intersession Communication • Intersession Communication Definition • DBMS_PIPE Built-in Package • DBMS_ALERT Built-in Package Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE • Interactive intersession communication is: • The process of sending messages from one open session in the database to another. • The process works by accessing a messaging buffer, known as a pipe. • Pipes are FIFO (First-in and last-out) queues. • The pipes can be local, private, and public. • The process uses the DBMS_PIPE built-in package to send and receive messages. Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE: Local Buffer Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE: Local Buffer Write DECLARE message VARCHAR2(30); success INTEGER; BEGIN message := DBMS_PIPE.UNIQUE_SESSION_NAME; DBMS_PIPE.RESET_BUFFER; DBMS_PIPE.PACK_MESSAGE(message); DBMS_OUTPUT.PUT_LINE('Message ['||message||'] '); END; / Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE: Local Buffer Read DECLARE message VARCHAR2(30); success INTEGER; BEGIN DBMS_PIPE.UNPACK_MESSAGE(message); DBMS_OUTPUT.PUT_LINE('Message ['||message||'] '); END; / Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE: SGA Named Pipe Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE: Create a Pipe DECLARE message_pipe VARCHAR2(30) := 'PLSQL$MESSAGE_INBOX'; success_size INTEGER := 2000; retval INTEGER; BEGIN retval := DBMS_PIPE.CREATE_PIPE(); IF (retval = 0) THEN DBMS_OUTPUT.PUT_LINE('Message ['||message||'] '); END IF; END; / '||message||' Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE: Write to a Pipe DECLARE flag INTEGER; BEGIN DBMS_PIPE.PURGE('PLSQL$MESSAGE_INBOX'); DBMS_PIPE.PACK_MESSAGE('MESSAGE'); DBMS_PIPE.SEND_MESSAGE('PLSQL$MESSAGE_INBOX'); END; / Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_PIPE: Read from a Pipe DECLARE message VARCHAR2(4000); flag INTEGER; BEGIN DBMS_PIPE.RESET_BUFFER; -- Force immediate read of pipe. flag := DBMS_PIPE.RECEIVE_MESSAGE('PLSQL$MESSAGE_INBOX',0); DBMS_PIPE.UNPACK_MESSAGE('MESSAGE'); DBMS_PIPE.SEND_MESSAGE('PLSQL$MESSAGE_INBOX'); END; / '||message||' Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_ALERT • Event-driven intersession communication is: • DBMS_ALERT built-in sends messages through a pipe: • The transaction-based DBMS_ALERT lets a database trigger send an alert message to an implicit public pipe. • DBMS_ALERT uses a publish-and-subscribe pattern where multiple sessions can register interest in an alert. • Subscribers, those who register: • Wait on the event for a polling interval. • Read ONLY the last message received by the pipe because it is a single element queue. Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_ALERT: Publish on Event CREATE OR REPLACE TRIGGER signal_message AFTER INSERT OF message_id, message ON messages FOR EACH ROW BEGIN IF :old.message_id IS NULL THEN DBMS_ALERT.SIGNAL('EVENT_MESSAGE_QUEUE',:message); END IF; END; / '||message||' Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_ALERT: Subscribe BEGIN -- Subscribe to an alert event. DBMS_ALERT.REGISTER('EVENT_MESSAGE_QUEUE'); END; / Oracle Database PL/SQL 10g Programming (Chapter 11)
Intersession CommunicationDBMS_ALERT: Catch DECLARE message VARCHAR2(4000); flag INTEGER; BEGIN DBMS_ALERT.WAITONE('EVENT_MESSAGE_QUEUE' ,message,status,30); END; / '||message||' Oracle Database PL/SQL 10g Programming (Chapter 11)
Summary • Database Trigger Concepts • DDL Triggers • DML Triggers • Instead-of Triggers • System or Database Event Triggers Oracle Database PL/SQL 10g Programming (Chapter 11)