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
– AndrewMcCoist, Commented Apr 25, 2016 at 19:54

Jon Heller · Accepted Answer · 2016-04-25 22:09:37Z

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, 2016 at 22:09

Jon Heller

36.9k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A_P Over a year ago

got it. So this is implicit cursor for loop statement. very convenient. docs.oracle.com/cd/E11882_01/appdev.112/e25519/…

Avrajit Roy · Accepted Answer · 2016-04-26 06:27:25Z

0

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;

answered Apr 26, 2016 at 6:27

Avrajit Roy

3,3131 gold badge16 silver badges25 bronze badges

Collectives™ on Stack Overflow

how to insert into nested table collection using cursor plsql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related