CREATE OR REPLACE PROCEDURE EXEC_DONT_FAIL( P_CMD IN VARCHAR2 ) IS
    e_table_or_view_does_not_exist exception;
    pragma exception_init(e_table_or_view_does_not_exist, -942);
    e_type_does_not_exist exception;
	pragma exception_init(e_type_does_not_exist, -4043);
	e_sequence_does_not_exist exception;
	pragma exception_init(e_sequence_does_not_exist, -2289);
BEGIN
	  EXECUTE IMMEDIATE P_CMD;
EXCEPTION
	WHEN e_table_or_view_does_not_exist OR e_type_does_not_exist OR e_sequence_does_not_exist THEN
	  NULL;
END;
/
DECLARE
BEGIN
	EXEC_DONT_FAIL('DROP TYPE DEP_OBJECTLIST');
	EXEC_DONT_FAIL('DROP TYPE DEP_OBJECT');
    EXEC_DONT_FAIL('DROP TYPE PrecedenceObjList');
	EXEC_DONT_FAIL('DROP TABLE dataflow_dependency_t');
END;
/

DROP PROCEDURE EXEC_DONT_FAIL;
-- DROP TYPE DEP_OBJECTLIST;
-- DROP TYPE DEP_OBJECT;
-- DROP TYPE PrecedenceObjList;
-- DROP TABLE dataflow_dependency_t;

CREATE TYPE PrecedenceObjList AS VARRAY(600) OF VARCHAR2(100);
/

CREATE TYPE DEP_OBJECT AS OBJECT (
   object_id NUMBER(9),
   object_uoid VARCHAR2(255),
   object_name VARCHAR2(255),
   object_logical_name VARCHAR2(255),
   object_classname VARCHAR2(255),
   object_strongtypename VARCHAR2(255),
   object_icontype VARCHAR2(255),
   group_id NUMBER(9),
   group_uoid VARCHAR2(255),
   group_name VARCHAR2(255),
   group_logical_name VARCHAR2(255),
   group_classname VARCHAR2(255),
   group_strongtypename VARCHAR2(255),
   group_icontype VARCHAR2(255),
   parent_id NUMBER(9),
   parent_uoid VARCHAR2(255),
   parent_name VARCHAR2(255),
   parent_logical_name VARCHAR2(255),
   parent_classname VARCHAR2(255),
   parent_strongtypename VARCHAR2(255),
   parent_icontype VARCHAR2(255),
   folder_id NUMBER(9),
   folder_uoid VARCHAR2(255),
   folder_name VARCHAR2(255),
   folder_logical_name VARCHAR2(255),
   folder_classname VARCHAR2(255),
   folder_strongtypename VARCHAR2(255),
   folder_icontype VARCHAR2(255),
   preBoundType VARCHAR2(20),
   precedence_objects PrecedenceObjList
);
/

CREATE TYPE DEP_OBJECTLIST IS VARRAY(600) OF DEP_OBJECT;
/

CREATE TABLE alldataflow_dependency_t (
   workspaceID NUMBER(9) default sys_context('owb_workspace','workspaceID') not null,
   source_comp_id NUMBER(9),
   source_entity_id NUMBER(9),
   source_param_id NUMBER(9),
   source_item_id NUMBER(9),
   target_param_id NUMBER(9),
   target_item_id NUMBER(9),
   target_comp_id NUMBER(9),
   target_entity_id NUMBER(9)
);

CREATE OR REPLACE VIEW dataflow_dependency_t (
   workspaceID,
   source_comp_id,
   source_entity_id,
   source_param_id,
   source_item_id,
   target_param_id,
   target_item_id,
   target_comp_id,
   target_entity_id
) as select
   workspaceID,
   source_comp_id,
   source_entity_id,
   source_param_id,
   source_item_id,
   target_param_id,
   target_item_id,
   target_comp_id,
   target_entity_id
from alldataflow_dependency_t
where workspaceID = (select sys_context('owb_workspace','workspaceID') from dual);