Object Views Not Compared Correctly

jonnoct

Morning,

Schema Compare for Oracle 3.1.0.137 isn't comparing object views correctly.
http://docs.oracle.com/cd/B19306_01/app ... #sthref663

For example here's what DBMS_METADATA will produce...

CREATE OR REPLACE FORCE VIEW "SCHEMA1234"."VIEW1234_V" OF "SCHEMA1234"."MY_OBJECT_TYP"
  WITH OBJECT IDENTIFIER (ID_NUM,SEQUENCE_NUM) AS 
  select
    ...

Schema Compare for Oracle will produce something invalid like...

CREATE OR REPLACE FORCE VIEW iron3731.random_eligibility_v (id_num, sequence_num, attribute1, attribute2,sys_nc_rowinfo$,sys_nc_oid$) AS
select
...

Any views in USER_VIEWS with a non-null VIEW_TYPE column would be impacted. Example:

select * from user_views where view_type = 'MY_OBJECT_TYP';

Other than this so far it's a great tool. Let us know if you need more examples or have questions.

Thanks,
Jon

richardjm

I think this has also come into support directly so we've asked you for more examples there.

Our object type support at the moment is pretty weak and it's something we want to work on as we get good examples from customers and what they need from it.

jonnoct

Thanks Richard,

Support has been very responsive and I have sent in a simpler example. I'll post it here just in case anyone is curious.

drop type hr.department_t force;
drop type hr.employee_tbl_t force;
drop type hr.employee_t force;
/
create type hr.employee_t as object
(
id_num number ( 6, 0 ),
first_name varchar2 ( 20 ),
last_name varchar2 ( 25 )
);
/
create type hr.employee_tbl_t as table of hr.employee_t;
/
create type hr.department_t as object
(
id_num number ( 4, 0 ),
name varchar2 ( 30 ),
employees employee_tbl_t
);
/
create or replace view hr.departments_v of hr.department_t
with object identifier ( id_num ) as
select
d.department_id,
d.department_name,
cast ( multiset (
select
e.employee_id,
e.first_name,
e.last_name
from
hr.employees e
where
e.department_id = d.department_id
)
as hr.employee_tbl_t )
from
hr.departments d;
/
select type_text, oid_text as object_identifier_text from dba_views where owner = 'HR' and view_name = 'DEPARTMENTS_V';

Since TYPE_TEXT is not null Schema Compare should be using the OBJECT_VIEW_CLAUSE (https://docs.oracle.com/database/121/SQ ... m#i2122282)

TYPE_TEXT OBJECT_IDENTIFIER_TEXT

---

hr.department_t id_num