12 Replies Latest reply: Oct 12, 2012 3:58 PM by mlov83 RSS

    retrieving row id with  bulk collect.

    mlov83
      I'm trying to bulk collect the records in a table including row id but i cant seem to get it right. I cant seem to find any syntax on how to do this. Here is my code the fails
      SELECT rowid, f.* BULK COLLECT INTO invoices
        FROM FWTMP_REGPO_INTRFACE f;
        
        FOR indx in 1..invoices.Count
          LOOP
            
            L_previousPaidAmount  :=  invoice_function.f_get_prev_paid_amt_inva( invoices(indx).FWTMP_REGPO_PO,NVL(invoices(indx).FWTMP_REGPO_LINE_NBR,'0'),invoices(indx).FWTMP_REGPO_SEQ_NBR);
           dbms_output.put_line(indx || invoices(indx).FWTMP_REGPO_INV_NUM ||' '|| L_previousPaidAmount );
         
          END LOOP;
      this code works but its not include the row id.
      SELECT f.* BULK COLLECT INTO invoices
        FROM FWTMP_REGPO_INTRFACE f;
        
        FOR indx in 1..invoices.Count
          LOOP
            
            L_previousPaidAmount  :=  invoice_function.f_get_prev_paid_amt_inva( invoices(indx).FWTMP_REGPO_PO,NVL(invoices(indx).FWTMP_REGPO_LINE_NBR,'0'),invoices(indx).FWTMP_REGPO_SEQ_NBR);
           dbms_output.put_line(indx || invoices(indx).FWTMP_REGPO_INV_NUM ||' '|| L_previousPaidAmount );
         
          END LOOP;
      what is the right syntax
        • 1. Re: retrieving row id with  bulk collect.
          Sven W.
          You didn't show how you declared your invoices record/table.

          Maybe try it like this:
          untested
          declare
            /* dummy cursor declaration */
            cursor c is (SELECT rowid, f.* FROM FWTMP_REGPO_INTRFACE f where 1=2);
            /* use a rowtype of the cursor */
            r_invoice c%rowtype; 
            /* build a table type from this rowtype */
            type t_invoice table of r_invoice index by binary_integer;  
            invoice t_invoice;
          begin
            SELECT rowid, f.* BULK COLLECT INTO invoice
            FROM FWTMP_REGPO_INTRFACE f;
            
            FOR indx in 1..invoice.Count
              LOOP
          
          ...
          I'm not sure if I didn't make any syntax errors. But i remember having used such a construct in the past.

          Edited by: Sven W. on Oct 12, 2012 6:57 PM

          Edited by: Sven W. on Oct 12, 2012 7:05 PM -- reedited. I forgot to declare the type correctly and use a variable on that type.
          • 2. Re: retrieving row id with  bulk collect.
            rp0428
            >
            I'm trying to bulk collect the records in a table including row id

            SELECT rowid, f.* BULK COLLECT INTO invoices
            FROM FWTMP_REGPO_INTRFACE f;
            >
            That is the correct syntax. Your problem is likely because you did not define 'invoices' to include ROWID.

            Post the DDL for the object you are collecting into.
            • 3. Re: retrieving row id with  bulk collect.
              rp0428
              >
              SELECT rowid, f.* BULK COLLECT INTO invoices
              >
              In your haste to 'beat me to the post' I think you forgot to change 'invoices' to your type. ;)
              • 4. Re: retrieving row id with  bulk collect.
                Sven W.
                rp0428 wrote:
                >
                SELECT rowid, f.* BULK COLLECT INTO invoices
                >
                In your haste to 'beat me to the post' I think you forgot to change 'invoices' to your type. ;)
                I did it already while you were writing this post. ;)

                Btw: I wondered why the forum suddenly was so slow. It might be because we both added something at almost the same time. but I won :P !

                Edited by: Sven W. on Oct 12, 2012 7:02 PM
                • 5. Re: retrieving row id with  bulk collect.
                  mlov83
                  here is the code sorry i didn't post the declaration first.
                  TYPE INVOICE
                     IS TABLE OF TEMPLE_FINANCE.FWTMP_REGPO_INTRFACE%ROWTYPE INDEX BY PLS_INTEGER;
                     invoices INVOICE;
                     L_previousPaidAmount  NUMBER(17,2);
                  • 6. Re: retrieving row id with  bulk collect.
                    rp0428
                    >
                    TYPE INVOICE
                    IS TABLE OF TEMPLE_FINANCE.FWTMP_REGPO_INTRFACE%ROWTYPE INDEX BY PLS_INTEGER;
                    invoices INVOICE;
                    L_previousPaidAmount NUMBER(17,2);
                    >
                    Well that TYPE definition doesn't have a ROWID column in it does it?
                    Well your query was
                    SELECT rowid, f.* BULK COLLECT INTO invoices
                      FROM FWTMP_REGPO_INTRFACE f;
                    So how can Oracle put ROWID and all of the columns of FWTMP_REGPO_INTRFACE into a TYPE instance that only has enough room for the columns of FWTMP_REGPO_INTRFACE?

                    That's why you need something like what Sven posted. He used a cursor to define a row that includes ROWID even though the cursor isn't used in the code after that.

                    And if you are bulk collecting you should use a nested table and not use an associative array. So get rid of the INDEX BY stuff.
                    • 7. Re: retrieving row id with  bulk collect.
                      mlov83
                      i get an error that R_invoice must be a type? I'm not sure i follow? any ideas
                      • 8. Re: retrieving row id with  bulk collect.
                        mlov83
                        thank you rp this makes more sense now. but i still dont understant the problem with r_invoice?
                        • 9. Re: retrieving row id with  bulk collect.
                          mlov83
                           cursor c is (SELECT rowid, f.* FROM FWTMP_REGPO_INTRFACE f);
                            TYPE r_invoice IS TABLE OF  c%ROWTYPE;
                            invoice r_invoice;
                          ok i figuered out what i was doing wrong.
                          Thanks for putting me on the right track guys.
                          • 10. Re: retrieving row id with  bulk collect.
                            rp0428
                            >
                            ok i figuered out what i was doing wrong.
                            Thanks for putting me on the right track guys.
                            >
                            Glad you found the problem.

                            Post exactly what the solution was so that other people reading the thread that have the same problem will know what to do.

                            And I notice you haven't marked this thread ANSWERED.

                            You seem to have a bad habit of abandoning your threads - 50 (27 unresolved)

                            Please review your unresolved threads and mark them ANSWERED as appropriate.
                            • 11. Re: retrieving row id with  bulk collect.
                              mlov83
                              my bad RP I will do so.
                              Thanks for pointing this out to me. I'll take care of it.
                              • 12. Re: retrieving row id with  bulk collect.
                                mlov83
                                the full solution is here
                                create or replace
                                PROCEDURE F_EINVOICE_IMPRVED AS 
                                
                                
                                  L_previousPaidAmount  NUMBER(17,2);
                                  
                                  cursor c is (SELECT rowid, f.* FROM FWTMP_REGPO_INTRFACE f);
                                  TYPE r_invoice IS TABLE OF  c%ROWTYPE;
                                  
                                  invoice r_invoice;
                                
                                BEGIN
                                
                                  
                                  
                                  
                                  
                                
                                BEGIN
                                /*
                                select  all the invoices into a temporary table in memory and processed it immeditiately.
                                */
                                 SELECT rowid, f.* BULK COLLECT INTO invoice
                                  FROM FWTMP_REGPO_INTRFACE f;
                                  
                                  FOR indx in 1..invoice.Count
                                    LOOP
                                      
                                      L_previousPaidAmount  :=  invoice_function.f_get_prev_paid_amt_inva( invoice(indx).FWTMP_REGPO_PO,NVL(invoice(indx).FWTMP_REGPO_LINE_NBR,'0'),invoice(indx).FWTMP_REGPO_SEQ_NBR);
                                     dbms_output.put_line(indx || invoice(indx).FWTMP_REGPO_INV_NUM ||' '|| L_previousPaidAmount );
                                   
                                    END LOOP;
                                
                                END;
                                
                                
                                
                                
                                
                                  BEGIN
                                
                                    NULL;
                                
                                  END;
                                
                                END F_EINVOICE_IMPRVED;