Oracle VArray Procedure Sample

-- CREATE OR REPLACE procedure MAT1.P_ADM0008_vTest ( pList in phone_varray_typ ) is
CREATE OR REPLACE procedure MAT1.P_ADM0008_vTest is 
    CURSOR vTestCursor is 
    select DEPT_NO as dNo, 
           PHONE_LIST as pList
      from mat1.dept_phone_list;
      
      dNo NUMBER(5) := '';
      pList phone_varray_typ := phone_varray_typ();
      
    BEGIN 
        open vTestCursor;
 
        loop    
            fetch vTestCursor                  
             into dNo,
                  pList;  
        exit when vTestCursor%notfound;
        
            FOR i IN 1 .. pList.count LOOP 
                dbms_output.put_line(dNo || '-' || pList(i).country_code || '-' || pList(i).area_code || '-' || pList(i).ph_number);  
            END LOOP;
            
        end loop;
    END;
 
 
---- Param ------
varry value = mat1.phone_varray_typ(mat1.phone_typ('111','222','333'),mat1.phone_typ('444','555','666'))
 
 
-------- Create ------------
--Create Object Type
CREATE or replace TYPE phone_typ AS OBJECT (
    country_code  VARCHAR2(20),
    area_code      VARCHAR2(30),
    ph_number      VARCHAR2(30));
 
--Create VArray Type    
CREATE TYPE phone_varray_typ AS VARRAY(10) OF phone_typ;
 
-- Create Table
CREATE TABLE mat1.dept_phone_list (
  dept_no NUMBER(5),
  phone_list phone_varray_typ);
  
-- Insert Test Data
INSERT INTO mat1.dept_phone_list VALUES (
  100,
  phone_varray_typ( phone_typ ('01', '650', '5550123'),
                      phone_typ ('01', '650', '5550148'),
                      phone_typ ('01', '650', '5550192')));