Oracle CAST Function
Version 21c

Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 21c
What's New In 23c

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