Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: RAISE_APPLICATION_ERROR and insert statemnt

    I tested the InsertLogMessage procedure and it works. The problem is the block won’t let me do both, RAISE the application and error AND make the insertion

    Into the message_log table –even when the insert statement comes before. Is there something I don’t’ know about RAISE_APPLICATION_ERROR here!>?

    Basically I want to do the regular RAISE… but I also want to insert the v_sqlerrm message into the message_log table…

    Any idea!?



    Thanks!!!




    SET VERIFY OFF

    SET SERVEROUTPUT ON


    BEGIN

    ajc57_api_midterm.add_student('&sv_salutation','&s v_first_name',

    '&sv_last_name', '&sv_street_address',

    '&sv_zip', '&sv_phone', '&sv_employer');

    EXCEPTION

    WHEN OTHERS THEN


    DECLARE

    -- Save the error message string

    v_sqlerrm VARCHAR2(250) := SUBSTR(SQLERRM,1,250); -- gives the first 250 characters


    BEGIN

    ajc57_api_midterm.InsertLogMessage(v_sqlerrm, 'add_student');

    RAISE_APPLICATION_ERROR(-20002,'A problem occurred with api_midterm_body.sql '||v_sqlerrm);


    END;

    END;

    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: RAISE_APPLICATION_ERROR and insert statemnt

    RAISE_APPLICATION_ERROR raises an exception, which means that any database work done in the procedure is rolled back. The answer is to modify the InsertLogMessage procedure to use an autonomous transaction by putting "PRAGMA AUTONOMOUS_TRANSACTION;" in its declaration, and adding a COMMIT at the end of the procedure.

Posting Permissions

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