This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Oct 25, 2012 7:43 AM by JoyceScapicchio Go to original post RSS
  • 15. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    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
    JoyceScapicchio Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    JoyceScapicchio Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    JoyceScapicchio Journeyer
    Currently Being Moderated
    Sorry, my bad, where grantee = 'MIGRATION', replace 'MIGRATION' with be your schema name
  • 21. Re: 3.2.1 DIFF still not working
    Postie Newbie
    Currently Being Moderated
    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
    JoyceScapicchio Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    JoyceScapicchio Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    JoyceScapicchio Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    JoyceScapicchio Journeyer
    Currently Being Moderated
    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

Legend

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