Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    12

    Question Unanswered: v$ views querys in pl/sql

    Hi,

    I want to create a pl/sql package to query the v$ views information, but the package is stored in a different schema of sys.

    The owner has the dba role granted and "select any table" privilege, but when I try to compile the package I get:
    PLS-00201: indentifier 'SYS.V$DATABASE' must be declared.

    I'm using Oracle 8.1.7.

    I'm missing some grant?...

    The owner of the package must be sys and there isn't possible to be create in a different schema?...

    Could anyone help me please, thank you in advance...
    Regards.

  2. #2
    Join Date
    Mar 2003
    Location
    Belgium
    Posts
    9

    Re: v$ views querys in pl/sql

    You should grant explicitly the grants for the views to the owner of the package. The grants via a role or a system privilege won't work.

    Regards.

    Originally posted by usandoval
    Hi,

    I want to create a pl/sql package to query the v$ views information, but the package is stored in a different schema of sys.

    The owner has the dba role granted and "select any table" privilege, but when I try to compile the package I get:
    PLS-00201: indentifier 'SYS.V$DATABASE' must be declared.

    I'm using Oracle 8.1.7.

    I'm missing some grant?...

    The owner of the package must be sys and there isn't possible to be create in a different schema?...

    Could anyone help me please, thank you in advance...
    Regards.

  3. #3
    Join Date
    Mar 2003
    Posts
    12

    Re: v$ views querys in pl/sql

    Hi Jef,

    Well, I tried that but I get:

    SQL> grant select on sys.v$database to kio;
    grant select on sys.v$database to kio
    *
    ERROR at line 1:
    ORA-02030: can only select from fixed tables/views

    It seems to me that the only way is to create the package inside the sys schema... but I don't like that because in some sites the customer doesn't want to share the sys account password...

    Maybe some guru trick can solve this...

    Thank you

  4. #4
    Join Date
    Mar 2003
    Posts
    12

    Re: v$ views querys in pl/sql

    Hi,

    I found the way... instead of granting select on v$database, it works if you grant to select on v_$database, and it's the same for all v$ views...

    My package is valid at least...

    Thank you and maybe this helps to some else.

    USF

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    You can easily overcome this problem by granting the user SELECT_CATALOG_ROLE. It will grant select ONLY on all the DBA_ and V$ views.


    Hope that helps,

    clio_usa - OCP - DBA


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •