1 / 15

Intersession Communication Oracle Database PL/SQL 10g Programming

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:

allene
Download Presentation

Intersession Communication Oracle Database PL/SQL 10g Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Intersession CommunicationOracle Database PL/SQL 10g Programming Chapter 11

  2. Intersession Communication • Intersession Communication Definition • DBMS_PIPE Built-in Package • DBMS_ALERT Built-in Package Oracle Database PL/SQL 10g Programming (Chapter 11)

  3. 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)

  4. Intersession CommunicationDBMS_PIPE: Local Buffer Oracle Database PL/SQL 10g Programming (Chapter 11)

  5. 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)

  6. 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)

  7. Intersession CommunicationDBMS_PIPE: SGA Named Pipe Oracle Database PL/SQL 10g Programming (Chapter 11)

  8. 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)

  9. 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)

  10. 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)

  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)

  12. 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)

  13. 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)

  14. 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)

  15. Summary • Database Trigger Concepts • DDL Triggers • DML Triggers • Instead-of Triggers • System or Database Event Triggers Oracle Database PL/SQL 10g Programming (Chapter 11)

More Related