Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




The Next_Item_Type() Function
Oracle Tips by Burleson

The Next_Item_Type() function is used when unpacking a pipe that can contain different types of values in the same positions. For instance, a message might contain either a company name or a company ID number in the same position. Depending on the value returned from this function, the next portion of the message will be placed into a character or number variable.

This function returns an integer value that indicates the datatype of the next section of the message. These values are listed in Table 9.2.

Table 9.2 Return values for the Next_Item_Type() function.

Return Value

Datatype Of Next Message Section


There are no more values in the message











This function does not have any parameters.

The Pack_Message() Procedure

The Pack_Message() procedure is used to add information to a message. The procedure is overloaded. The different implementations of the procedure are as follows:

PROCEDURE Pack_Message (item IN     varchar2)
PROCEDURE Pack_Message (item IN     date)
PROCEDURE Pack_Message (item IN     number)
PROCEDURE Pack_Message (item IN     raw)
PROCEDURE Pack_Message (item IN     ROWID)

Each implementation of this procedure is designed to add data of a specific type to the message that will be sent over the pipe.

The Purge() Procedure

The Purge() procedure is used to completely empty the pipe, thus freeing the memory used by the pipe when it is removed from the SGA. Pipes are not removed from the SGA if they contain messages. Therefore, it is critical that unnecessary pipes are emptied using this procedure.

The procedure accepts a single parameter:

PROCEDURE Purge (pipename IN     varchar2)

The pipename parameter holds the name of an existing pipe.

This procedure empties the pipe by calling the Receive_Message() function repeatedly. Therefore, the values in the local buffer will probably change if you use the Purge() procedure.


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter