Results 1 to 15 of 15

Thread: Oracle Function

  1. #1
    Join Date
    Aug 2010
    Posts
    34

    Unanswered: Oracle Function

    Hi

    I am bit struck on this, trying to create a oracle function to kill the Hanged sessions .Its creating and compiling with Warning errors. Yours Earlier suggestions are highly appreciatable,



    Result,
    Warning: Function created with compilation errors.

    Please anyone,

    Thanks,
    Last edited by mr10; 08-02-10 at 13:35.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    After you compile in sqlplus, type

    show errors

    and sqlplus will tell you the errors and the lines they are on.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2010
    Posts
    34
    Thanks for the propt response,

    The Error is given below,


    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/1 PLS-00103: Encountered the symbol "IS" when expecting one of the
    following:
    return


    Regards,

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what datatype gets returned by function?
    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
    please let me know whether I miss anything here.

    Thanks,
    Last edited by mr10; 08-02-10 at 13:36.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The purpose of a FUNCTION is to return some datatype when it gets invoked.

    For your function, what datatype should it return to invoking session/process?
    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.

  7. #7
    Join Date
    Aug 2010
    Posts
    34
    Sorry, It suppose to return 'username' details. So that user can kill his own hanged sessions.
    I am still learning & trying on this,

    Thanks

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 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.

  9. #9
    Join Date
    Aug 2010
    Posts
    34
    Thanks, I had a quick run on that given link but I can't get corrected my function error issues on that, any other simpler way which helps to sort this out,


    Regards

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails Read The Fine Manual

    Overview of PL/SQL
    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.

  11. #11
    Join Date
    Aug 2010
    Posts
    34
    Well, If that's the way .... then I will, thanks anyway,
    Any other suggestions please

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any other suggestions please
    Provide complete & correct PL/SQL syntax

    RETURN clause is missing
    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.

  13. #13
    Join Date
    Aug 2010
    Posts
    34
    Yes, Now I got the different one now ..


    Warning: Function created with compilation errors.

    SQL> show errors
    Errors for FUNCTION KILL_USER:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/4 PL/SQL: SQL Statement ignored
    6/11 PL/SQL: ORA-00942: table or view does not exist
    9/4 PL/SQL: Statement ignored
    9/7 PLS-00201: identifier 'V_USERNAME' must be declared
    SQL>
    Last edited by mr10; 08-02-10 at 13:38.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It suppose to return 'username' details.

    So why are you "RETURN NUMBER"?

    Privilege acquired via ROLE do not apply within named PL/SQL procedure or function

    GRANT SELECT ON SYS.V_$SESSION TO <function_owner>;
    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.

  15. #15
    Join Date
    Aug 2010
    Posts
    34
    Thanks for your time,
    Last edited by mr10; 08-02-10 at 13:39.

Posting Permissions

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