Forum Stats

  • 3,840,388 Users
  • 2,262,598 Discussions
  • 7,901,258 Comments

Discussions

User unable to see views SQL definition

wolf.goebel
wolf.goebel Member Posts: 48 Blue Ribbon

I have a user that is unable to see views definitions from other users using SQLDeveloper version 20.4.0.379, Build 379.2205. Using an older version, 19.4, it worked OK. Could this be a bug?

Tagged:

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,708 Employee

    Possibly, but I'm not able to reproduce what you're describing.

    We run a variation of this

     select DBMS_METADATA.GET_DDL('VIEW',:NAME,:OWNER) from dual


    Try that and see what happens, substituting your view name and schema for :NAME and :OWNER


    You can see exactly what we're running in the View - Log - Statements panel

  • wolf.goebel
    wolf.goebel Member Posts: 48 Blue Ribbon

    Hi Jeff!

    Thanks for the answer! Never paid much attention to the Statements-Log panel, though.... nice!


    The user I'm trying with can see the view:

    select owner,view_name from all_views where view_name='V_CONTACT';

    OWNER                                                                                                                           VIEW_NAME                                                                                                                      

    -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

    REPORTING_1SAT                                                                                                                  V_CONTACT                                                                  


    But the dbms_metadata fails telling it that the object was not found:

    select DBMS_METADATA.GET_DDL('VIEW','V_CONTACT','REPORTING_1SAT') from dual;                                               

    Error starting at line : 2 in command -

    select DBMS_METADATA.GET_DDL('VIEW','V_CONTACT','REPORTING_1SAT') from dual

    Error report -

    ORA-31603: object "V_CONTACT" of type VIEW not found in schema "REPORTING_1SAT"

    ORA-06512: at "SYS.DBMS_METADATA", line 6731

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

    ORA-06512: at "SYS.DBMS_METADATA", line 6718

    ORA-06512: at "SYS.DBMS_METADATA", line 9734

    ORA-06512: at line 1


    Out of curiosity, I still have my 19.4.0.354.1759, version, and tried looking at the Satements-Log panel, comparing both versions and they are similar, but have some differences. I attached both logs.


  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,708 Employee

    the database is basically saying your user doesn't have any awareness of that object, SQL Developer is just the messenger here...

  • User_88CHT
    User_88CHT Member Posts: 2 Green Ribbon

    Jeff -- The logs that the reporter posted here show a clear difference between version 19 and version 20. The version 19 does both:

    -- SELECT /*OracleDictionaryQueries.ALL_VIEW_TEXT_QUERY*/ TEXT_LENGTH, TEXT FROM SYS.ALL_VIEWS read from all_views, and

    -- dbms_metadata.get_ddl()

    The version 20 only does:

    -- dbms_metadata.get_ddl()

    You wrote:

    the database is basically saying your user doesn't have any awareness of that object

    That is not accurate. The user can select from the view. This inability to see the source within SQLDeveloper happens even if the user has both SELECT and DEBUG privileges on the view.

    This is also causing some friction at my company.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,708 Employee

    that info is already being shown on the details page, so if you want it, its' there

    dbms_metadata is the database, basically, we're making the request, it's not allowing you based on privs

    that panel represents the output from the API call to get the DDL for an object, and nothing else

  • User_88CHT
    User_88CHT Member Posts: 2 Green Ribbon

    Jeff -- I see it now on the Details tab. Thanks.

    I conclude from your statement that the version 19 behavior is not coming back. So from my point of view this is resolved. But I can't speak for the original poster.

  • wolf.goebel
    wolf.goebel Member Posts: 48 Blue Ribbon

    Wow, this is from a long time ago, already!

    Yes... this was resolved back then. I understand that the users permissions weren't correctly given for the use of dbms_metadata and that this isn't a "problem" for SQLDeveloper to correct.

    Thanks anyway!