This discussion is archived
12 Replies Latest reply: Oct 12, 2012 1:58 PM by mlov83 RSS

retrieving row id with  bulk collect.

mlov83 Newbie
Currently Being Moderated
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. Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    >
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
     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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points