how to insert into nested table collection using cursor plsql

Question

I'm writing a function that needs to manipulate multiple rows at the same time and they need to be indexed. After several hours of reading about Oracle pl/sql I figured I could create a nested table kind of collection. Since I couldn't find a definitive answer and trial/error method takes way to long. Here is question part: QUESTION: What is the best practice to populate a nested table collection? Oracle PL/SQL

       type partsTable is table of Parts_north_wing%rowtype;
       pt PartsTable;    
       index number;         
       cursor pCursor is select * from Parts_north_wing;
begin
        index := 1;
        open pCursor;
        loop
                fetch pCursor into tempRow;
                pt(index) := tempRow;
                index := index + 1;
                exit when pCursor%notfound;
        end loop;
        close pCursor;

I believe, the most efficient way is to use `BULK COLLECT INTO` clause on your cursor or select, e.g. `OPEN pCursor; FETCH pCursor BULK COLLECT INTO pt; CLOSE pCursor;`. — AndrewMcCoist, Apr 25 '16 at 19:54

score 2 · Accepted Answer · answered Apr 25 '16 at 22:09

2

A cursor FOR LOOP is almost always the best way to process rows in PL/SQL. It's simpler than the OPEN/FETCH/CLOSE method - no need to declare variables and manipulate cursors. It's also faster since it automatically bulk collects the results.

begin
    for pt in
    (
        select parts_north_wing.*, rownum row_index
        from parts_north_wing
    ) loop
        --Do something here
        null;
    end loop;
end;
/

answered Apr 25 '16 at 22:09

Jon Heller

34,999
6
74
132

got it. So this is implicit cursor for loop statement. very convenient. http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/cursor_for_loop_statement.htm#LNPLS1155 – A_P Apr 26 '16 at 13:54

score 0 · Answer 2 · answered Apr 26 '16 at 06:27

Try this. Hope this helps you to clear some of your concepts.

--Create a dummy object tyep
CREATE OR REPLACE TYPE av_obj
IS
  OBJECT
  (
    ADD1 VARCHAR2(100),
    ADD2 VARCHAR2(100) );
  --Create a nested tale type

CREATE OR REPLACE TYPE AV_TT
IS
  TABLE OF AV_OBJ;

  --Bulk collect into nested table type
  DECLARE
    av_nested_tab AVROY.AV_TT;
  BEGIN
    SELECT avroy.av_obj(LEVEL
      ||'add1',LEVEL
      ||'add2') BULK COLLECT
    INTO av_nested_tab
    FROM DUAL
      CONNECT BY LEVEL < 10;
  END;

how to insert into nested table collection using cursor plsql

2 Answers2