Oracle CAST Function
Version 21c

Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Full Syntax CAST(<column_or_value> AS <data_type>
[DEFAULT <return_value> ON CONVERSION ERROR]
[,<format>, 'NLS_Parameter'])
 
CAST with Dates
CAST as DATE data type CAST(<column_or_value> AS <data_type>)
SELECT CAST('01-JAN-2020' AS DATE) CDate
FROM dual;

CDATE
--------------------
12-DEC-2020 00:00:00
 
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;

  FLOATING
----------
   1.47728
 
CAST with Strings
CAST as VARCHAR2 data type CAST(<column_or_value> AS <data_type>)
SELECT object_name
FROM user_objects
WHERE rownum < 6;

OBJECT_NAME
------------------------------
ACCESS$
ACLMV$
ACLMV$_BASE_VIEW
ACLMV$_MVINFO
ACLMV$_REFLOG


SELECT CAST(object_name AS VARCHAR2(30)) OBJ_NAME
FROM user_objects
WHERE rownum < 6;

OBJECT_NAME
------------------------------
ACCESS$
ACLMV$
ACLMV$_BASE_VIEW
ACLMV$_MVINFO
ACLMV$_REFLOG
 
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;

DTWTZ
------------------------------
12-DEC-20 09.49.14.000000 PM
Cast a timestamp calculation result as a number of seconds CAST(<column_or_value> AS <data_type>)
col cast_timestamp format 9999999999

SELECT ROUND(CAST(SYSTIMESTAMP AT TIME ZONE 'utc' AS DATE) - TO_DATE('15.01.2020', 'DD.MM.YYYY')) * 86400 AS CAST_TIMESTAMP
FROM dual;

CAST_TIMESTAMP
--------------
       5270400
 
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 19c
What's New In 20c-21c

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