Oracle Built−in Packages- P36: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk message_out message unpacked message_out END message unpacked This is my message PL SQL procedure successfully completed. The only difference in the second test was that RESET_BUFFER was called prior to packing and unpacking the message. Furthermore subsequent executions of the first test block completed successfully even though RESET_BUFFER was not explicitly called. So the real answer to the first question appears to be that packed messages can be unpacked prior to sending as long as RESET_BUFFER has been previously called in the session. This confuses me and I don t like the fact that Oracle does not expose more details about the inner workings of the local message buffer in relation to packing and unpacking messages. The second question Do PACK_MESSAGE and UNPACK_MESSAGE use a common buffer is a little trickier. It actually occurred to me only after exploring the first question about unpacking a packed buffer before sending. I wondered whether packing and unpacking messages could happen independently of each other in the message buffer or whether the session message buffer was essentially a single slot with room for only one message. I expanded my earlier test script into the following Filename on companion disk DECLARE test_pipename VARCHAR2 30 OPBIP_TEST_PIPE2 call_status INTEGER message1_out VARCHAR2 2000 message2_out VARCHAR2 2000 BEGIN make sure pipe is empty call_status test_pipename test_pipename pack and send first message This is message one call_status test_pipename call status sendl TO_CHAR call_status now pack second message without sending This is message two receive unpack and print message call_status test_pipename call status receivel TO_CHAR call_status .
