Oracle PL/SQL | Object Usage Example

-- Definition of Object
drop type Address01

create or replace type Address01 as object(
field01 varchar2(50),
field02 varchar2(50),
field03 varchar2(50),
field04 varchar2(50),
member procedure ShowValue(self in out nocopy Address01)
);
-- Body of Object
create type body Address01
as
member procedure ShowValue( self in out nocopy Address01)
is
begin
dbms_output.put_line( 'House Name: ' || self.field01 );
dbms_output.put_line( 'Street Name: ' || self.field02 );
dbms_output.put_line( 'City Name: ' || self.field03 );
dbms_output.put_line( 'Country: ' || self.field04 );
end;
end;
/
-- Creation of Table having Object Column
drop table table01;

create table table01(
field01 varchar2(10),
field02 varchar2(50),
field03 Address01
);
-- Inserting values to Object using table
insert into table01
values(
'0002',
'Name 02',
Address01( 'Block 02', 'Street02', 'City02', 'Country02 - 000 002' )
)
-- Updating values into Object using table
update table01 t
set t.field03.Field01 = 'Block 03'
where t.field01 = '0002'
-- To use Object member procedure
declare
cursor FetchAddress01 is
select Field01, Field02, Field03
from Table01;
begin
for i in FetchAddress01
loop
dbms_output.put_line('Person Id: ' || i.Field01);
dbms_output.put_line('Person Id: ' || i.Field02);
i.Field03.ShowValue();
end loop;
end;
/
-- To use Dynamic SQL with Objects
declare
cursor FetchAddress01 is
select Field01, Field02, Field03
from Table01;

v_address01 Address01;
v_field01 varchar2(10);
v_field02 varchar2(50);
v_sql varchar2(255);
begin
v_sql := ' Insert into table01
values( :1, :2, :3)';
for i in 3 .. 10
loop
v_field01 := to_char( i, '0000' );
v_field02 := 'Name ' || to_char( i, '00' );
v_address01 := Address01(
'Block ' || to_char( i, '00' ),
'Street' || to_char( i, '00' ),
'City' || to_char( i, '00' ),
'Country' || to_char( i, '00' ) || ' - 000 ' || to_char( i, '000' )
);
execute immediate v_sql using v_field01, v_field02, v_address01;
commit;
end loop;
for i in FetchAddress01
loop
dbms_output.put_line('Person Id: ' || i.Field01);
dbms_output.put_line('Person Id: ' || i.Field02);
i.Field03.ShowValue();
end loop;
end;
/