Oracle 10g PL/SQL New Features - New/Changed Datatypes And New PL/SQL Packages

Along with all the other Oracle 10g PL/SQL new features there have been changes to existing datatypes and new ones introduced as well as ome new PL/SQL packages introduced. Here we provide a brief overview of these changes.

New Floating-Point Types

Two new datatypes BINARY_FLOAT and BINARY_DOUBLE were introduced in Oracle 10g release 1 to improve the performance of intensive computations involving floating-point numbers. These types store numbers in the IEE 754 format which is often supported at the hardware level and are commonly used in scientific applications.

Constants of these types are also supported. BINARY_FLOAT constants are suffixed with f (eg. 0.5f) and 

BINARY_DOUBLE constants are suffixed with d (eg. 1.000025d).

Change to the BINARY_INTEGER Datatype

In Oracle 10g release 1, the BINARY_INTEGER datatype was changed to make it identical to the PLS_INTEGER datatypes which means these two types can now be used interchangeably.

One thing to be aware of though is that the overflow handling has also changed. In 10g and later versions, an exception will be raised if there is an overflow in calculations involving the BINARY_INTEGER datatype.

Conversion Between CLOB and NCLOB

Implicit conversion between CLOB and NCLOB datatypes are now allowed, but note that these can be very expensive in terms of i/o due to the potential sizes of these datatypes. Explicit conversion using the functions TO_CLOB and TO_NCLOB are still allowed.


This package was introduced in Oracle 10g release 1 and provides functions and procedures to enable the compression and decompression of binary data (either RAW or BLOB datatypes) as well as to add pieces to and extract pieces from an existing archive in much the same way as zip and unzip utilities at the o/s level.

UTL_MAIL Package

UTL_MAIL greatly simplifes the process of sending email from PL/SQL by providing three overloaded procedures which handle everything for you and optionally enable an attachment to be sent. 

This reduces the code for sending an email to just a single  procedure call. The three procedures are SEND_MAILSEND_ATTACH_RAW and SEND_ATTACH_VARCHAR2.

 Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're  missing out on a myriad of tips and techniques to help you work better, faster and smarter. Subscribe now and enhance your career.