?
Solved

dbms_sql question in oracle7

Posted on 2003-03-10
3
Medium Priority
?
1,259 Views
Last Modified: 2012-08-13
Hi, here is the package procedure that I am running in oracle7.CREATE OR REPLACE PACKAGE KillIllegalUser
IS

  PROCEDURE StartJob;
  PROCEDURE StopJob;
  PROCEDURE IfUnauthorized;

END KillIllegalUser;
/
CREATE OR REPLACE PACKAGE BODY KillIllegalUser
IS
  PROCEDURE StartJob
  IS
  BEGIN
    sys.dbms_job.Isubmit(1, 'KillIllegalUser.IfUnauthorized;', SYSDATE, 'SYSDATE + 1/3000');
  END StartJob;

  PROCEDURE StopJob
  IS
  BEGIN
    sys.dbms_job.Remove(1);
  END StopJob;

  PROCEDURE IfUnauthorized
  IS
    CURSOR check_client_and_program_cur
     IS
      SELECT sid
            , serial#
             , RTRIM(username) username
             , RTRIM(module) module
             , RTRIM(lower(osuser)) osuser
         FROM SYS.V_$SESSION
        WHERE RTRIM(UPPER(username)) = 'TRAC_MGR'
         AND RTRIM(LOWER(osuser)) != 'torlink';          
           
    l_cursor  INTEGER;
    l_dummy   INTEGER;
     
     sid_l SYS.V_$SESSION.sid%TYPE;
    serial#_l SYS.V_$SESSION.serial#%TYPE;
    username_l SYS.V_$SESSION.username%TYPE;
    module_l SYS.V_$SESSION.module%TYPE;
    osuser_l SYS.V_$SESSION.osuser%TYPE;

  BEGIN
         FOR program_rec IN check_client_and_program_cur
         LOOP
          sid_l:=program_rec.sid;
          serial#_l:=program_rec.serial#;
         l_cursor := DBMS_SQL.Open_Cursor;
              DBMS_SQL.Parse (
                    l_cursor,
                  'ALTER SYSTEM KILL SESSION '''
                          || sid_l
                                || ','
                             || serial#_l
                             || '''',
                    DBMS_SQL.Native
                      );
                   
                   
          l_dummy := DBMS_SQL.Execute (l_cursor);
          DBMS_SQL.Close_Cursor (l_cursor);  

     END LOOP;
 
  END IfUnauthorized;

END KillIllegalUser;
/

The errors are:

PLS-00201: identifier 'SYS.V_$SESSION' must be declared
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
PL/SQL: Item ignored
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
PL/SQL: Item ignored
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
PL/SQL: Item ignored
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
PL/SQL: Item ignored
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
PL/SQL: Item ignored
PLS-00320: the declaration of the type of this expression is incomplete or malformed
PLS-00320: the declaration of the type of this expression is incomplete or malformed
PLS-00320: the declaration of the type of this expression is incomplete or malformed

Can some body help me with this please.

Thank you


0
Comment
Question by:ravikiran121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 300 total points
ID: 8104515
Log in as SYS, then:
grant select on v_$session to [procedure_owner];

PL\SQL objects need to have explicit grants on tables they reference (not grants via a role like the DBA role).
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9999340
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: markgeer {http:#8104515}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question