RE: unhandled user-defined exception

[Venugopal, R (GEP, Contractor)]

Title: unhandled user-defined exception

I am attaching the source code of the trigger ,Please let me know where I made mistake .Thanks..

 

 

Venu

 

-----Original Message-----
From: Venugopal, R (GEP, Contractor)
Sent: Thursday, May 17, 2001 7:25 PM
To: Multiple recipients of list ORACLE-L
Subject: unhandled user-defined exception

Hi Gurus 
 
   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 
 
 
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;