1 2 Previous Next 28 Replies Latest reply: Oct 25, 2012 9:43 AM by Joyce Scapicchio-Oracle Go to original post RSS
      • 15. Re: 3.2.1 DIFF still not working
        Postie
        Right.. I think I have isolated around GRANTS

        I these databases are developed with APEX..
        Source was built at apex 3.1 and updated progressively to 4.1.1
        Target was built at apex 4.0 and updated to 4.1.1
        I know there were some permission changes in apex around 4?

        So.. if on
        - source system I have no explicit grants on WEEKDAY
        - target system I explicitly grant my Connect schema execute/debug to WEEKDAY function..
        then the DIFF will work

        Even though I can view Function in SQL Dev on both Source & Target Systems..

        Any ideas.. I what I need to fix ?
        • 16. Re: 3.2.1 DIFF still not working
          Joyce Scapicchio-Oracle
          You might compare your permissions and roles on the two users and see what the source has that the target does not. Possibly you have been granted a role on the source that you don't have on the target.
          • 17. Re: 3.2.1 DIFF still not working
            Postie
            Joyce

            I got DBA to compare roles.. and they are exactly the same?

            Do you have a SQL I can run to use to compare permissions & roles?

            Dean
            • 18. Re: 3.2.1 DIFF still not working
              Joyce Scapicchio-Oracle
              You need to be a priviledged user for these...

              select * from dba_role_privs where grantee = 'MIGRATION';
              select * from dba_sys_privs where grantee = 'MIGRATION';
              • 19. Re: 3.2.1 DIFF still not working
                Postie
                Hi Run these.. I can run
                select * from dba_role_privs ;
                select * from dba_sys_privs ;
                and get results...

                But there is no MIGRATION grantee/schema
                • 20. Re: 3.2.1 DIFF still not working
                  Joyce Scapicchio-Oracle
                  Sorry, my bad, where grantee = 'MIGRATION', replace 'MIGRATION' with be your schema name
                  • 21. Re: 3.2.1 DIFF still not working
                    Postie
                    Ok.. on both connect schemas Source & Destination

                    select * from dba_sys_privs where grantee = 'POSTIE_READONLY';
                    GRANTEE     PRIVILEGE     ADMIN_OPTION
                    POSTIE_READONLY     CREATE SESSION     NO
                    POSTIE_READONLY     SELECT ANY TABLE     NO

                    select * from dba_role_privs where grantee = 'POSTIE_READONLY';
                    GRANTEE     GRANTED_ROLE     ADMIN_OPTION     DEFAULT_ROLE
                    POSTIE_READONLY     SELECT_CATALOG_ROLE     NO     YES

                    on FDP Schema I am trying to comapre
                    select * from dba_sys_privs where grantee = 'FDP';
                    GRANTEE     PRIVILEGE     ADMIN_OPTION
                    FDP     CREATE OPERATOR     NO
                    FDP     CREATE SEQUENCE     NO
                    FDP     CREATE JOB     NO
                    FDP     CREATE TYPE     NO
                    FDP     CREATE MATERIALIZED VIEW     NO
                    FDP     CREATE TRIGGER     NO
                    FDP     CREATE ANY CONTEXT     NO
                    FDP     CREATE DIMENSION     NO
                    FDP     CREATE INDEXTYPE     NO
                    FDP     CREATE SYNONYM     NO
                    FDP     CREATE PROCEDURE     NO
                    FDP     CREATE TABLE     NO
                    FDP     CREATE VIEW     NO
                    FDP     CREATE CLUSTER     NO
                    • 22. Re: 3.2.1 DIFF still not working
                      Joyce Scapicchio-Oracle
                      Hi Postie,
                      Thanks for sticking with me on this. I have been looking into removing the requirement to have object level permisssions in order to do a diff which I think will resolve your issue. However, these results you reported are concerning/confusing me so I am hoping you have not done them as I expected:

                      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


                      Please retry the queries in SQLDeveloper as follows:

                      1. Connect to your destination connection. In the worksheet for your destination connection, enter these commands and let post the results:

                      select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
                      select 1 from dba_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
                      SELECT dbms_metadata.get_ddl('FUNCTION','WEEKDAY','FDP') FROM DUAL;


                      2. Connect to the source connection. In the worksheet for you source connection, enter the same commands and post the results.
                      Joyce
                      • 23. Re: 3.2.1 DIFF still not working
                        Postie
                        Joyce

                        Sorry.. I got very busy..

                        SQLdev - Dest
                        select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
                        Result = 1
                        select 1 from dba_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;"

                        SQLdev - Source
                        select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
                        Result = null
                        select 1 from dba_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;
                        "

                        So be interested to see if I have something set wrong on my source database? to get the null in first result

                        Thanks
                        Dean
                        • 24. Re: 3.2.1 DIFF still not working
                          Joyce Scapicchio-Oracle
                          Are you sure you don't have the source and the dest reversed? If so, that would explain your results and would also mean that the 'fix' I have would resolve your issue. But, if you can issue those queries successfully from the dest connection, I don't understand why diff did not find the function in the dest connnection. In 3.2, it is querying all_objects to see if the object exists in the dest connection (ie. is accessible from the dest connection). Otherwise, I am perplexed...
                          • 25. Re: 3.2.1 DIFF still not working
                            Postie
                            Joyce

                            I had a explicit execute branch on Dest.. So I could get diff working better... so I removed & re-ran SQLs

                            SQLdev - Dest
                            select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
                            Result = null
                            select 1 from dba_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;"

                            SQLdev - Source
                            select 1 from all_objects where owner = 'FDP' and object_type = 'FUNCTION' and object_name = 'WEEKDAY' ;
                            Result = null
                            select 1 from dba_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;
                            "
                            • 26. Re: 3.2.1 DIFF still not working
                              Joyce Scapicchio-Oracle
                              Exactly what I wanted to see! Thanks. So, our next release should be better for you! Keep an eye out.

                              Thanks for trying and re-trying.
                              Joyce
                              • 27. Re: 3.2.1 DIFF still not working
                                Postie
                                Cool thanks...

                                One last think.. I did notice some niggly issues around Quotes on function/package/table names for DIFF
                                eg create Function "FDP"."FDP_WEEKDAY"

                                Where it was finding differences when there wasn't any difference

                                Thanks
                                Dean
                                • 28. Re: 3.2.1 DIFF still not working
                                  Joyce Scapicchio-Oracle
                                  Well, I think that this thread has gotten way too long and complicated and this sounds like a completely different issue. So, if you think that there is another problem, please post as a different thread with as simple a test case as possible. That also allows us to hook the forum problems with bugs and the resolutions in the source code.

                                  Thanks.
                                  Joyce
                                  1 2 Previous Next