1 2 Previous Next 28 Replies Latest reply: Oct 25, 2012 9:43 AM by Joyce Scapicchio-Oracle RSS

    3.2.1 DIFF still not working

    Postie
      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
          Joyce Scapicchio-Oracle
          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
            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
              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-Oracle
                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
                  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-Oracle
                    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
                      Yes schema names are the same

                      Dean
                      • 8. Re: 3.2.1 DIFF still not working
                        Joyce Scapicchio-Oracle
                        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
                          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
                            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
                              Hi Joyce

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

                              Dean
                              • 12. Re: 3.2.1 DIFF still not working
                                Joyce Scapicchio-Oracle
                                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
                                  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
                                    Joyce Scapicchio-Oracle
                                    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