Errors while creating procedure in Oracle

[Dave Walley]

Hi.

From time to time I run DML to identify what is going on in the database, the dml decodes some of the codes into text. I am now trying to create a procedure to do this using the following script, but I get an error. I run this script as system. Can anybody tell me what I am doing wrong.

Thanks in advance.

Dave

create or replace procedure curr_db_activity is
begin
SELECT sid, serial#, username,
DECODE(command, 0, 'None', 2, 'Insert', 3,
'Select', 6, 'Update', 7, 'Delete', 8, 'Drop', 'Other')
command from v$session where type <> 'BACKGROUND';
end;
/


Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE CURR_DB_ACTIVITY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
6/14 PLS-00201: identifier 'SYS.V_$SESSION' must be declared

[Steven E. Protter]

When scripting you sometimes have issue with special characters.

The $ in SYS.V_$SESSION requires a special character in perl to work correctly in a script.

SYS.V_\$SESSION I think

Same deal with v$session, even in shell scripting.


Here is an example.

perl

#!/usr/contrib/bin/perl

#$Argument = ($ARGV[0]);

$sqlstring = 'select username,status,logon_time,program,osuser from v\$session;'
;

#print "${Argument}\n";
$DML =<manager/manager1@jufsys
ttitle center 'User Session Report:' skip 2
column username format a8 heading 'Name' trunc
column status format a8 heading 'Status' trunc
column logon_time format a8 heading 'Start' trunc
column program format a44 heading 'Program' trunc
column osuser format a8 heading 'OS-User' trunc
set pagesize 24
set linesize 80
$sqlstring
eodml
$Input = `echo "$DML" | sqlplus`;
print $Input;


shell scripting

1 #!/bin/ksh
2
3 echo `date` >> /utmp/oracle/database.log
4 ORACLE_SID=jufdev
5 . /usr/contrib/bin/setOraProfile
6
7
8 sqlplus internal << EOFTOP
9 alter system switch logfile;
10 exit;
11 EOFTOP
12
13 ORACLE_SID=juftest
14 . /usr/contrib/bin/setOraProfile
15
16
17 sqlplus internal << EOFBOT
18 alter system switch logfile;
19 exit;
20 EOFBOT


Just examples of proper shell scripting technique.

I think its the dollar signs.

SEP

[Yogeeraj_1]

hi,

v$session is a synonym for 'SYS.V_$SESSION'

either you:
1. grant dba to yourself
2. as user SYS, grant select on SYS.V_$SESSION to the user in question.

this should fix that

regards
Yogeeraj

[Yogeeraj_1]

hi again,

a quote from my notes:


In order to create a trigger or procedure that references v$session, you need to:

SQL> grant select on v_$session to

when connected as SYS. v_$session is a view and can be granted on. v$session is a synonym for that view (and hence cannot be granted on, thats why you grant on v_$session, not v$session).

....

Some people create a view:

create view my_session_info
as
select * from V$session where audsid = userenv('sessionid');

grant select on my_session_info to public;


as SYS so everyone can see their session. You would be able to use that grant and view in your procedure|trigger as well.



hope this helps!

regards
Yogeeraj