Exception propogation question

From: <Rick_Cale_at_teamhealth.com>
Date: Tue, 29 Jan 2002 14:15:59 -0800
Message-ID: <F001.003FFA8D.20020129141536@fatcity.com>

Resending as it did not go thru the 1st time ----- Forwarded by Rick Cale/CORP/TeamHealth on 01/29/2002 05:17 PM -----

                                                                                       
                            
                    Rick Cale                                                          
                            
                                         To:     ORACLE-L_at_fatcity.com, 
oracledba_at_lazydba.com                       
                    01/29/2002           cc:                                           
                            
                    05:11 PM             Subject:     Exception propogation question   
                            
                                                                                       
                            
                                                                                       
                            

Hi DBAs,

procedure load_patreg_table calls insert_pat_reg_info. As you can see insert_pat_reg_info has an exception handler with a RAISE statement If an insert error occurs I get a record inserted into error log table then exception propogates up to calling program and another record gets inserted into error_log table. WHY? I thought by putting in the RAISE it would not propogate.
Can someone explain how to control propogation/

Thanks
Rick

PROCEDURE load_PatReg_table(num_of_days_to_go_back IN INTEGER,

       v_idx_hospital_id IN OUT
patreg.idx_hospid_lookup.idx_hospital_id%TYPE) IS

BEGIN

     OPEN adt_visits_cur(num_of_days_to_go_back);
     LOOP
        fetch adt_visits_cur INTO adt_visits_rec;
        exit when adt_visits_cur%notfound;
        v_idx_hospital_id := get_idx_hospital_id();
        insert_pat_reg_info(v_idx_hospital_id);
      END LOOP;
     COMMIT;
     EXCEPTION
        WHEN OTHERS THEN

error_log_pkg.insert_error_log_table(SYSDATE,USER,'patient_registration_pkg.load_patreg_table

failed',

                                               'SQLCODE - '||SQLCODE||'
SQLERRM - '||SQLERRM);
     CLOSE adt_visits_cur;

END load_PatReg_table ;

PROCEDURE insert_pat_reg_info(v_idx_hospital_id IN patreg.idx_hospid_lookup.idx_hospital_id%TYPE) IS BEGIN
   INSERT INTO patreg.patient_registration_info

       (patient_site_id,
        adt_seq_id,
        date_of_service,
        idx_hospital_id)
   VALUES (adt_visits_rec.patient_site_id,
           adt_visits_rec.adt_seq_id,
           adt_visits_rec.admission_datetime,
           v_idx_hospital_id);
   EXCEPTION
           WHEN OTHERS THEN

error_log_pkg.insert_error_log_table(SYSDATE,USER,'patient_registration_pkg.insert_pat_reg_info

failed',

                 'SQLCODE - '||SQLCODE||' SQLERRM - '||SQLERRM);
           RAISE;

END insert_pat_reg_info;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 29 2002 - 16:15:59 CST