Oracle Built−in Packages- P34: 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 NOTE The numeric datatype identifiers in the ORA-06559 message do not match the item type values returned by the NEXT_ITEM_TYPE function. The function The NEXT_ITEM_TYPE function returns a number indicating the datatype of the next item in the user session s message buffer. The header for this function follows FUNCTION RETURN INTEGER The return value will be one of the following Item Type Description 0 No more items in buffer 6 NUMBER 9 VARCHAR2 11 ROWID 12 DATE 23 RAW The program does not raise any package exceptions. Example The following PL SQL block contains an inline procedure called unpack_all_items which can unpack any message and display its contents using DBMS_OUTPUT. The unpack_all_items procedure uses NEXT_ITEM_TYPE to determine which version of UNPACK_MESSAGE to call for each item. Filename on companion disk set serveroutput on size 100000 DECLARE call_stat INTEGER PROCEDURE unpack_all_items IS declare temp variables of all message item types temp_varchar2 temp_date temp_number temp_rowid temp_raw VARCHAR2 2000 DATE NUMBER ROWID RAW 2000 next_item INTEGER 0 BEGIN next_item unpack by item type and convert to varchar2 WHILE next_item 0 LOOP IF next_item 9 THEN temp_varchar2 Packing and Unpacking Messages 156 Appendix A What s on the Companion Disk ELSIF next_item 6 THEN temp_number temp_varchar2 NUMBER TO_CHAR temp_number ELSIF next_item 11 THEN temp_rowid temp_varchar2 ROWID ROWIDTOCHAR temp_rowid ELSIF next_item 12 THEN temp_date temp_varchar2 DATE TO_CHAR temp_date YYYY MM DD HH24 MI SS ELSIF next_item 23 THEN temp_raw temp_varchar2 RAW RAWTOHEX temp_raw ELSE temp_varchar2 Invalid item type TO_CHAR next_item END IF display item and determine next item
