Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    17

    Post Unanswered: How to set out parameter in Exception

    Hi,

    I have created situation in procedure so that exception should occurs and
    have set a value of SQLCODE to out parameter.
    After executing procedure an exception occurs but out variable remains null.
    I want value in out parameter when an exeption occus.
    Please do let me know. How can I do this.


    e.g.

    CREATE OR REPLACE PROCEDURE GET_PRODUCT_WITH_DESC_SP
    ( P_PROD_CD Varchar2 := null,
    cur_our out sys_refcursor,
    p_return_val out number
    )
    AS
    v_sql Varchar2(1000);
    v_error_code varchar2(400);
    BEGIN

    .
    .
    .
    .

    EXCEPTION
    WHEN OTHERS THEN
    p_return_val := SQLCODE;
    v_error_code := 'An error has encountered. '|| SQLCODE || ' Error '|| SQLERRM;
    raise_application_error(-20001,v_error_code);
    End

    ---------------------------------------------------------------
    SQL> var v refcursor
    SQL> var val number
    SQL> exec GET_PRODUCT_WITH_DESC_SP('NO',:v,:val);

    BEGIN GET_PRODUCT_WITH_DESC_SP('NO',:v,:val); END;

    *
    ERROR at line 1:
    ORA-20001: An error has encountered. -933 Error Ora-00933: Sql Command Not Properly Ended
    ORA-06512: at "COBRA.GET_PRODUCT_WITH_DESC_SP", line 28
    ORA-06512: at line 1

    SQL> print val
    VAL
    ----------

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    of course it remains null, an error occurred so the normal return was never done.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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