comparing changed types

DariusV

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;

Eddie D

Hi

Thank you for your forum post and sorry that you have encountered a problem.

Sadly following the steps you kindly provided, I am unable to replicate the reported fault symptoms. Below is the deployment script I generate using Schema Compare for Oracle V3.1.5.1321 comparing two Oracle 12c schemas:

-
-- Script generated by Schema Compare for Oracle 3.1.5.1321 on 6/17/2016 12:58:29 PM
--
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;
/

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;
/

To move this on, a support ticket has been created for you in our call logging system. The call reference is #67773. Would it be possible for you to send an email to [support@red-gate.com] quoting call reference #67773 and attach a Schema Compare for Oracle snapshot file of the source and target schema's for me to test against?

Many Thanks
Eddie