Oracle CAST Function
Version 12.1.0.2

Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.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.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
 
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@pdbev

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@pdbdev

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@pdbdev

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
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved