1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Improve my exception handling code for Table Locking

Discussion in 'SQL PL/SQL' started by salmankhalid, May 6, 2009.

  1. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    when ever there is some sort of updating or deleting occur on my form i have to check fr the exception

    but there is still locking exist tell me what should i do for the better improvement of my exception handling code


    Code (SQL):
    DECLARE
       v_rowid           ROWID;
       resource_busy1    EXCEPTION;
       resource_busy2    EXCEPTION;
       PRAGMA EXCEPTION_INIT (resource_busy1, -54);
       PRAGMA EXCEPTION_INIT (resource_busy2, -30006);
       v_failure_cause   VARCHAR2 (500);
       crlf              VARCHAR2 (2)   := CHR (13) || CHR (10);
    BEGIN
       FOR i IN (SELECT     ROWID
                       FROM pathology.pathology_result r
                      WHERE r.section_no = 'RCH08000002' AND r.serial_no = 1
                 FOR UPDATE NOWAIT)
       LOOP
          --- Delete the row
          BEGIN
             DELETE FROM pathology.pathology_result
                   WHERE ROWID = i.ROWID;
          EXCEPTION
             WHEN OTHERS
             THEN
                v_failure_cause :=
                   'LOCK ON TABLE pathology.pathology_result, PLEASE TRY AFTER FEW SECONDS '
                   || crlf
                   || 'PR_NO:'
                   || :cpt_return_detail.cpt_return_no
                   || crlf
                   || 'SR_NO:'
                   || :cpt_return_detail.serial_no;
                display_alert ('MESSAHE', v_failure_cause || ' ' || SQLERRM);
                ROLLBACK;
                RAISE form_trigger_failure;
          END;
       --dbms_output.put_line('lock successful...' || i.ROWID);
       END LOOP;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          v_failure_cause :=
                'LOCK ON TABLE pathology.pathology_result, PLEASE TRY AFTER FEW SECONDS '
             || crlf
             || 'PR_NO:'
             || :cpt_return_detail.cpt_return_no
             || crlf
             || 'SR_NO:'
             || :cpt_return_detail.serial_no;
          display_alert ('MESSAHE', v_failure_cause || ' ' || SQLERRM);
          ROLLBACK;
          --ERROR_FORM(v_failure_cause,:PARAMETER.P_OBJECT_CODE);
          --EXECUTE_QUERY;
          --GO_RECORD(CURR_RECORD);
          RAISE form_trigger_failure;
       WHEN resource_busy1 OR resource_busy2
       THEN
          v_failure_cause :=
                'LOCK ON TABLE pathology.pathology_result, PLEASE TRY AFTER FEW SECONDS '
             || crlf
             || 'PR_NO:'
             || :cpt_return_detail.cpt_return_no
             || crlf
             || 'SR_NO:'
             || :cpt_return_detail.serial_no;
          display_alert ('MESSAHE', v_failure_cause || ' ' || SQLERRM);
          ROLLBACK;
          --ERROR_FORM(v_failure_cause,:PARAMETER.P_OBJECT_CODE);
          --EXECUTE_QUERY;
          --GO_RECORD(CURR_RECORD);
          RAISE form_trigger_failure;
       WHEN OTHERS
       THEN
          v_failure_cause :=
                'LOCK ON TABLE pathology.pathology_result, PLEASE TRY AFTER FEW SECONDS '
             || crlf
             || 'PR_NO:'
             || :cpt_return_detail.cpt_return_no
             || crlf
             || 'SR_NO:'
             || :cpt_return_detail.serial_no;
          display_alert ('MESSAHE', v_failure_cause || ' ' || SQLERRM);
          ROLLBACK;
          --ERROR_FORM(v_failure_cause,:PARAMETER.P_OBJECT_CODE);
          --EXECUTE_QUERY;
          --GO_RECORD(CURR_RECORD);
          RAISE form_trigger_failure;
    END;
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    salmankhalid likes this.