Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2010
    Posts
    34

    Unanswered: need help to work this function

    I am a newbee and still learning. My function should kill the session if it falls under the condition or it need to return the message, no compilation warning err at all but still throws few pl/sql errors,

    create or replace function kill_user
    (v_sid number)
    RETURN NUMBER
    IS
    v_user varchar2(30);
    v_serial NUMBER;
    BEGIN
    select username, serial# into v_user, v_serial from v$session where sid = v_sid;
    IF( v_user in ('SIEBEL', 'EIMADMIN', 'SADMIN', 'SUPPSVC'))
    THEN
    execute immediate 'alter system kill session '''||v_sid||','||v_serial||'''';
    ELSE
    dbms_output.put_line('You are only authorised to kill SIEBEL, EIMADMIN, SADMIN or SUPPSVC sessions.');
    END IF;
    END;
    /

    After the run,

    select kill_user(362) from dual
    *
    ERROR at line 1:
    ORA-06503: PL/SQL: Function returned without value
    ORA-06512: at "SYS.KILL_USER", line 15

    Still scratching my head on this, any suggestions

    Thanks,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR replace FUNCTION Kill_user (v_sid NUMBER)
    RETURN NUMBER
    IS
      v_user   VARCHAR2(30);
      v_serial NUMBER;
    BEGIN
      SELECT username,
             serial#
      INTO   v_user, v_serial
      FROM   v$session
      WHERE  sid = v_sid;
    
      IF( v_user IN ( 'SIEBEL', 'EIMADMIN', 'SADMIN', 'SUPPSVC' ) ) THEN
        EXECUTE IMMEDIATE 'alter system kill session '''||v_sid||','||v_serial||''''
        ;
      ELSE
    dbms_output.Put_line('You are only authorised to kill SIEBEL, EIMADMIN, SADMIN or SUPPSVC sessions.');
    END IF;
    RETURN V_USER;
    END;
    /
    Last edited by anacedent; 08-03-10 at 10:55.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2010
    Posts
    34
    Thanks the response, I appreciate
    But still few warning errors

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    20/1 PLS-00103: Encountered the symbol "END" when expecting one of the
    following:
    . ( * @ % & = - + ; < / > at in is mod remainder not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
    LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
    The symbol ";" was substituted for "END" to continue.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Return v_user;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2010
    Posts
    34
    Thanks, now no warning errors but when i run the function to kill a session, its returning the following errors,


    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "SYS.KILL_USER", line 19

  6. #6
    Join Date
    Aug 2010
    Posts
    34
    Its fine now as I changed the the RETURN VARCHAR2 & its working fine.
    Thanx for your help, well appreciated

  7. #7
    Join Date
    Aug 2010
    Posts
    34
    Bit strange, when I tested I found that It's killing sessions which falls in the condition which is Fine, But
    When I try to kill non authorized User, it suppose to return Else condition Message 'You are only authorised to kill SIEBEL, EIMADMIN, SADMIN or SUPPSVC sessions.' But its not it just returning

    KILL_USER(313)
    --------------------------------------------------------------------------
    PILLA05

    Any Suggestions please,

    Thanks,

  8. #8
    Join Date
    Aug 2010
    Posts
    34
    Its working fine now as I replaced
    DBMS_OUTPUT.PUT_LINE with a simple RETURN


    (dunno why this dbms_output.put_line is not working ...)


    Thanks,

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In my opinion, DBMS_OUTPUT is a debugging aid & should never exist within Production application.
    PL/SQL runs deep inside RDBMS engine & does not have any direct contact with end user.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Aug 2010
    Posts
    34
    Thanks, It makes sense.
    Jus out of eagerness, If I want to compile this Function Kill_user as a procedure, Is there any easier ways,


    Thanks,

    Mohammed

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If I want to compile this Function Kill_user as a procedure, Is there any easier ways,
    I do not understand this question.

    Is what easier than what?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Aug 2010
    Posts
    34
    As per the requirement again as need to compile as a procedure rather than function, I tried something like this, no compilation error though


    CREATE OR replace procedure Kill_user (v_sid NUMBER)
    AS
    v_user VARCHAR2(30);
    v_serial NUMBER;
    BEGIN
    SELECT username,
    serial#
    INTO v_user, v_serial
    FROM v$session
    WHERE sid = v_sid;

    IF( v_user IN ( 'SIEBEL', 'EIMADMIN', 'SADMIN', 'SUPPSVC' ) ) THEN
    EXECUTE IMMEDIATE 'alter system kill session '''||v_sid||','||v_serial||''''
    ;
    ELSE
    raise_application_error(-2000,'You are only authorised to kill SIEBEL, EIMADMIN, SADMIN or SUPPSVC sessions.');
    END IF;
    END;
    /

    But
    RESULT

    SQL> EXEC KILL_USER(356);
    BEGIN KILL_USER(356); END;

    *
    ERROR at line 1:
    ORA-21000: error number argument to raise_application_error of -2000 is out of
    range
    ORA-06512: at "SYS.KILL_USER", line 16
    ORA-06512: at line 1

    Any Suggestions please,

    Thanks,

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    21000, 00000, "error number argument to raise_application_error of %s%s is out of range"
    // *Cause:  
    // *Action: Use an error number in the range of -20000 to -20999, inclusive.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Aug 2010
    Posts
    34
    sorry, not clear to me as I am quiet a beginner in this Func/Proc/Trigg still trying & learning, I tried with different ranges like
    with 20000, with 1000 but this time I got,

    ERROR at line 1:
    ORA-21000: error number argument to raise_application_error of -1000 is out of
    range
    ORA-06512: at "SYS.KILL_USER", line 16
    ORA-06512: at line 1



    Thanks,

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  CREATE OR replace procedure sample_proc
      2  AS
      3  BEGIN
      4  raise_application_error(-20200,'sample ERROR 20200');
      5* END;
    SQL> /
    
    Procedure created.
    
    SQL> exec sample_proc;
    BEGIN sample_proc; END;
    
    *
    ERROR at line 1:
    ORA-20200: sample ERROR 20200
    ORA-06512: at "SYS.SAMPLE_PROC", line 4
    ORA-06512: at line 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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