Oracle CAST Function
Version 12.1.0.2

Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
 
CAST with Dates
CAST as DATE data type CAST(<column_or_value> AS <data_type>)
SELECT CAST('01-JAN-2014' AS DATE) CDate
FROM dual;
 
CAST with Numbers
CAST as NUMBER data type CAST(<column_or_value> AS <data_type>)
SELECT 1 + CAST(3.14 * 0.152 AS NUMBER(10,7)) FLOATING
FROM dual;
 
CAST with Strings
CAST as VARCHAR2 data type CAST(<column_or_value> AS <data_type>)
SELECT object_name
FROM user_objects;

SELECT CAST(object_name AS VARCHAR2(30)) OBJ_NAME
FROM user_objects;
 
CAST With Timestamps
CAST as TIMESTAMP data type CAST(<column_or_value> AS <data_type>)
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) DTWTZ
FROM dual;
Cast a timestamp calculation result as a number of seconds CAST(<column_or_value> AS <data_type>)
SELECT ROUND(CAST(SYSTIMESTAMP AT TIME ZONE 'utc' AS DATE) - TO_DATE('15.03.2014', 'DD.MM.YYYY')) * 86400
FROM dual;
 
CAST with Varrays
Converting a Varray Type Column Into A Nested Table CAST(<column_or_value> AS <data_type>)
CREATE OR REPLACE TYPE district_t AS OBJECT (
region_no NUMBER(2),
title     VARCHAR2(35),
cost      NUMBER(7,2));
/

CREATE TYPE DistList_t AS TABLE OF district_t;
/

CREATE TYPE DistrictList AS VARRAY(10) OF district_t;
/

CREATE TABLE region_tab (
reg_id   NUMBER(2),
reg_name VARCHAR2(15),
district DistrictList);

set describe depth all linenum on indent on

desc region_tab

SELECT * FROM region_tab;

INSERT INTO region_tab
VALUES(30, 'Northwest',
DistrictList (District_t(1, 'Alaska', 3250),
District_t(2, 'Washington', 12350),
District_t(3, 'Oregon', 2750),
District_t(4, 'Idaho', 1425)));

INSERT INTO region_tab
VALUES(40, 'Southwest',
DistrictList (District_t(1, 'Arizona', 3250),
District_t(2, 'California', 12350),
District_t(3, 'Nevada', 2750),
District_t(4, 'New Mexico', 1425)));

SELECT CAST(s.district AS DistList_t)
FROM region_tab s
WHERE s.reg_id = 30;
 
CAST MULTISET with Collections
Using Multiset with a VARRAY CAST(MULTISET(<select statement>) AS <data_type>)
conn uwclass/uwclass

CREATE OR REPLACE TYPE cust_address_t
OID '53A970B3F5024BEC8EFD4F84CAD5E09E'
AS OBJECT (
street_address VARCHAR2(40),
postal_code    VARCHAR2(10),
city           VARCHAR2(30),
state_province VARCHAR2(2),
country_id     VARCHAR2(2));
/

CREATE OR REPLACE TYPE address_book_t AS TABLE OF cust_address_t;
/

CREATE TABLE cust_address (
custno         NUMBER(10),
street_address VARCHAR2(40),
postal_code    VARCHAR2(10),
city           VARCHAR2(30),
state_province VARCHAR2(2),
country_id     VARCHAR2(2));

INSERT INTO cust_address
VALUES (1,'123 Main St.','98040','Mercer Island','WA','US');
INSERT INTO cust_address
VALUES (2,'1 Broadway','10202','New York','NY','US');
INSERT INTO cust_address
VALUES (3,'2462 Edgar Crest','V6L 2C4','Vancouver','BC','CN');
COMMIT;

CREATE TABLE cust_short (
custno NUMBER(10),
name   VARCHAR2(30));

INSERT INTO cust_short VALUES (1,'Morgan');
INSERT INTO cust_short VALUES (2,'Kolk');
INSERT INTO cust_short VALUES (3,'Scott');

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;
Using Multiset with a PL/SQL Table CAST(MULTISET(<select statement>) AS <data_type>)
conn uwclass/uwclass

CREATE OR REPLACE TYPE project_table_t AS
TABLE OF VARCHAR2(25);
/

CREATE TABLE projects (
person_id    NUMBER(10),
project_name VARCHAR2(20));

CREATE TABLE pers_short (
person_id NUMBER(10),
last_name VARCHAR2(25));

INSERT INTO projects   VALUES (1, 'Teach');
INSERT INTO projects   VALUES (1, 'Code');
INSERT INTO projects   VALUES (2, 'Code');
INSERT INTO pers_short VALUES (1, 'Morgan');
INSERT INTO pers_short VALUES (2, 'Kolk');
INSERT INTO pers_short VALUES (3, 'Scott');
COMMIT;

SELECT * FROM projects;

SELECT * FROM pers_short;

SELECT e.last_name, CAST(MULTISET(
  SELECT p.project_name
  FROM projects p
  WHERE p.person_id = e.person_id
  ORDER BY p.project_name) AS project_table_t)
FROM pers_short e;
Using Multiset with a Multi-column Collection CAST(MULTISET(<select statement>) AS <data_type>)
conn uwclass/uwclass

CREATE OR REPLACE TYPE uob_type AS OBJECT (
object_name VARCHAR2(128), object_type VARCHAR2(18));
/

CREATE OR REPLACE TYPE t_uob_type AS TABLE OF uob_type;
/

set serveroutput on

DECLARE
 x t_uob_type;
BEGIN
  SELECT CAST(MULTISET(
    SELECT object_name, object_type
    FROM user_objects
    WHERE rownum <10) AS t_uob_type)
  INTO x
  FROM dual;

  FOR i IN 1 .. x.COUNT
  LOOP
    dbms_output.put_line(x(i).object_name || ' - '
    || x(i).object_type);
  END LOOP;
END;
/

Related Topics
Built-in Functions
Built-in Operators
Data Types
Types
VArrays

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved