100CodeExamples - Use the force to replace PL/SQL objects

• Script Name 100CodeExamples - Use the force to replace PL/SQL objects
• Visibility Unlisted - anyone with the share link can access
• Description Showcase how to use the force keyword to replace objects which already have dependencies
• Area PL/SQL General
• Contributor Pesse
• Created Tuesday February 12, 2019
• Statement 1
``````create or replace type t_disturbance is object
(
planet_name varchar2(1000),
strength number(10,4)
); ``````
`Type created.`
• Statement 2
``````create or replace type t_disturbances
is table of t_disturbance; ``````
`Type created.`
• Statement 3
``````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; ``````
`Function created.`
• Statement 4
``````select *
from table(get_disturbances())``````
```
PLANET_NAMESTRENGTHAlderaan10000
```
• Statement 5
``````create or replace type t_disturbance is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10)
); ``````
```ORA-02303: cannot drop or replace a type with type or table dependents

```
More Details: https://docs.oracle.com/error-help/db/ora-02303
• Statement 6
``````create or replace type t_disturbance force is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10)
); ``````
`Type created.`
• Statement 7
``````create table disturbance_history (
id integer not null primary key,
disturbance t_disturbance,
occured timestamp with local time zone
default current_timestamp
)``````
`Table created.`
• Statement 8
``````create or replace type t_disturbance force is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10),
cause_name varchar2(1000)
); ``````
```ORA-22866: cannot replace a type with table dependents

```
More Details: https://docs.oracle.com/error-help/db/ora-22866
• Statement 9
``drop table disturbance_history``
`Table dropped.`
• Statement 10
``````create or replace type t_disturbance force is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10),
cause_name varchar2(1000)
); ``````
`Type created.`
• Statement 11
``select * from user_objects where status <> 'VALID'``
```
OBJECT_NAMESUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPECREATEDLAST_DDL_TIMETIMESTAMPSTATUSTEMPORARYGENERATEDSECONDARYNAMESPACEEDITION_NAMESHARINGEDITIONABLEORACLE_MAINTAINEDAPPLICATIONDEFAULT_COLLATIONDUPLICATEDSHARDEDCREATED_APPIDCREATED_VSNIDMODIFIED_APPIDMODIFIED_VSNIDGET_DISTURBANCES - 11986875 - FUNCTION12-FEB-1912-FEB-192019-02-12:19:54:07INVALIDNNN1 - NONEYNNUSING_NLS_COMPNN -  -  -  - T_DISTURBANCES - 11986854 - TYPE12-FEB-1912-FEB-192019-02-12:19:54:07INVALIDNNN1 - NONEYNNUSING_NLS_COMPNN -  -  -  -
2 rows selected.```
• Statement 12
``alter type t_disturbances compile``
`Type altered.`
• Statement 13
``````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; ``````
`Function created.`
• Statement 14
``````select *
from table(get_disturbances())``````
```
PLANET_NAMESTRENGTHALIGNMENTCAUSE_NAMEAlderaan10000 -  -
```