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;
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;
/
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);
/
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;
/