Dare to use cast.

Have you ever wanted to convert one data type to other.

Most of the times we would rely on oracle's ability to implicitly convert them for you.

But if you want it fool proof take the task in your hand .

Here's How it's done.

CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.

You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.

Examples :

CAST(<string_or_column> AS <DATATYPE>)
 
-- The following examples use the CAST function 
-- with scalar datatypes:
 
SELECT CAST('22-OCT-1997' AS TIMESTAMP WITH LOCAL TIME ZONE) 
   FROM dual;
 
SELECT product_id, 
   CAST(ad_sourcetext AS VARCHAR2(30))
   FROM print_media;
 
 
-- This example casts a subquery:
 
SELECT s.custno, s.name,
   CAST(MULTISET(SELECT ca.street_address,   
                        ca.postal_code, 
                        ca.city, 
                        ca.state_province, 
                        ca.country_id
                 FROM cust_address ca
                 WHERE s.custno = ca.custno)
   AS address_book_t)
FROM cust_short s;