define oracle pl/sql collection (object and table) types

1. Define Types

• define Object Types

syntax

create or replace type [OBJECT_TYPE_NAME] as object 
( 
    define fields ...
)

code

create or replace type My_OBJECT_TYPE as object 
( 
    field1 varchar2(5),
    field2 varchar2(30),
    field3 number,
    field4 date
)

• define Table Types (Array) reference to Object Types

syntax

create or replace type [TABLE_TYPE_NAME] as table of [OBJECT_TYPE_NAME];

code

create or replace type My_TABLE_TYPE as table of MY_OBJECT_TYPE;

2. Apply

• write pl/sql

declare
  -- 1) allocate empty space for table, not define is null 
  -- same array of other programming languages  
  p_tab  MY_TABLE_TYPE := MY_TABLE_TYPE();

begin

  for rcd in (select col1, col2, col3, col4 from MY_TABLE) loop

    -- 2) extends table space (array) from zero to one, two, three, ... n spaces (by step)
    p_tab.extend(); 

    -- 3) initial value, pass null to constructor equal number of fields for My_OBJECT_TYPE 
    -- p_tab.last start by 1 after call p_tab.extend()
    -- same new instance of other programming languages  
    p_tab(p_tab.last) := MY_OBJECT_TYPE(null, null, null, null);

    -- 4) define value to each fields
    p_tab(p_tab.last).field1 := rcd.col1;
    p_tab(p_tab.last).field2 := rcd.col2; 
    p_tab(p_tab.last).field3 := rcd.col3;
    p_tab(p_tab.last).field4 := rcd.col4; 

  end loop;
  
  exception
    when others then dbms_output.put_line(DBMS_UTILITY.format_error_stack);

end;