Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    28

    Question Unanswered: HOW TO HAVE ACCESS TO "SYS" tables or VIEWS in a procedure PL/SQL

    I try to build a PL/SQL PROCEDURE in which i execute an
    select tablespace_name from v$sort_segment to get tablespace_name
    as this

    create or replace procedure mikevw.verif_temp is

    tablespace VARCHAR2(31);

    begin
    select tablespace_name into tablespace from v$sort_segment ;
    end ;

    and i've got errors to compile step


    1 create or replace procedure mikevw.verif_temp is
    2 tablespace VARCHAR2(31);
    3 begin
    4 select tablespace_name into tablespace from v$sort_segment ;
    5* end ;
    SQL> /

    Warning : Procedure created with errors
    SQL> show errors
    Errors for PROCEDURE MIKEVW.VERIF_TEMP :

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/1 PL/SQL: SQL Statement ignored
    4/45 PL/SQL: ORA-00942: Table ou vue inexistante

    What is the solution ??
    my schema and user has DBA Role
    thanks for your reply

    Best regards

    Sidnet

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    seems that the dba didn't

    grant select on v$sort_seqment to <usr>;

    mybe to is needed to write

    grant select on v_$sort_seqment to <usr>;

    to your user directly.

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Just so you know...

    There is a parameter in your init.ora called O7_DICTIONARY_ACCESSIBILITY:

    If TRUE, SYSTEM privileges, such as SELECT ANY TABLE, do not restrict access to objects in the SYS schema (Oracle7 behavior).

    If FALSE, users can only access objects in the SYS schema when granted the SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, or DELETE_CATALOG_ROLE.
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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