create or replace type t_disturbance is object
(
planet_name varchar2(1000),
strength number(10,4)
); Type created.
create or replace type t_disturbances
is table of t_disturbance; Type created.
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.
select *
from table(get_disturbances())| PLANET_NAME | STRENGTH | Alderaan | 10000 |
|---|
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 dependentsMore Details: https://docs.oracle.com/error-help/db/ora-02303
create or replace type t_disturbance force is object
(
planet_name varchar2(1000),
strength number(10,4),
alignment varchar2(10)
); Type created.
create table disturbance_history (
id integer not null primary key,
disturbance t_disturbance,
occured timestamp with local time zone
default current_timestamp
)Table created.
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 dependentsMore Details: https://docs.oracle.com/error-help/db/ora-22866
drop table disturbance_historyTable dropped.
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.
select * from user_objects where status <> 'VALID'| OBJECT_NAME | SUBOBJECT_NAME | OBJECT_ID | DATA_OBJECT_ID | OBJECT_TYPE | CREATED | LAST_DDL_TIME | TIMESTAMP | STATUS | TEMPORARY | GENERATED | SECONDARY | NAMESPACE | EDITION_NAME | SHARING | EDITIONABLE | ORACLE_MAINTAINED | APPLICATION | DEFAULT_COLLATION | DUPLICATED | SHARDED | CREATED_APPID | CREATED_VSNID | MODIFIED_APPID | MODIFIED_VSNID | GET_DISTURBANCES | - | 11986875 | - | FUNCTION | 12-FEB-19 | 12-FEB-19 | 2019-02-12:19:54:07 | INVALID | N | N | N | 1 | - | NONE | Y | N | N | USING_NLS_COMP | N | N | - | - | - | - | T_DISTURBANCES | - | 11986854 | - | TYPE | 12-FEB-19 | 12-FEB-19 | 2019-02-12:19:54:07 | INVALID | N | N | N | 1 | - | NONE | Y | N | N | USING_NLS_COMP | N | N | - | - | - | - |
|---|
alter type t_disturbances compileType altered.
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.
select *
from table(get_disturbances())| PLANET_NAME | STRENGTH | ALIGNMENT | CAUSE_NAME | Alderaan | 10000 | - | - |
|---|