FOR ALL, BULK COLLECT, BULK COLLECT LIMIT , BULK EXCEPTIONS in Postgresql

Leute,

Ich habe den Orakelcode beigefügt, der diese enthält
FÜR ALLE, BULK COLLECT, BULK COLLECT LIMIT, BULK AUSNAHMEN, GOTO . Ich stecke in der Migration zu Postgresql fest. Hinzugefügt – # 1, # 2 #3#8 Ich stecke dort fest. Ich brauche Hilfe

Versucht, aber in der Mitte stecken geblieben. Dachte, nur Orakelcode zu geben.


create or replace PROCEDURE   Spr_abc_Customer_Statement(v_Ismonthly_Run IN CHAR   ,v_Pole_Id     IN NUMBER) AS
 
  v_Pol_Main_Lang                CHAR(1);
  v_Input_Date VARCHAR2(10);
  
  CURSOR Cur_Cc_No IS
    SELECT Cc_No_705
          ,Cc_Stmt_Payor_Level_705
          ,Cc_Stmt_Multi_Lang_705
    FROM   Cc_Organization
    WHERE  Cc_State_Req_Sw_705 = 'Y'
           AND Nvl(Cc_Pole_Id_705, 0) = v_Pole_Id;
  
---- #1  
  TYPE Cc_No_705_Tab IS TABLE OF Cc_Organization.Cc_No_705%TYPE;
  t_Cc_No_705 Cc_No_705_Tab;

  TYPE Cc_Stmt_Payor_Level_705_Tab IS TABLE OF Cc_Organization.Cc_Stmt_Payor_Level_705%TYPE;
  t_Cc_Stmt_Payor_Level_705 Cc_Stmt_Payor_Level_705_Tab;

  TYPE Cc_Stmt_Multi_Lang_705_Tab IS TABLE OF Cc_Organization.Cc_Stmt_Multi_Lang_705%TYPE;
  t_Stmt_Multi_Lang_705 Cc_Stmt_Multi_Lang_705_Tab;

  -- Main Cursor
  CURSOR Cur_Invoice IS
    
	select * from abc.test where id = 67767;
-- #2
  TYPE t_Rec IS RECORD(
     Cust_Parent_Level_Flag   Dbms_Sql.Varchar2_Table
        ,Inv_Date                 Dbms_Sql.Number_Table);
  
  Cur_Invoice_Record t_Rec;

  CURSOR Cur_Letter_Sob IS
    SELECT Nvl(Letter_Desc_789, ' ') Letter_Desc
    FROM   Letter_And_Statement_Detail
    WHERE  Letter_No_789 = 'STMNT'
     ORDER  BY Letter_Code_789
             ,Letter_Line_No_789;
  
  Cur_Letter_Rec_Sob Cur_Letter_Sob%ROWTYPE;
  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~

  CURSOR Cur_Letter_Sob_Acgrp IS
    SELECT Nvl(Letter_Desc_789, ' ') Letter_Desc
          ,Nvl(Letter_Skip_Nl_789, 'N') Skip_Nl
    FROM   Letter_And_Statement_Detail
    WHERE  Letter_No_789 = 'STMNT';
          
  Cur_Letter_Rec_Sob_Acgrp Cur_Letter_Sob_Acgrp%ROWTYPE;
 
  CURSOR Cur_Letter_Sob_Acgrp_Curr IS
    SELECT Nvl(Letter_Desc_789, ' ') Letter_Desc
          ,Nvl(Letter_Skip_Nl_789, 'N') Skip_Nl
    FROM   Letter_And_Statement_Detail
    WHERE  Letter_No_789 = 'STMNT'
           AND Letter_Lang_789 = v_Cust_Lang
               ORDER  BY Letter_Code_789
             ,Letter_Line_No_789;
			 
  Cur_Letter_Rec_Sob_Acgrp_Curr Cur_Letter_Sob_Acgrp_Curr%ROWTYPE;

  CURSOR Cur_Ageing IS
     select abc,bde from test.abc where id =909090;
  
  Cur_Ageing_Record Cur_Ageing%ROWTYPE;
  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
  v_Limit NUMBER := 15000;

BEGIN

  SELECT Nvl(Pol_Main_Lang_799, ' ') Pol_Main_Lang
      INTO   v_Pol_Main_Lang
        FROM   System_Policy;

  v_Temp_Cust_No         := ' ';
    Inv_Stat_Amt           := 0;

-- #3 A
 
  IF Cur_Cc_No%ISOPEN THEN
    CLOSE Cur_Cc_No;
  END IF;

  OPEN Cur_Cc_No;
  FETCH Cur_Cc_No BULK COLLECT
    INTO t_Cc_No_705
        ,t_Cc_Stmt_Payor_Level_705
        ,t_Stmt_Multi_Lang_705;
  CLOSE Cur_Cc_No;

-- #3 B
  FOR j IN 1 .. t_Cc_No_705.Count
  LOOP
    v_Cc_No_705 := t_Cc_No_705(j);
    v_Cc_Stmt_Payor_Level_705 := Nvl(t_Cc_Stmt_Payor_Level_705(j), ' ');
    v_Cc_Stmt_Multi_Lang_705 := Nvl(t_Stmt_Multi_Lang_705(j), 'N');

    <<loop_Cur_Invoice>>
    BEGIN
      IF Cur_Invoice%ISOPEN THEN
        CLOSE Cur_Invoice;
      END IF;

      OPEN Cur_Invoice;
      LOOP
        EXIT WHEN Cur_Invoice%NOTFOUND;

      --#4 
	  FETCH Cur_Invoice BULK COLLECT
          INTO Cur_Invoice_Record LIMIT v_Limit;

        FOR i IN 1 .. Cur_Invoice_Record.Cust_Cc_No.Count
        LOOP
          v_Letter_Desc := NULL;
          Licnt         := 0;
          v_Cc_Owner    := Cur_Invoice_Record.Cust_Cc_No(i);

          BEGIN
            SELECT Cc_Manager_Coll_Name_705
                              INTO   v_Coll_Name
                  ,
            FROM   Cc_Organization Cc
                  ,Personnel       p
            WHERE  Cc_No_705 = v_Cc_Owner;
    
          EXCEPTION
            WHEN No_Data_Found THEN
              v_Coll_Name  := '';
                       END;

          BEGIN
            SELECT Email_Id_Formatted
            INTO   v_From_Email_Id
            FROM   Email_Statement
            WHERE  TRIM(Email_Id) = TRIM(Cur_Invoice_Record.Cust_From_Emailid(i));
          EXCEPTION
            WHEN No_Data_Found THEN
              v_From_Email_Id := '';
          END;
          IF Cur_Invoice_Record.Other_Table_Tc_Flag3_794(i) IS NULL OR
             Cur_Invoice_Record.Other_Table_Tc_Flag3_794(i) <> 'Y' THEN
            v_Inv_Cust_No          := Cur_Invoice_Record.Inv_Cust_No_720(i);
            v_Cust_Last_Stmt_Dt    := '';
            v_Cust_Last_Cheq_Amt   := 0;
          
            IF v_Set_Of_Book <> v_Temp_Sb THEN
              v_Temp_Sb := v_Set_Of_Book;
            END IF;

            SELECT COUNT(*)
            INTO   v_Cc_Count
            FROM   Dtld_Cores_Conf_Cnc_Connector
            WHERE  Dtld_Cores_Conf_Id =999999;
                   

            --Get Remit To Address
            BEGIN
              IF Cur_Invoice_Record.Cust_Remit_To_No(i) = ' ' THEN
                <<backtoremit>>

                BEGIN
                  IF v_Cc_Count > 0 THEN
                    v_Temp_Set_Of_Book := v_Set_Of_Book;
                    
					SELECT COUNT(1)
                    INTO   v_Remit_Cnt
                    FROM   Remit_To_Address
                    WHERE  Remit_To_Cc_No_712 = v_Cc_Owner AND Remit_To_Cust_Currency_712 = Cur_Invoice_Record.Currency(i);
                   
				   IF v_Remit_Cnt = 0 THEN
                      v_Temp_Set_Of_Book := ' ';
                      SELECT COUNT(1)
                      INTO   v_Remit_Cnt
                      FROM   Remit_To_Address
                      WHERE  Remit_To_Cc_No_712 = v_Cc_Owner
                             AND Remit_To_Set_Of_Book_712 = ' '
                             AND Remit_To_Cust_Currency_712 =
                             Cur_Invoice_Record.Currency(i);
                    END IF;
                    IF v_Remit_Cnt = 0 THEN
                      SELECT Cc_Owner_No_705
                      INTO   v_Cc_Owner
                      FROM   Cc_Organization
                      WHERE  Cc_No_705 = v_Cc_Owner;

                      GOTO Backtoremit;
                    END IF;

                    SELECT Nvl(Remit_To_Name_712, ' ') Remit_To_Name
                         
                    INTO   v_Remit_To_Name
                         
                    FROM   Remit_To_Address
                    WHERE  Remit_To_Cc_No_712 = v_Cc_Owner
                           AND Remit_To_Set_Of_Book_712 = v_Temp_Set_Of_Book;
                          
                  ELSE

--#5                 
				 <<backtoremit1>>
                    v_Temp_Set_Of_Book := v_Set_Of_Book;
                    SELECT COUNT(1)
                    INTO   v_Remit_Cnt
                    FROM   Remit_To_Address
                    WHERE  Remit_To_Cc_No_712 = v_Cc_Owner
                     AND Remit_To_Cust_Currency_712 = ' ';
					 
					 
                    IF v_Remit_Cnt = 0 THEN
                      v_Temp_Set_Of_Book := ' ';
                      SELECT COUNT(1)
                      INTO   v_Remit_Cnt
                      FROM   Remit_To_Address
                      WHERE  Remit_To_Cc_No_712 = v_Cc_Owner;
                   
                    END IF;
                    IF v_Remit_Cnt = 0 THEN
                      SELECT Cc_Owner_No_705
                      INTO   v_Cc_Owner
                      FROM   Cc_Organization
                      WHERE  Cc_No_705 = v_Cc_Owner;
					  
                      --#6 
					  GOTO Backtoremit1;
                    END IF;
                    
					SELECT Nvl(Remit_To_Name_712, ' ') Remit_To_Name
                                              INTO   v_Remit_To_Name
                       
                    FROM   Remit_To_Address
                    WHERE  Remit_To_Cc_No_712 = v_Cc_Owner;
                        
                  END IF;

                EXCEPTION
                  WHEN OTHERS THEN
                    SELECT Cc_Owner_No_705
                    INTO   v_Cc_Owner
                    FROM   Cc_Organization
                    WHERE  Cc_No_705 = v_Cc_Owner;
                    GOTO Backtoremit;
                END;
              ELSE
                SELECT Nvl(Cremit_To_Name_781, ' ')
                   
                INTO   v_Remit_To_Name
                      
                WHERE  Cremit_To_No_781 = Cur_Invoice_Record.Cust_Remit_To_No(i);
              END IF;
            EXCEPTION
              WHEN No_Data_Found THEN
                v_Remit_To_Name  := '';
              
              WHEN OTHERS THEN
                Dbms_Output.Put_Line(SQLCODE);
                Dbms_Output.Put_Line(SQLERRM);
            END;
            SELECT COUNT(1)
            INTO   v_Cnt
            FROM   Letter_And_Statement_Header
            WHERE  Letter_No_788 = 'STMNT'
                   AND Letter_Lang_788 = v_Cust_Lang;
                

            IF v_Cnt = 0 THEN
              v_Remark := ' ';
            ELSE
              v_Remark := v_Set_Of_Book;
            END IF;
   
            v_Acct_Group := Cur_Invoice_Record.Acct_Group(i);
        
            OPEN Cur_Letter_Sob_Acgrp_Curr;
            LOOP
              FETCH Cur_Letter_Sob_Acgrp_Curr
                INTO Cur_Letter_Rec_Sob_Acgrp_Curr;
              EXIT WHEN Cur_Letter_Sob_Acgrp_Curr%NOTFOUND;
             
			 IF Licnt = 0 THEN
                v_Letter_Desc := Cur_Letter_Rec_Sob_Acgrp_Curr.Letter_Desc;
                Licnt         := Licnt + 1;
              ELSE
                IF Cur_Letter_Rec_Sob_Acgrp_Curr.Skip_Nl = 'Y' THEN
                  v_Letter_Desc := v_Letter_Desc || ' ' ||
                                   Cur_Letter_Rec_Sob_Acgrp_Curr.Letter_Desc;
                ELSE
                  v_Letter_Desc := v_Letter_Desc || Chr(10) || Chr(13) ||
                                   Cur_Letter_Rec_Sob_Acgrp_Curr.Letter_Desc;
                END IF;
              END IF;
            END LOOP;
            CLOSE Cur_Letter_Sob_Acgrp_Curr;
            --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
            IF v_Letter_Desc IS NULL THEN
              OPEN Cur_Letter_Sob_Acgrp;
              LOOP
                FETCH Cur_Letter_Sob_Acgrp
                  INTO Cur_Letter_Rec_Sob_Acgrp;
                EXIT WHEN Cur_Letter_Sob_Acgrp%NOTFOUND;
                IF Licnt = 0 THEN
                  v_Letter_Desc := Cur_Letter_Rec_Sob_Acgrp.Letter_Desc;
                  Licnt         := Licnt + 1;
                ELSE
                  IF Cur_Letter_Rec_Sob_Acgrp.Skip_Nl = 'Y' THEN
                    v_Letter_Desc := v_Letter_Desc || ' ' ||
                                     Cur_Letter_Rec_Sob_Acgrp.Letter_Desc;
                  ELSE
                    v_Letter_Desc := v_Letter_Desc || Chr(10) || Chr(13) ||
                                     Cur_Letter_Rec_Sob_Acgrp.Letter_Desc;
                  END IF;
                END IF;
              END LOOP;
              CLOSE Cur_Letter_Sob_Acgrp;
            END IF;

            IF v_Letter_Desc IS NULL THEN
              Licnt := 0;
              OPEN Cur_Letter_Sob;
              LOOP
                FETCH Cur_Letter_Sob
                  INTO Cur_Letter_Rec_Sob;
                EXIT WHEN Cur_Letter_Sob%NOTFOUND;
                IF (Cur_Letter_Rec_Sob.Letter_Desc IS NOT NULL AND Licnt < 5) THEN
                  v_Letter_Desc := v_Letter_Desc || ' ' ||
                                   Cur_Letter_Rec_Sob.Letter_Desc;
                  Licnt         := Licnt + 1;
                END IF;
              END LOOP;
              CLOSE Cur_Letter_Sob;
            END IF;
            --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
            v_Inv_No := Cur_Invoice_Record.Inv_No(i);
            IF v_Set_Of_Book = 'J' AND v_Cc_Owner = 'CJ04' THEN
              v_Letter_Desc := v_Letter_Desc ||
                               ' IF PAYMENT IS ALREADY MADE PLEASE IGNORE THIS STATEMENT.';
            ELSIF v_Set_Of_Book = 'A' THEN
              v_Letter_Desc := v_Letter_Desc ||
                               ' FOR ACCURATE AND PROMPT CREDITING OF YOUR PAYMENTS, PLEASE SEND ALL REMITTANCE INSTRUCTIONS TO OUR EMAIL ADDRESS LISTED BELOW.  PLEASE INCLUDE YOUR ACCOUNT NUMBER AND INVOICES TO BE PAID. EMAIL ADDRESS: Aviationstatements.WCS@ge.com';
            ELSIF v_Co_Hyphen = 'Y' AND
                  Cur_Invoice_Record.Inv_Payment_Sched_Id(i) <> ' ' THEN
              v_Inv_No := Cur_Invoice_Record.Inv_Payment_Sched_Id(i);
            END IF;
            --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
            BEGIN
              SELECT Nvl(Err_Mess_Desc_783, ' ')
              INTO   v_Attention
              FROM   Error_Message
              WHERE  Err_Mess_No_783 = '369';
          
            EXCEPTION
              WHEN No_Data_Found THEN
                v_Attention := '';
            END;
            Mdblbalance          := 0;
            Mdblinvoicework      := Cur_Invoice_Record.Inv_Amt(i) -
                                    Cur_Invoice_Record.Inv_Amt_Paid(i);
     

            IF (Cur_Invoice_Record.Inv_Set_Book(i) = 'T' AND
               Cur_Invoice_Record.Cust_Int_Rate_Sw(i) = 'Y' AND
               To_Date(Cur_Invoice_Record.Inv_Due_Date(i)) < To_Date(v_Input_Date) AND
               Mdblrptamtdue > 0) THEN
              Mdblrptamtdue := (Mdblrptamtdue * 1.02);
      
            END IF;

            Mdbloutstandamt := Mdblinvoicework;
            --Last payment details
            IF Cur_Invoice_Record.Cust_Cd_Mess_Sw(i) = 'Y' THEN
              v_Cust_Last_Cheq_Amt := Cur_Invoice_Record.Cust_Last_Cheq_Amt(i);
          

              BEGIN
                SELECT Nvl(Rpt_Desc_782, ' ')
                INTO   v_Chq_Desc
                FROM   Report_Description
                WHERE Rpt_Lang_782 = Cur_Invoice_Record.Cust_Lang(i);

              EXCEPTION
                WHEN No_Data_Found THEN
                  v_Chq_Desc := '';
              END;
            END IF;
   
            BEGIN
              SELECT MAX(Connection_Arc_No_690)
              INTO   v_Conn_Arc_No
              FROM   Arc_Invoice_Connector
              WHERE  Connection_Cust_No_690 = v_Inv_Cust_No;
              
					 
              SELECT Arc_No_729 || Arc_Sub_Tc_729
              INTO   v_Arc_No
              FROM   Arc
              WHERE  Arc_No_729 = v_Conn_Arc_No;
            EXCEPTION
              WHEN No_Data_Found THEN
                v_Conn_Arc_No := 0;
                v_Arc_No      := '';
            END;

            IF Cur_Invoice_Record.Inv_Due_Date(i) <> 99999999 THEN
              -- Ageing Cursor
              OPEN Cur_Ageing;
              LOOP
                FETCH Cur_Ageing
                  INTO Cur_Ageing_Record;
                EXIT WHEN Cur_Ageing%NOTFOUND;
                Midaysoverdue := To_Date(Miageingdate, 'YYYYMMDD') -
                                 To_Date(Cur_Ageing_Record.Inv_Due_Date, 'YYYYMMDD');
              END LOOP;
              CLOSE Cur_Ageing;
            END IF;
            
			IF Midaysoverdue <= -31 THEN
              Mdblnotdueamt         := Mdblnotdueamt + Mdbloutstandamt;
              Connection_Ageing_Cat := '1';
            ELSIF Midaysoverdue > 0 THEN
              Mdblpastdue           := Mdblpastdue + Mdbloutstandamt;
              Connection_Ageing_Cat := '3';
            ELSIF (Midaysoverdue > -31 AND Midaysoverdue <= 0) THEN
              Mdblcurrentamt        := Mdblcurrentamt + Mdbloutstandamt;
              Connection_Ageing_Cat := '2';
            END IF;

     
            v_Cust_Tot_Inv := v_Cust_Tot_Inv + 1; -- updating invoice count
            IF v_Temp_Mod_Cust_No = ' ' THEN
              v_Temp_Mod_Cust_No   := v_Inv_Cust_No;
              v_Temp_Inv_Cnt       := v_Cust_No_Detail;
            
            ELSIF v_Temp_Mod_Cust_No = (v_Inv_Cust_No) THEN
              v_Temp_Mod_Cust_No   := v_Inv_Cust_No;
         
            ELSIF v_Temp_Mod_Cust_No <> (v_Inv_Cust_No) THEN
        
              IF v_Temp_Inv_Cnt <> (v_Cust_Tot_Inv - 1) THEN
                IF v_Tmp_Sb = ' ' THEN
                  v_Tmp_Sb := v_Set_Of_Book;

                  UPDATE Temp_Customer_Statement
                  SET    Custtotinv = v_Cust_Tot_Inv - 1
                  WHERE  Cust_No = v_Temp_Mod_Cust_No
                         AND Setofbook = v_Tmp_Sb;
                ELSIF v_Tmp_Sb = v_Set_Of_Book THEN
                  v_Tmp_Sb := v_Set_Of_Book;
                  UPDATE Temp_Customer_Statement t
                  SET    t.Custtotinv = v_Cust_Tot_Inv - 1
                  WHERE  Cust_No = v_Temp_Mod_Cust_No
                         AND Setofbook = v_Tmp_Sb;
                ELSIF v_Tmp_Sb <> v_Set_Of_Book THEN
                  UPDATE Temp_Customer_Statement t
                  SET    t.Custtotinv = v_Cust_Tot_Inv - 1
                  WHERE  Cust_No = v_Temp_Mod_Cust_No
                         AND Setofbook = v_Tmp_Sb;
                END IF;
              END IF;
              
              v_Cust_Tot_Inv       := 1;
              Inv_Stat_Notdue_Amt  := 0;
            
              Mdblbalance          := Mdblbalance + Mdblinvoicework;
              Mdblinvoiceworktrans := Cur_Invoice_Record.Inv_Trans_Amt(i) -
                                      v_Dbltransamtpaid;
                 END IF;
            --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
          
            v_Cust_Addr_Line6 := Cur_Invoice_Record.Cust_Addr_Line6(i);

            IF v_Cc_Stmt_Payor_Level_705 = 'Y' THEN
              IF v_Inv_Cust_No <> Cur_Invoice_Record.Cust_High_Payor_No(i) THEN
                v_Inv_Cust_No := Cur_Invoice_Record.Cust_High_Payor_No(i);
                SELECT Nvl(Cust_Long_Name_717, ' ') Cust_Long_Name
                      
                INTO   v_Cust_Long_Name
                  
                FROM   Customer         c
                      ,Customer_Address Ca
                WHERE  Ca.Cust_No_682 = c.Cust_No_717;
                    
              END IF;
            END IF;
            --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
            IF Cur_Invoice_Record.Inv_Set_Book(i) = 'Q' AND
               Cur_Invoice_Record.Cust_Cc_No(i) IN
               ('SB75', 'SB76', 'CO51', 'CO52', 'CO53', 'CO54') THEN
              IF v_Cust_Addr_Line3 = ' ' THEN
                v_Cust_Addr_Line2 := ' '; -- it will contain country and postal code, so clear it.
              ELSE
                IF Length(TRIM(REPLACE(v_Cust_Addr_Line2
                                      ,Cur_Invoice_Record.Cust_City(i)))) > 0 THEN
                  v_Cust_Addr_Line2 := REPLACE(v_Cust_Addr_Line2
                                              ,Cur_Invoice_Record.Cust_City(i)
                                              ,' ');
                END IF;
                v_Cust_Addr_Line3 := REPLACE(v_Cust_Addr_Line3
                                            ,Cur_Invoice_Record.Cust_City(i)
                                            ,' ');
                IF v_Cust_Addr_Line2 = ' ' AND v_Cust_Addr_Line3 <> ' ' THEN
                  v_Cust_Addr_Line2 := v_Cust_Addr_Line3;
                  v_Cust_Addr_Line3 := ' ';
                END IF;
              END IF;

              v_Cust_Addr_Line3 := Cur_Invoice_Record.Cust_Postal_Code(i) || ' ' ||
                                   Cur_Invoice_Record.Cust_City(i);
              v_Cust_Addr_Line4 := Cur_Invoice_Record.Cust_Country_Name(i);
              --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
              IF v_Cust_Addr_Line2 = ' ' OR v_Cust_Addr_Line3 = ' ' THEN
                IF v_Cust_Addr_Line2 = ' ' AND v_Cust_Addr_Line3 <> ' ' THEN
              
                  v_Cust_Addr_Line4 := ' ';
                ELSIF v_Cust_Addr_Line2 <> ' ' AND v_Cust_Addr_Line3 = ' ' AND
                      v_Cust_Addr_Line4 <> ' ' THEN
          
                  v_Cust_Addr_Line4 := ' ';
                ELSIF v_Cust_Addr_Line3 = ' ' AND v_Cust_Addr_Line4 <> ' ' THEN
             
                  v_Cust_Addr_Line4 := ' ';
                END IF;
              END IF;
            ELSE
              v_Cust_Addr_Line4 := ' ';
            END IF;
            --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
            IF v_Cc_Stmt_Multi_Lang_705 = 'N' THEN
              v_Cust_Lang := 'E';
            END IF;
            --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~ -- ~~  --  ~~  --  ~~
            v_Cust_Addr_Line     := '';
            
            IF v_Cust_Addr_Line5 IS NOT NULL AND v_Cust_Addr_Line5 <> ' ' THEN
              v_Cust_Addr_Line     := v_Cust_Addr_Line5;
       
            END IF;

            IF v_Cust_Addr_Line6 IS NOT NULL AND v_Cust_Addr_Line6 <> ' ' THEN
              IF v_Cust_Addr_Line IS NULL OR v_Cust_Addr_Line = ' ' THEN
           
              ELSE
                IF v_Cust_Addr_Line3 IS NULL OR v_Cust_Addr_Line3 = ' ' THEN
                  v_Cust_Addr_Line3 := v_Cust_Addr_Line2;
               
                ELSE
                  v_Cust_Addr_Line := v_Cust_Addr_Line || Chr(10) || Chr(13) ||
                                      v_Cust_Addr_Line6;
                END IF;
              END IF;
            END IF;

            IF v_Cust_Addr_Line IS NOT NULL AND v_Cust_Addr_Line <> ' ' THEN
              IF v_Cust_Addr_Line3 IS NULL OR v_Cust_Addr_Line3 = ' ' THEN
  
              
              ELSE
                v_Cust_Addr_Line3 := v_Cust_Addr_Line || Chr(10) || Chr(13) ||
                                     v_Cust_Addr_Line3;
              END IF;
            END IF;

            v_Cust_Parent_Level_Flag := Cur_Invoice_Record.Cust_Parent_Level_Flag(i);
       
            INSERT INTO Temp_Customer_Statement
              (Cust_Contact
             
              ,Ardw_Remit_To_Addr5
             )
            VALUES
              (Cur_Invoice_Record.Cust_Contact(i)
              ,Cur_Invoice_Record.Ardw_Remit_To_Addr5(i)
              );
          END IF;
        END LOOP;
        COMMIT;
      END LOOP;

      IF Cur_Invoice%ISOPEN THEN
        CLOSE Cur_Invoice;
      END IF;

      COMMIT;

    EXCEPTION
      WHEN OTHERS THEN
        v_Err_Num := SQLCODE;
        v_Err_Msg := Substr(SQLERRM, 1, 100);

        IF Cur_Invoice%ISOPEN THEN
          CLOSE Cur_Invoice;
        END IF;

        INSERT INTO Load_Err
        VALUES
          (v_Err_Num
          ,v_Err_Msg
          ,To_Number(To_Char(SYSDATE, 'YYYYMMDD'))
          ,'ELECT_STMT'
          ,'Cust No -' || v_Inv_Cust_No || '-Finder_No-' || v_Inv_Finder_No ||
           '-Spr_Gcars_Customer_Statement-Insert-Temp_Customer_Statement');
        COMMIT;
    END Loop_Cur_Invoice;
  END LOOP;


  <<upd_Temp_Invduedate>>
  DECLARE
    CURSOR Cur_Temp_Invduedate IS
      SELECT Cust_No
            ,Invfinderno
      FROM   Temp_Customer_Statement
            ,Invoice
      WHERE  Inv_Cust_No_720 = Cust_No
             AND Inv_Disc_Rec_720 IN ('DD', 'DR');

    TYPE t_Rec_Upd_Temp IS RECORD(
       Cust_No     Dbms_Sql.Varchar2_Table
      ,Invfinderno Dbms_Sql.Number_Table);

    l_Rec_Temp   t_Rec_Upd_Temp;
    Err_Num      NUMBER;
    Err_Msg      VARCHAR2(100);
    v_Num_Errors BINARY_INTEGER := 0;
    p_Cust_No    VARCHAR2(200);
    p_Finderno   BINARY_INTEGER;
  BEGIN
    IF Cur_Temp_Invduedate%ISOPEN THEN
      CLOSE Cur_Temp_Invduedate;
    END IF;
    OPEN Cur_Temp_Invduedate;
    LOOP
      EXIT WHEN Cur_Temp_Invduedate%NOTFOUND;
      FETCH Cur_Temp_Invduedate BULK COLLECT
        INTO l_Rec_Temp LIMIT v_Limit;

--#7     
	 FORALL i IN 1 .. l_Rec_Temp.Cust_No.Count --SAVE EXCEPTIONS
        UPDATE Temp_Customer_Statement
        SET    Invduedate = Invduedate || '*'
        WHERE  Cust_No = l_Rec_Temp.Cust_No(i)
               AND Invfinderno = l_Rec_Temp.Invfinderno(i);
      COMMIT;
    END LOOP;
    COMMIT;

    IF Cur_Temp_Invduedate%ISOPEN THEN
      CLOSE Cur_Temp_Invduedate;
    END IF;

  
  --#8
  EXCEPTION
    WHEN OTHERS THEN
      v_Num_Errors := SQL%Bulk_Exceptions.Count;
      IF Cur_Temp_Invduedate%ISOPEN THEN
        CLOSE Cur_Temp_Invduedate;
      END IF;
      FOR Err IN 1 .. v_Num_Errors
      LOOP
        p_Cust_No  := l_Rec_Temp.Cust_No(SQL%BULK_EXCEPTIONS(Err).Error_Index);
        p_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 Load_Err
        VALUES
          (Err_Num
          ,Err_Msg
          ,To_Number(To_Char(SYSDATE, 'YYYYMMDD'))
          ,'ELECT_STMT'
          ,'Cust No -' || p_Cust_No || '-Finder no-' || p_Finderno ||
           '- Spr_abc_Customer_Statement - Update Temp_Customer_Statement for INVDUEDATE');
      END LOOP;
      COMMIT;
  END Upd_Temp_Invduedate;

 
EXCEPTION
  WHEN OTHERS THEN
    Dbms_Output.Put_Line(v_Inv_Cust_No);
    Dbms_Output.Put_Line(v_Inv_Finder_No);
    Dbms_Output.Put_Line(SQLCODE);
    Dbms_Output.Put_Line(SQLERRM);

    IF Cur_Invoice%ISOPEN THEN
      CLOSE Cur_Invoice;
    END IF;
    v_Err_Num := SQLCODE;
    v_Err_Msg := Substr(SQLERRM, 1, 100);

    ROLLBACK;
     RAISE;
END;

Das sind alles spezifische Cursor Deklarationen, die je nach Umständen (und Mengengerüst) Performance Vorteile bieten.
BULK EXCEPTION zeigt auf die Nummer des fehlerhaften Statements. Das kenne ich nicht in PG.

GOTO gibt es nicht, es muss ersetzt werden durch LABEL/EXIT, FUNCTIONS oder Nesting.

Du solltest versuchen, alles in postgres explizite Cursordeklarationen zu verwandeln und für den Anfang diverse Sonderfälle einfach weglassen, bis das Gerüst läuft.
Noch wichtiger scheint mir, zu verstehen, was hier fachlich gemacht wird. Und wie es technisch umgesetzt ist. Die Umsetzung scheint mir an einigen Stellen fragwürdig. Eine Schleife in einer Schleife in einer Schleife …
Das wäre in meiner Welt ein Join.

Hier ist ein kleiner Artikel zu Cursor bulk collect mit Verweisen auf weitere des Autors.

Du musst natürlich verstehen, was auf der Oracle Seite passiert.

1 „Gefällt mir“

Sure,thanks. @castorp @pogomips

Hallo,

Ich habe versucht, es zu migrieren, aber es schlägt in der Mitte fehl. Wenn es Ihnen nichts ausmacht, kann ich den Code per E-Mail weitergeben. Zum Prüfen & Beheben. Dies ist dringend und ich habe kein Bewusstsein für diese komplexen Konzepte.

In dem Fall musst Du wohl jemand finden, der einen Auftrag annimmt und Geld dafür bekommt.

Das ist gut, aber

Es ist üblich, in solchen Fällen in einem Forum eine Fehlermeldung und eine Codestelle zu zeigen, die das Problem beschreibt.

Hast Du schonmal ein Forum benutzt oder ist es das erste Mal? Hier trifft man sich, um Rat einzuholen und Tipps mit allen zu teilen, so dass alle etwas davon haben.

1 „Gefällt mir“

Du könntest es ja mal versuchen via dem EDB migration portal zu migrieren, allerdings wäre Zielsystem dann EPAS (die kommerzielle PG-Version von EDB mit Ora-Compatibility-Layer)

1 „Gefällt mir“

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