In a previous example we learned about the basic usage of objects in PL/SQL with which we can start object-oriented development in an oracle database.
A more common usage for objects, however, is to use them as a base type for nested table collections (table of objects). That way we can make objects accessible from SQL, which is a very powerful possibility to return table-structures from PL/SQL functions.
The annoyances often start when we want to change an object which is already referenced in a table type: we get ORA-02303 dependency errors. The “force”, as usual, can help here:
-- We can use attribute-only objects without
-- any methods. We dont even have to specify
-- a body in that case
create or replace type t_disturbance is object
(
planet_name varchar2(1000),
strength number(10,4)
);
/
-- And we can create a table of objects
-- so we can select objects from SQL
create or replace type t_disturbances
is table of t_disturbance;
/
-- Little helper function to create
-- disturbances
create or replace function get_disturbances
return t_disturbances
as
l_result t_disturbances := t_disturbances();
begin
l_result.extend;
l_result(1) := new t_disturbance('Alderaan', 10000);
return l_result;
end;
/
select *
from table(get_disturbances());
-- What if we want to change the underlying object now?
create or replace type t_disturbance is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10)
);
/
-- Error.
-- The problem is, that our object-type is a
-- dependency of the table-type.
-- But we can use the force (since 11.2)!
create or replace type t_disturbance force is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10)
);
/
-- Caution! This doesnt work if the type is used
-- in a table:
create table disturbance_history (
id integer not null primary key,
disturbance t_disturbance,
occured timestamp with local time zone
default current_timestamp
);
create or replace type t_disturbance force is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10),
cause_name varchar2(1000)
);
/
-- So lets just not do this for the moment
drop table disturbance_history;
/
create or replace type t_disturbance force is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10),
cause_name varchar2(1000)
);
/
-- Check for invalid objects
select * from user_objects where status 'VALID';
-- The table-type is invalid, but
-- we can just compile it without any
-- additional change
alter type t_disturbances compile;
-- We now need to adapt our helper function
-- because the default constructor of
-- objects requires each attribute to be
-- passed
create or replace function get_disturbances
return t_disturbances
as
l_result t_disturbances := t_disturbances();
begin
l_result.extend;
l_result(1) := new t_disturbance('Alderaan', 10000, null, null);
return l_result;
end;
/
select *
from table(get_disturbances());
This example can be run on LiveSQL. Full source on github.
I started my PL/SQL coding on 10g and remember to have had several scripts which just dropped a table-type, changed the underlying object-type and created the table-type again.
The thing is: I used that approach even after updating to 11 and 12, because I didn’t know about this powerful little helper until recently. Maybe I’m not the only one – if so, let me know 🙂
Top comments (0)