PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle CAST Function
Version 11.1
 
CAST With Collections

Using Multiset With A VARRAY		 CAST(MULTISET(<select statement>) AS <data_type>)
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     VARCHAR(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>)
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>)
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;
/
 

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 With Dates
Date CAST(<column_or_value> AS <data_type>)
SELECT CAST('01-JAN-2004' AS DATE) CDate
FROM DUAL;
Timestamp CAST(<column_or_value> AS <data_type>)
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) DTWTZ
FROM DUAL;
 
CAST With Numbers
Number CAST(<column_or_value> AS <data_type>)
SELECT 1 + CAST(3.14 * 0.152 AS NUMBER(10,7)) FLOATING
FROM DUAL;
 
CAST With Strings
Varchar2 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;
 
Related Topics
Types
VArrays
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [360 users online]    © 2010 psoug.org