RE: unhandled user-defined exception

<FONT color=#0000ff

  face=Arial size=2>I am attaching the source   code of the trigger ,Please let me know where I made mistake   .Thanks..
<FONT color=#0000ff

  face=Arial size=2> 
<FONT color=#0000ff

  face=Arial size=2> 
<FONT color=#0000ff

  face=Arial size=2>Venu
<FONT face=Tahoma

  size=2> 
<FONT face=Tahoma

  size=2>-----Original Message-----From: Venugopal, R (GEP,   Contractor) Sent: Thursday, May 17, 2001 7:25 PMTo:   Multiple recipients of list ORACLE-LSubject: unhandled user-defined   exception
  Hi Gurus    <FONT
  size=2>   I am getting the error attached below when trigger fires.   what could be the reasons for this error.     DB error: ORA-06510:
  PL/SQL: unhandled user-defined exception  <FONT   size=2>    Thanks in
  Advance Venu  

CREATE OR REPLACE TRIGGER dcs20.ESS_ORACLE_CUST_TRG1 BEFORE UPDATE OF NAME1, NAME2, KILLDATE, CUVATCD, USERDEFCOLUMN1, OFICUSTPROFCLASS, STREET1, STREET2, STREET3, CITY, USSTATECD, COUNTRYCD, ZIPCODE, TERMSPAY, COMPID ON EHDA.CUSTBILL
FOR EACH ROW
WHEN ((NEW.COMPNO = 17 OR NEW.COMPNO = 18) AND NEW.COMPID IS NOT NULL) DECLARE   var_extraction_date VARCHAR2(17);
  TTYPE VARCHAR2(10);

  CREDIT_FLG  ehda.A_ARCREDITPROF.ORDMANREL%TYPE;
  COLLECTOR   ehda.A_ARCREDITPROF.EMPLOYNO%TYPE;
  CREDIT_LMT  ehda.A_ARCREDITPROF.CREDLIM%TYPE;
  DUMMY       NUMBER(6);

  err_file_dir_GC  VARCHAR2(50):='/sngesstst/appl/dcs20/err/GC';
  err_file_dir_SEA  VARCHAR2(50):='/sngesstst/appl/dcs20/err/SEA';
  err_file_name_gl_GC  VARCHAR2(300) ;
  err_file_name_gl_SEA  VARCHAR2(300) ;
  err_file_h_GC          UTL_FILE.FILE_TYPE ;
  err_file_h_SEA         UTL_FILE.FILE_TYPE ;

  var_count              NUMBER;
  l_o_err_num            NUMBER:=0;
  l_n_err_num            NUMBER:=0 ;
  l_s_err_msg            VARCHAR2(500):=' ';
  l_s_err_string         VARCHAR2(300);
  v_rec_count            NUMBER;

BEGIN

      /* Record trigger date */
      SELECT
      TO_CHAR(SYSDATE,'YYYYMMDDHH24MISSSSS')
      INTO var_extraction_date
      FROM dual;

  SELECT ORDMANREL, EMPLOYNO, CREDLIM INTO CREDIT_FLG, COLLECTOR, CREDIT_LMT     FROM ehda.A_ARCREDITPROF
    WHERE COMPNO = :NEW.COMPNO
    AND ORGUNITGRP = 'CR'
    AND CUSTNOBILL = :NEW.CUSTNOBILL; END IF; EXCEPTION   WHEN OTHERS THEN      IF (:NEW.COMPNO='17') THEN       /* opening the error file for writing - GC*/           

err_file_name_gl_GC:='trigger_error_gc_ess_oracle_cust_trg1.'||var_extraction_date;

     err_file_h_GC := UTL_FILE.FOPEN(err_file_dir_GC, err_file_name_gl_GC, 'w') ;

        dbms_output.put_line('Very First Exception');

     UTL_FILE.PUT_LINE(err_file_h_GC, RPAD('*', 80, '*' ));
     UTL_FILE.PUT_LINE(err_file_h_GC,'CUSTOMER RETURNS (GL) TRIGGER fired AT : 
'||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH:MI:SS'));
     UTL_FILE.PUT_LINE(err_file_h_GC, RPAD('*', 80, '*' ));


         l_o_err_num := TO_NUMBER(SQLCODE) ;
         l_s_err_msg := SUBSTR(SQLERRM,1,100) ;
     
        dbms_output.put_line('First Exception');

         UTL_FILE.PUT_LINE(err_file_h_GC,l_o_err_num||':'||l_s_err_msg) ;
         UTL_FILE.FCLOSE(err_file_h_GC);
     END IF;

     IF (:NEW.COMPNO='18') THEN

   /* opening the error file for writing - SEA*/     

err_file_name_gl_SEA:='trigger_error_sea_ess_oracle_cust_trg1.'||var_extraction_date;

    err_file_h_SEA := UTL_FILE.FOPEN(err_file_dir_SEA, err_file_name_gl_SEA, 'w') ;

    UTL_FILE.PUT_LINE(err_file_h_SEA, RPAD('*', 80, '*' ));     UTL_FILE.PUT_LINE(err_file_h_SEA,'CUSTOMER RETURNS (GL) TRIGGER fired AT : '||TO_CHAR(SYSDATE, 'DD/MM/YYYY HH:MI:SS'));     UTL_FILE.PUT_LINE(err_file_h_SEA, RPAD('*', 80, '*' ));

        l_o_err_num := TO_NUMBER(SQLCODE) ;
         l_s_err_msg := SUBSTR(SQLERRM,1,100) ;

         UTL_FILE.PUT_LINE(err_file_h_SEA,l_o_err_num||':'||l_s_err_msg) ;
         UTL_FILE.FCLOSE(err_file_h_SEA);
     END IF;

END; Received on Thu May 17 2001 - 07:40:19 CDT