Error in creating procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Error in creating procedure

  1. #1
    Join Date
    Sep 2006
    Posts
    114

    Error in creating procedure

    I am trying to create the following procedure

    1 CREATE OR REPLACE PROCEDURE ck_security AS
    2 cur integer;
    3 rc integer;
    4 v_sid number;
    5 v_serial number;
    6 v_username varchar2(100);
    7 v_machine varchar2(100);
    8 CURSOR c_user_security IS
    9 SELECT sid, serial#, username, terminal, machine, status
    10 FROM v$session;
    11 BEGIN
    12 FOR this IN c_user_security LOOP
    13 IF this.username = 'LISTEST' THEN
    14 IF this.terminal = 'JK1157' THEN
    15 null;
    16 ELSIF this.status <> 'KILLED' THEN
    17 v_sid := this.sid;
    18 v_serial := this.serial#;
    19 cur := DBMS_SQL.OPEN_CURSOR;
    20 DBMS_SQL.PARSE(cur,
    21 'ALTER SYSTEM KILL SESSION '''
    22 || v_sid
    23 || ','
    24 || v_serial
    25 || '''', DBMS_SQL.NATIVE);
    26 rc := DBMS_SQL.EXECUTE(cur);
    27 DBMS_SQL.CLOSE_CURSOR(cur);
    28 INSERT INTO ck_security_table
    29 VALUES
    30 ('Security Violation: '
    31 || this.username
    32 || ' accessed from '
    33 || this.machine
    34 || '('
    35 || this.terminal
    36 || ') on '
    37 || TO_CHAR(sysdate(),'MON-DD-YYYY HH24:MI:SS'));
    38 END IF;
    39 END IF;
    40 END LOOP;
    41* END ck_security;
    42 /

    Warning: Procedure created with compilation errors.

    SQL> show errors
    Errors for PROCEDURE CK_SECURITY:

    LINE/COL ERROR
    -------- -------------------------------------------------------
    9/1 PL/SQL: SQL Statement ignored
    10/6 PL/SQL: ORA-00942: table or view does not exist
    13/1 PL/SQL: Statement ignored

    Can anybody help in this regard?
    thanx in advance

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Most likely you do not have SELECT privilege on v$session.
    Last edited by SANJAY_G; 12-04-2006 at 03:14 AM.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Sep 2006
    Posts
    114
    Quote Originally Posted by SANJAY_G
    Most likely you do not have SELECT privilege on v$session.
    i am tring to create the procedure while i am logon as 'sys as sysdba'user

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Try with v_$session.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

Posting Permissions

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



Click Here to Expand Forum to Full Width