Stored Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Stored Procedure

  1. #1
    Join Date
    Apr 2001
    Posts
    112
    Hi
    I have tables x,y,z .
    My procedure deletes all the tables and then populates each of them as follows:

    Create or Replace procedure Ins as
    Begin
    Delete from x;
    Insert into x
    select * from tmp;

    Delete from y;
    Insert into y
    select * from tmp2;

    Delete from z;
    Insert into z
    select * from tmp3;

    end;


    Could you please help me in answering the following questions.

    1) How should I error handle if I get errors at each insert stmt. Would the procedure at the error or just give me an error and proceed to the other inserts.
    2) How should incorporate the Exception handling, what kind of exceptions should I take care in this code.
    3) Should I put Commit statements after each Insert?

    Thanks

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    a). The procedure will exit, when an error occurs, therefore the process will not move on to the next statement.

    b). To find out if an exception occur and what the error is play the following exception of your code.

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);

    c). I would recommend placing a commit after each statment. Depends on the number of rows inserted and the size of your rollback segs also. You may want to consider a cursor insert and commiting after ever n records.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Apr 2001
    Posts
    112
    Hi grjohnson
    Can I make use of hte following:

    EXCEPTION
    When Others Then
    ErrMsg:=SQLERRM;
    RAISE_APPLICATION_ERROR (-20016,ErrMsg);
    END;

    Also when I run into an error on hte 3rd insert the above 2 inserts are made successfully.

    I have added a commit after each insert.

    Thanks

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I don't see why not, as long as you declare ErrMsg.

    b). If the first and second complete and third fails, but if the first failed and there was no exception handling, then the process would exit, and the second and third wouldn't complete.

    OCP 8i, 9i DBA
    Brisbane Australia

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