Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: v$instance used inside a procedure throws error?

    Hi,

    I used the following select query from sqlplus command prompt and found it worked fine.

    Code:
    SQL> select host_name from v$instance;
    
    HOST_NAME
    ----------------------------------------------------------------
    swissql-lintest3

    When i tried a similar query (given below) with in a stored procedure,

    CREATE OR REPLACE PROCEDURE groupfun
    AS

    servername VARCHAR2(50);
    BEGIN


    SELECT host_name into groupfun.servername from v$instance;

    DBMS_OUTPUT.PUT_LINE('server name : '||servername);


    END;
    /

    show err
    /


    The following errors are thrown,
    Code:
    SQL> @servername_test.sql
    
    Warning: Procedure created with compilation errors.
    
    Errors for PROCEDURE GROUPFUN:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/3      PL/SQL: SQL Statement ignored
    6/34     PL/SQL: ORA-00942: table or view does not exist

    Is it not right to use the v$instance inside a procedure?

    I wud also like to know will it be a better option to use UTL_INADDR.get_host_name utilities or any other alternatives for v$instance?

    thanks and regards,
    Sn

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    The problem is your use of the variable. It is declared as servername and not groupfun.servernameso the select should be:

    SELECT host_name into servername from v$instance;

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I wouldn't agree with this explanation - such a variable declaration is correct; after all, the error says that TABLE OR VIEW does not exist. In other words, the problem is in v$instance.

    v$instance is a public synonym to the SYS owned view named v_$instance. Although v$instance is accessible in SQL, PL/SQL requires access to real tables/views; therefore, connect as SYS and issue such a statement:

    SYS> GRANT SELECT ON v_$instance TO your_user;

    Just to mention, if v$ synonyms weren't here at all, you should have ran rdbms/admin/catalog.sql which would then create them.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    I wouldn't agree with this explanation - such a variable declaration is correct
    Interesting I haven't seen this usage before (and I was not referring to the declaration but to the usage).
    Quote Originally Posted by Littlefoot
    v$instance is a public synonym to the SYS owned view named v_$instance. Although v$instance is accessible in SQL, PL/SQL requires access to real tables/views; therefore, connect as SYS and issue such a statement:
    Wow! You learn something new every day. Do you happen to know why you can't access synonyms in PL/SQL?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I know, Oracle decided to do it that way. I really wouldn't know the background of this decision.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    as a DBA you can access the v$ tables, because the select access is included in the dba ROLE. oracle doc states that privileges granted via roles are not inherited by stored objects. so, packages, procedures, VIEWS, etc need explicit grants, not grants via roles. and this is not just for sys or system objects, but for all users.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Without giving to many privs to low level users, you can't do what you want by using v$instance, however the following code works in all the versions of oracle (at least oracle 7 and up)

    The following will return the instance name

    select sys_context('USERENV','DB_NAME') from dual;

    The following will return the running host name
    select sys_context('USERENV','HOST') from dual;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the discussion, suggestions which helped me to learn new things,. i proceeded using the SYS_CONTEXT.

    Thanks again

    Regards,
    Sn

  9. #9
    Join Date
    Nov 2012
    Posts
    1
    Do one of the following
    -Log in as sys and query the table
    -Get the dba to grant admin priviledge to user then refer to the table as sys.v$instance

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by ekea View Post
    Do one of the following
    -Log in as sys and query the table
    -Get the dba to grant admin priviledge to user then refer to the table as sys.v$instance
    You never get a second chance to make a first impression.
    This was a six year old dead thread; so did you desire to resurrect it?

    The real problem is that privilege acquired via ROLE does not apply within PL/SQL procedure.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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