Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle CAST Function Tips

Oracle Database Tips by Donald Burleson

The Oracle CAST function converts one data type to another.  The CAST function can convert built-in and collection-typed values into other built-in or collection typed values. 

 CAST can convert a date or other unnamed operand (or a nested table or other named collection) into a type-compatible datatype or named collection.  For this use of CAST, type_name and/or operand must be of (or evaulate to) a built-in datatype or collection type .


When using CAST to convert an operand, the expr can also be either a built-in datatype or a collection type; however, it can be an instance of an ANYDATA type as well. When the expr is of an ANYDATA type, CAST attempts an extraction of the value of the ANYDATA expr and returns it if it matches the CAST target type.  If the there is no match to the CAST target type, NULL is returned. 

CAST with LOB Datatypes

 LOB datatypes are not directly supported by the CAST function.  Using CAST to convert CLOB values into a character datatypes or BLOB values into the RAW datatype results in the database converting the LOB value implicity to character or raw data.  Once this implicit conversion is done, the resulting value is CAST into the target datatype.  This process will throw an error if the resulting value is larger than the target type.

The Oracle docs note the syntax for Oracle CAST as follows:

CAST({ expr | MULTISET (subquery) } AS type_name)

With the Oracle CAST function a block could be re-written as:


On the other hand, the function could be re-written with Oracle CAST as:


Each of these uses of the CAST function will produce the same result.

Here are built-In Datatypes that Accept the CAST Conversions:

char, varchar2 number datetime / interval raw rowid, urowid nchar, nvarchar2
char, varchar2 X X X X X  
number X X        
datetime / interval X   X      
raw X     X    
rowid, urowid X       X  
nchar, nvarchar2   X X X X X

Here are examples of using the CAST function:  

  • convert string to date
    select cast ('1997-10-22' as date) from dual;

  • convert ROWID to char
    select * from t1 where cast (rowid as char(5)) = '01234';  

  • convert string to timestamp
    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;


 MULTISET must be specified when the CAST subquery results will return multiple rows.  In this situation,  the rows returned by the CAST function are the elements that comprise the collection value into which they are cast. Failure to specify MULTISET keyword, causes the subquery to be treated as a scalar subquery.

Conversely, use of MULTISET causes the return of a collection value from the results of the CAST subquery.

Use of CAST and MULTISET to get a collection value results in each item in the SELECT list that is passed to the CAST function to be converted to the attribute type of the target collection type.

The SQL operators CAST and MULITSET casts a multiple input data stream into the appropriate data types for the SQL operation:

                 GRADE, STUDENT
                  GRADE.course_name = 'CS101'
                  GRADE.student_name = STUDENT.student_name

Use of CAST on Results

The use of CAST does indeed resize or expand to the specified length. When using varchar2, CAST does not right pad with blank space.  An example of the use of CAST:

   cast('abcdef' as varchar2(3)) v3,
   length(cast('abcdef' as varchar2(3)) ) lv3,
   cast('abcdef' as varchar2(9)) v9,
   length(cast('abcdef' as varchar2(9)) ) lv9
from dual;

'ABCDEF' V3         LV3 V9               LV9
-------- --- ---------- --------- ----------
abcdef   abc          3 abcdef             6

Casting to a larger string is more visible in a view:

SQL> create view v as
   2  select cast('abcdef' as varchar2(9)) vc9, substr('abcdef',1,9) sub from dual;

View created.

SQL> desc v

Name              Null?    Type
----------------- -------- ------------
VC9                        VARCHAR2(9)
SUB                        VARCHAR2(6)

Do not use CAST if you need SUBSTR(char), TO_CHAR(number) or TO_CHAR(date) !





Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational