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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle CAST Function Tips

Oracle Tips by Burleson Consulting

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 .

CAST and ANYDATA 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:

DECLARE
  v NUMBER;
BEGIN
  a(CAST (v AS INTEGER));
END;

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

DECLARE
  v NUMBER;
BEGIN
  a(v::INTEGER);
END;

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

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

TO FROM
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;

CAST & MULTISET

 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:

 INSERT INTO
    COURSE (STUDENT_LIST)
    (CAST
       (MULTISET
           (SELECT
                 student_name,
                 student_address,
                 grade
             FROM
                 GRADE, STUDENT
             WHERE
                  GRADE.course_name = 'CS101'
                  AND
                  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:

 select
   'abcdef',
   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 dba poster
 

 
 
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.