#100CodeExamples – Use the force to replace objects
Tags: 100codeexamples, plsql, oracle
Published: 2019-03-11 08:52:37

---
title: #100CodeExamples – Use the force to replace objects
published: true
tags: #100CodeExamples,PL/SQL,Oracle
canonical_url: https://cleandatabase.wordpress.com/2019/02/12/100codeexamples-use-the-force-to-replace-objects/
---

In a [previous example](https://dev.to/pesse/100codeexamples--plsql-objects-basics-of-force-sensitive-beings-1275) 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:

```sql
-- 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](https://livesql.oracle.com/apex/livesql/s/hyqyfannrmnlh1pqhpo2jpdl1). Full source on [github](https://github.com/pesse/sith-demo-db/blob/master/100CodeExamples/objects/9_table_objects_and_the_force.sql).

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 🙂