Home » SQL & PL/SQL » SQL & PL/SQL » complex processing with collection on each row?
complex processing with collection on each row? [message #185011] Sun, 30 July 2006 00:40 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi there!

I have a question is it possible to do complex processing on each row as it is queried with BULK COLLECT ?
like we do in CURSOR FOR LOOP.

IF IT IS POSSIBLE , COULD YOU HINT ME or SHOW ANY EXAMPLE?

HERE IS MY CODE:

     DECLARE
          CURSOR c1 IS SELECT * FROM EMPLOYEES;
          
          TYPE empc1 IS TABLE OF c1%ROWTYPE INDEX BY BINARY_INTEGER;
          
          emprec empc1;
          
          TYPE emp2 IS TABLE OF employees2%ROWTYPE;
          
          emp2tab emp2 := emp2();
          
          bulk_errors EXCEPTION;
          
          PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
          
          
     BEGIN

      OPEN c1;
      LOOP
        
        FETCH c1 BULK COLLECT INTO emprec LIMIT 5;
        EXIT WHEN emprec.COUNT = 0;
          
        FOR i IN 1..emprec.COUNT
        LOOP
     emp2tab.EXTEND;
     emp2tab(emp2tab.LAST) := emprec(i); /* NOW BEFORE I ASSIGNED RECORDS TO COLLECTIONS I WANT TO DO SOME DATA PROCESS
                                           HOW and where DO I DO DATA PROCESS LIKE 
                                                          IF v_department = 10 then
                                                             emp2tab(emp2tab.LAST) := emprec(i)
                                                                     end if;
                                          like this there are so many conditions & complex processing on each row 
                                          before it assigned to emp2tab(emp2tab.last).
                                          Is it possible to do various conditions & complex processing with collection
                                            on each row as it is queried?        
                                         */
            END LOOP;
         END LOOP;
          BEGIN
               FORALL i IN 1..emp2tab.COUNT SAVE EXCEPTIONS
               INSERT INTO employees2 values emp2tab(i);
          EXCEPTION
            WHEN bulk_errors THEN
            FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT
            LOOP
            DBMS_OUTPUT.PUT_LINE('ERROR FROM ELEMENT #' ||
            TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
                 SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
            END LOOP;
           END;
      COMMIT;
   CLOSE c1;
end;
/


Re: complex processing with collection on each row? [message #185012 is a reply to message #185011] Sun, 30 July 2006 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:


IF v_department = 10 then
emp2tab(emp2tab.LAST) := emprec(i)


this doesn't look like complex processing to me.
You could do this by adding a where-clause in your original query:
where department = 10

to address columns in your collection use
emprec(i).department
Re: complex processing with collection on each row? [message #190697 is a reply to message #185012] Thu, 31 August 2006 18:16 Go to previous message
adminme
Messages: 31
Registered: May 2006
Member
I am somewhat new here but understand that you can not manipulate rows/row columns in a bulk movement process.

I believe you should put them in an array or something else to massage the data.

If there is a way I'd be interested to know.

-adminme
Previous Topic: Bulk Collect & Pipelined Table Function - Confusion Building
Next Topic: how to load csv file that is in a CLOB column into a table ?
Goto Forum:
  


Current Time: Tue Dec 06 10:46:55 CST 2016

Total time taken to generate the page: 0.12647 seconds