Massenausnahme in postgresql




Mir ist die Zeile ( FOR Err IN 1 .. v_Num_Errors ) nicht bekannt, um Massenausnahmen oder normale Schleifenausnahmen in postgresql zu behandeln. Ich brauche Hilfe
---------------------------------------------------------------------------------------------------------------


DECLARE
     Cur_Temp_Invdt CURSOR FOR
      SELECT Cust_No
            ,Invfinderno
      FROM   Temp_Customer_Statement
            ,Invoice
      WHERE  Inv_Cust_No = Cust_No
             AND Inv_Finder_No = Invfinderno
             AND Inv_Disc_Rec IN ('DD', 'DR');

    /*TYPE t_Rec_Upd_Temp IS RECORD(
       Cust_No     Dbms_Sql.Varchar2_Table
      ,Invfinderno Dbms_Sql.Number_Table); */
	  
	 /* CREATE TYPE abc.t_Rec_Upd_Temp AS (Cust_No TEXT[],
	  Invfinderno NUMERIC[],
	  );*/

    l_Rec_Temp   t_Rec_Upd_Temp;
    Err_Num      TEXT;
    Err_Msg      TEXT;
    v_Num_Errors INTEGER := 0;
    p_Cust_No    TEXT;
    p_Finderno   INTEGER;
  
  
  BEGIN
    
	IF EXISTS(SELECT * FROM pg_cursors WHERE upper(name) = 'Cur_Temp_Invduedate') THEN
	CLOSE Cur_Temp_Invdt;
  END IF;

   /* OPEN Cur_Temp_Invdt;
    LOOP
      EXIT WHEN Cur_Temp_Invduedate%NOTFOUND;
      FETCH Cur_Temp_Invduedate BULK COLLECT
        INTO l_Rec_Temp LIMIT v_Limit; */

  OPEN Cur_Temp_Invduedate;
  FETCH Cur_Temp_Invduedate ----BULK COLLECT
    INTO l_Rec_Temp;
	
	
	
      --FORALL i IN 1 .. l_Rec_Temp.Cust_No.Count --SAVE EXCEPTIONS
	  
	  IF array_length(l_Rec_Temp,1) > 0 THEN 
	FOR e IN 1..array_length(l_Rec_Temp,1) LOOP
	  
	  
        UPDATE Temp_Customer_Statement
        SET    Invduedate = Invduedate || '*'
        WHERE  Cust_No = l_Rec_Temp.Cust_No[i]
               AND Invfinderno = l_Rec_Temp.Invfinderno[i];
    
    END LOOP;
   
	IF EXISTS(SELECT * FROM pg_cursors WHERE upper(name) = 'Cur_Temp_Invduedate') THEN
	CLOSE Cur_Temp_Invduedate;
  END IF;
END IF;
  
  
  EXCEPTION
    WHEN OTHERS THEN
      v_Num_Errors := SQLSTATE; --SQL%Bulk_Exceptions.Count;     			
IF EXISTS(SELECT * FROM pg_cursors WHERE upper(name) = 'Cur_Temp_Invduedate') THEN
	CLOSE Cur_Temp_Invduedate;
  END IF;
	

 
     FOR Err IN 1 .. v_Num_Errors
      LOOP
        l_Cust_No  := l_Rec_Temp.Cust_No(SQL%BULK_EXCEPTIONS(Err).Error_Index); 
       l_Finderno := l_Rec_Temp.Invfinderno(SQL%BULK_EXCEPTIONS(Err).Error_Index);
        Err_Num    := SQL%BULK_EXCEPTIONS(Err).Error_Index;
		 
        Err_Msg    := Substr(SQLERRM(-1 * SQL%BULK_EXCEPTIONS(Err).Error_Code), 1, 100);

        INSERT INTO test.Load_Err
        VALUES
          (Err_Num
          ,Err_Msg
          ,To_Number(To_Char(CURRENT_DATE, 'YYYYMMDD'))
          ,'CT_STMT'
          ,CONCAT_WS('', 'Cust No -', p_Cust_No, '-Finder no-',
					p_Finderno,
                     '- Customer_Statement - Update Temp_Customer_Statement for INVDUEDATE'));
     END LOOP; 
   
  END Upd_Temp_Invdt;

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.