?
Solved

dbms_sql question in oracle7

Posted on 2003-03-10
3
Medium Priority
?
1,278 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
2 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

612 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