This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Oct 25, 2012 7:43 AM by JoyceScapicchio RSS

3.2.1 DIFF still not working

Postie Newbie
Currently Being Moderated
Hi

I am really keen to use DIFF functions (of non owning schema) to maintain my databases..

But is still not right.. a large portion of the differences it finds, are reported as new.. that is simply not the case..

eg this is a funtion that SQL Dev says is new.. but not
--Source System
create or replace FUNCTION weekday (zdate IN date)
RETURN varchar2
deterministic
AS
BEGIN
return trim(to_char(zdate,'DAY'));
END;

--Target System
create or replace FUNCTION WEEKDAY (zdate IN date)
RETURN varchar2
deterministic
AS
BEGIN
return trim(to_char(zdate,'DAY'));
END;

--Diff Report (says this is new function)
CREATE OR REPLACE FUNCTION "FDP"."WEEKDAY" (zdate IN date)
RETURN varchar2
deterministic
AS
BEGIN
return trim(to_char(zdate,'DAY'));
END;
  • 1. Re: 3.2.1 DIFF still not working
    JoyceScapicchio Journeyer
    Currently Being Moderated
    Hello Postie,
    I'm not sure exactly what you are doing when you are using the diff too, but possibly you would be interested in our new "maintain schema" option. Our product manager has a post in his blog that shows you how to use it [ThatJeffSmith -> Database Diff Enhancements|http://www.thatjeffsmith.com/archive/2012/09/using-database-diff-to-compare-schemas-when-you-dont-have-the-destination-user-password/]

    Joyce Scapicchio
    SQLDeveloper Team
  • 2. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    Hi Joyce

    Think I have conversed previously about DIFF.. glad to see you build this maintain option..

    And Yes.. I have used the "maintain" option when doing the DIFF.. the example I provided was one of many functions/procedures that it thought were new.. but are not

    Dean
  • 3. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    BTW

    I did a compare using TOAD, for Functions.. none of the 23 functions were different

    Then I used SQLDEV3.2.1 and get 15 new functions?

    Dean
  • 4. Re: 3.2.1 DIFF still not working
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    Can you check the procedure name to make sure it's not got a case sensitive name?

    In the tree, does it show as

    case

    or as

    CASE

    It's possible that the DIFF is correct as you can have 2 procedures of the same name, although when you force the case by double-quoting it, it's not technically the same name.


    To reproduce your case, we need some sample code, your version of Oracle database, and the options you used for the DIFF.
  • 5. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    Hi Jeff

    I thought that maybe the case as well and tested prior to posting this error...

    So.. in the tree it is UPPER CASE in both UAT & PRD

    PRD is
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    create or replace FUNCTION       "WEEKDAY" (zdate IN date)
       RETURN varchar2
       deterministic
    AS
    BEGIN
        return trim(to_char(zdate,'DAY'));
    END;
    UAT is
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    create or replace FUNCTION       "WEEKDAY" (zdate IN date)
       RETURN varchar2
       deterministic
    AS
    BEGIN
        return trim(to_char(zdate,'DAY'));
    END;
    DDL Gen Options
    - Password value checked
    - constraints checked
    - schema greyed out
    - referential constraints checked

    DDL Compare Options
    - Schema = maintain
    - match constraints by = Definition
    - ignore segment attributes checked
    - ignore storage checked
    - ignore tablespace checked
    - logical compare checked
    - ignore table column position checked
  • 6. Re: 3.2.1 DIFF still not working
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    So with 'maintain' preference for the DIFF, please confirm that the schema name in UAT matches the schema name for WEEKDAY in PRD.

    We capture the schema in the source schema then use that to find the object in the destination connection, regardless of the user defined in the connection.
  • 7. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    Yes schema names are the same

    Dean
  • 8. Re: 3.2.1 DIFF still not working
    JoyceScapicchio Journeyer
    Currently Being Moderated
    I am suspecting thay you are having a permissions issue. You need to have not only object permission, but also select_catalog_role (ensure that it is enabled as default). DDL and services are implemented via DBMS_METADATA and these permissions are required for those services. Based on what you are seeing, I suspect that you have the required permissions on the source connections, but not on the target connection.

    These are the basic queries we use which you can try to test your permissions...

    select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;

    SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;
  • 9. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    PRD
    select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
    Result=1

    SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;
    result null

    UAT
    select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
    Result=null

    SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;
    result null

    I did get select_catalog_role.. obviously not correctly.. it is a pain to have to get this.. when we don't on TOAD?

    Dean
  • 10. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    Sorry I did these tests in a rush (on TOAD).. and it does work on SQLDev..

    PRD
    select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
    Result = 1

    SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;
    result = "
    CREATE OR REPLACE FUNCTION "FDP"."WEEKDAY" (zdate IN date)
    RETURN varchar2
    deterministic
    AS
    BEGIN
    return trim(to_char(zdate,'DAY'));
    END;

    "

    UAT
    PRD
    select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
    Result = 1

    SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;
    Result ="
    CREATE OR REPLACE FUNCTION "FDP"."WEEKDAY" (zdate IN date)
    RETURN varchar2
    deterministic
    AS
    BEGIN
    return trim(to_char(zdate,'DAY'));
    END;
    "
  • 11. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    Hi Joyce

    So is this a bug? Or do I have settings wrong?

    Dean
  • 12. Re: 3.2.1 DIFF still not working
    JoyceScapicchio Journeyer
    Currently Being Moderated
    Hi again,
    Sorry your previous posts confused me and i thought you had resolved your problem. The only way I can reproduce your problem is if I have the required permissions on the source, but not on the dest connection. Those queries would have to be run on each connection, the source and the dest connections. I'm not sure if you did that. Anyway, please provide a reproducible test case, with the actual permissions for the objects and the source and dest connections if you still think there is a problem.
    Joyce
  • 13. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    Hi sorry I was unclear.. results below from source & dest system

    Source System
    select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
    result ==> 1

    SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;
    result==> "
    CREATE OR REPLACE FUNCTION "FDP"."WEEKDAY" (zdate IN date)
    RETURN varchar2
    deterministic
    AS
    BEGIN
    return trim(to_char(zdate,'DAY'));
    END;

    "
    Dest System
    select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
    result ==>1

    SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;
    result ==> "
    CREATE OR REPLACE FUNCTION "FDP"."WEEKDAY" (zdate IN date)
    RETURN varchar2
    deterministic
    AS
    BEGIN
    return trim(to_char(zdate,'DAY'));
    END;
    "
  • 14. Re: 3.2.1 DIFF still not working
    JoyceScapicchio Journeyer
    Currently Being Moderated
    Hi Postie,
    I will need a reproducible case with the grants for the objects and users in order to diagnose the problem as I have been unable to reproduce it in my environment.
    Thanks,
    Joyce
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points