Simple schema compare scripts

Assuming connected to "test" database and want to compare to "product" via database link "prod".


Object difference:

select p.object_name prod_obj_name, p.object_type prod_obj_type, t.object_name test_obj_name, t.object_type test_obj_type
from user_objects@prod p
full outer join user_objects t
on p.object_name=t.object_name
and t.object_type=p.object_type
order by decode(p.object_name,NULL, 0, 1), decode(t.object_name,NULL, 0, 1),
p.object_name;

The results are shown in 3 sections: NULL first 2 columns - objects exist in "test" not in "prod"; NULL last 2 columns - objects exist in "prod" not in "test"; no NULL columns - objects exist in both. "Where" clause can be added to show only the difference instead of thousands of identical objects.


Table column difference:

WITH X as (select p.table_name, p.column_name
from user_tab_columns@prod p
join user_tables tt
on tt.table_name=p.table_name
),
Y as
(select t.table_name, t.column_name
from user_tab_columns t
join user_tables@prod pt
on pt.table_name=t.table_name
)
select x.table_name prod_table_name, x.column_name prod_column_name, y.table_name test_table_name, y.column_name test_column_name
from x full outer join y
on x.table_name=y.table_name
and x.column_name=y.column_name
where x.table_name is null or y.table_name is null
;


Similar to object difference, but it has to be done in a more complex query.

Index columns or even procedures etc can also be checked similarly if needed.

Rollback when rasise application error?

Do we need to explicitly call rollback when we call RAISE_APPLICATION_ERROR? The answer is: Maybe.

Test case:

CREATE TABLE A (COL1 VARCHAR2(10));

INSERT INTO A VALUES ('1');

begin
    insert into a values('2');
    RAISE_APPLICATION_ERROR(-20005,'ERROR: ');
end;
/

begin
*
ERROR at line 1:
ORA-20005: ERROR:
ORA-06512: at line 4

SQL> select * from a;

COL1
----------
1
 

If we try rollback manually:

begin
    insert into a values('2');
    rollback;
    RAISE_APPLICATION_ERROR(-20005,'ERROR: ');
end;
/
 

begin
*
ERROR at line 1:
ORA-20005: ERROR:
ORA-06512: at line 4
 

SQL> select * from a;
no rows selected
 

Conclusion: RAISE_APPLICATION_ERROR implicits an rollback for the PL/SQL block ONLY. If you want to rollback the whole transaction, you still need to issue rollback explicitly.