OraFAQ Forum: SQL & PL/SQL » V$SESSION Table

Home » SQL & PL/SQL » SQL & PL/SQL » V$SESSION Table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

V$SESSION Table [message #12705]

Tue, 01 June 2004 19:18

ReidR
Messages: 16
Registered: June 2004

Junior Member

I am relatively new to Oracle. Some documentation I found on the net explains creating a package to insert transaction errors. Part of that code uses V$SESSION and I can't seem to find how to access the table. I think it is a virtual typed table.

Here is the package body :

CREATE OR REPLACE PACKAGE BODY PckTransactionErrors IS

-- Private variable declarations
CURSOR sess IS
SELECT MACHINE, PROGRAM
--FROM SYS.DBMS_APPLICATION_INFO.V$SESSION
FROM SYS.V$SESSION
WHERE AUDSID = USERENV('SESSIONID');

rec sess%ROWTYPE;

-- Function and procedure implementations
PROCEDURE PutLine (code_in IN INTEGER, text_in IN VARCHAR2)
IS
BEGIN
INSERT INTO Transaction_Errors
VALUES (
code_in,
text_in,
SYSDATE,
USER,
SYSDATE,
USER,
rec.machine,
rec.program
);
END;

PROCEDURE SaveLine (code_in IN INTEGER, text_in IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
PutLine (code_in, text_in);
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK;
END;
BEGIN
OPEN sess; FETCH sess INTO rec; CLOSE sess;

end PckTransactionErrors;

AS you can see I tried to qualify the table in two different packages to no avail.

The compilation error is : (cursor on the V$SESSION)
"table or view does not exist".

Can someone explain this?

Thanks

Report message to a moderator

Re: V$SESSION Table [message #12706 is a reply to message #12705]

Tue, 01 June 2004 22:11

Deepak
Messages: 111
Registered: December 1999

Senior Member

You should have DBA privileges in order to access V$SESSION. Please check whether your program is run while logged in as a DBA user.

Report message to a moderator

Re: V$SESSION Table [message #12717 is a reply to message #12706]

Wed, 02 June 2004 07:23

ReidR
Messages: 16
Registered: June 2004

Junior Member

Hello,

I am logged into Oracle using PL/SQL Developer with a login that has DBA privledges.

Can you offer anything else?

Report message to a moderator

Re: V$SESSION Table [message #12719 is a reply to message #12717]

Wed, 02 June 2004 09:14

sverch
Messages: 582
Registered: December 2000

Senior Member

You have to have not ROLE privilege, but SCHEMA level privilege
grant select on v_$session to schema_name;

Report message to a moderator

Re: V$SESSION Table [message #12724 is a reply to message #12719]

Wed, 02 June 2004 13:05

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

And remove the hardcoded schema names. There is no SYS.V$SESSION. There is a public synonym V$SESSION for the view SYS.V_$SESSION.

Report message to a moderator

Re: V$SESSION Table [message #12735 is a reply to message #12719]

Thu, 03 June 2004 07:15

ReidR
Messages: 16
Registered: June 2004

Junior Member

Hello,

Thanks for the reply.

I removed the reference to the SYS package per the request. (Actually I had it that way to begin with, only tried the other packages to experiment)

If I use either PL/SQL Developer, SQL Plus, or SQL Worksheet, the result is the same. When I execute the following :

GRANT SELECT ON v_$session TO FGSDeveloper

It says the same error : ORA-00942: table or view does not exist

Report message to a moderator