Oracle Built−in Packages- P37: 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 private_IN IN BOOLEAN DEFAULT TRUE RETURN BOOLEAN encapsulates and returns FALSE if ORA-23322 is raised indicating the pipename exists and is not removable by the caller FUNCTION closepipe pipename_IN IN VARCHAR2 RETURN BOOLEAN purges all pipes the caller can access PROCEDURE purge_all_pipes END dbpipe Unpack_to_tbl and pack_from_tbl procedures The two procedures unpack_to_tbl and pack_from_tbl implement the generic unpack and pack functionality. They use PL SQL tables of records based on message_tbltype which is designed to hold an ordered list of items of any datatype. Each row in a table of type message_tbltype contains two data values an entry in the item_type field indicating the type of this message item as returned by and an entry in the field of the corresponding datatype with the value of this message item. The unpack_to_tbl procedure unpacks all items in a newly received message into a message table indexing them in the table by their unpack order. The pack_from_tbl procedure takes a message table loaded in this fashion and repacks the original message into the message buffer in index order. The unpack_to_tbl procedure can also optionally use the DBMS_OUTPUT built-in package described in Chapter 6 Generating Output from PL SQL Programs to display the message unpacked. Here are the full package bodies for unpack_to_tbl and pack_from_tbl. Note how unpack_to_tbl grew out of the example code for the function. Filename on companion disk PROCEDURE unpack_to_tbl message_tbl_OUT OUT message_tbltype display_TF IN BOOLEAN FALSE IS NOTE this procedure should only be called after a successful call to empty table to flush output table on exception null_message_tbl message_tbltype temp display variable extra long to account for RAWTOHEX conversion that can double size temp_varchar2 VARCHAR2 8186 next_item .
