when comparing changed types, drop ddl is in the wrong line
Also i would like to have option not to drop and recreate depended types.
Because we use: DROP TYPE ... FORCE; create changed type and then recompile invalidated dependent types.
If there are complex dependencies then its quicker to drop force and recompile invalidated types then recreate all the types again.
Schema compare for oracle v3.1.5.1321
Oracle Db 12c
Behavior
Include deployment script header
Include SET DEFINE OFF
Exclude schema names
Detect renamed columns
Add PURGE clause to all table drops
Suppress dependent ALTER COMPILE
Ignore
slow dependencies
dependent objects in other schemas
white space
double quotes in PL/SQL blocks
sequence current value
permissions
materialized view START WITH value
on database1 create:
create or replace type Test_Type_Row as object (
Col1 NUMBER,
Col2 VARCHAR2(30) );
/
CREATE OR REPLACE TYPE Test_Type_Table AS TABLE OF Test_Type_Row;
/
CREATE OR REPLACE PACKAGE Test_Package IS
FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED ;
END Test_Package;
/
create or replace package body Test_Package is
FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED IS
BEGIN
PIPE ROW(Test_Type_Row(Col1, 'Test1'));
RETURN;
END Test_Function;
end Test_Package;
/
on database2 create:
create or replace type Test_Type_Row as object(
Col1 NUMBER,
Col2 VARCHAR2(30),
Col3 VARCHAR2(30)
);
/
CREATE OR REPLACE TYPE Test_Type_Table AS TABLE OF Test_Type_Row;
/
CREATE OR REPLACE PACKAGE Test_Package IS
FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED ;
END Test_Package;
/
create or replace package body Test_Package is
FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED IS
BEGIN
PIPE ROW(Test_Type_Row(Col1, 'Test1', 'Test2'));
RETURN;
END Test_Function;
end Test_Package;
/
for deployment select only type Test_Type_Row (without dependencies) then script is created correctly (drop ddl is on the second line):
SET DEFINE OFF
DROP TYPE test_type_table;
CREATE OR REPLACE type Test_Type_Row as object(
Col1 NUMBER,
Col2 VARCHAR2(30)
);
/
CREATE OR REPLACE TYPE Test_Type_Table AS TABLE OF Test_Type_Row;
/
but when together selected package body Test_Package (or selected: include dependencies ) script is wrong (drop ddl is on the last line):
SET DEFINE OFF
CREATE OR REPLACE type Test_Type_Row as object(
Col1 NUMBER,
Col2 VARCHAR2(30)
);
/
CREATE OR REPLACE TYPE Test_Type_Table AS TABLE OF Test_Type_Row;
/
CREATE OR REPLACE package body Test_Package is
FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED IS
BEGIN
PIPE ROW(Test_Type_Row(Col1, 'Test1'));
RETURN;
END Test_Function;
end Test_Package;
/
DROP TYPE test_type_table;
Hi
Also i would like to have option not to drop and recreate depended types.
Because we use: DROP TYPE ... FORCE; create changed type and then recompile invalidated dependent types.
If there are complex dependencies then its quicker to drop force and recompile invalidated types then recreate all the types again.
Schema compare for oracle v3.1.5.1321
Oracle Db 12c
Behavior
Include deployment script header
Include SET DEFINE OFF
Exclude schema names
Detect renamed columns
Add PURGE clause to all table drops
Suppress dependent ALTER COMPILE
Ignore
slow dependencies
dependent objects in other schemas
white space
double quotes in PL/SQL blocks
sequence current value
permissions
materialized view START WITH value
on database1 create:
create or replace type Test_Type_Row as object ( Col1 NUMBER, Col2 VARCHAR2(30) ); / CREATE OR REPLACE TYPE Test_Type_Table AS TABLE OF Test_Type_Row; / CREATE OR REPLACE PACKAGE Test_Package IS FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED ; END Test_Package; / create or replace package body Test_Package is FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED IS BEGIN PIPE ROW(Test_Type_Row(Col1, 'Test1')); RETURN; END Test_Function; end Test_Package; /on database2 create:
create or replace type Test_Type_Row as object( Col1 NUMBER, Col2 VARCHAR2(30), Col3 VARCHAR2(30) ); / CREATE OR REPLACE TYPE Test_Type_Table AS TABLE OF Test_Type_Row; / CREATE OR REPLACE PACKAGE Test_Package IS FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED ; END Test_Package; / create or replace package body Test_Package is FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED IS BEGIN PIPE ROW(Test_Type_Row(Col1, 'Test1', 'Test2')); RETURN; END Test_Function; end Test_Package; /for deployment select only type Test_Type_Row (without dependencies) then script is created correctly (drop ddl is on the second line):
but when together selected package body Test_Package (or selected: include dependencies ) script is wrong (drop ddl is on the last line):
SET DEFINE OFF CREATE OR REPLACE type Test_Type_Row as object( Col1 NUMBER, Col2 VARCHAR2(30) ); / CREATE OR REPLACE TYPE Test_Type_Table AS TABLE OF Test_Type_Row; / CREATE OR REPLACE package body Test_Package is FUNCTION Test_Function (Col1 NUMBER) RETURN Test_Type_Table PIPELINED IS BEGIN PIPE ROW(Test_Type_Row(Col1, 'Test1')); RETURN; END Test_Function; end Test_Package; / DROP TYPE test_type_table;