Insert data from pl/sql table type to another pl/sql table type

Question

I am just wondering whether it is possible to insert data from one pl/sql table to another using bulkcollect?

I am trying it out but looks like it is not possible conceptually and the only way is to loop through the Pl/sql table .

Any insights would be really helpful. Thanks

below is the simplified version of what i am trying. i think i am making some conceptual mistake here . hence it is not working:

DECLARE 
TYPE ROWTBL IS TABLE OF BW_COLUMN.NAME%TYPE ;
PL_TBL_ROW ROWTBL;

TYPE COLNAME_TBL IS TABLE OF BW_COLUMN.NAME%TYPE ;
PL_TBL_COLNAME COLNAME_TBL;

BEGIN

   SELECT NAME 
   BULK COLLECT INTO PL_TBL_ROW 
   FROM TBL_COL WHERE TBL_ID = 2000081;


   SELECT NAME 
   BULK COLLECT INTO PL_TBL_COLNAME 
   FROM PL_TBL_ROW;

END;

BULK COLLECT is used to reduce the context switching between PL/SQL and SQL. If I understand you correctly your desired action is in PL/SQL only and you just need to assign one variable to the other: table_var2 := table_var1; — Jon Tofte-Hansen, Jun 12 '15 at 09:50
yeah that is one option. i have to write a for loop for that. in that case , would global temp table be a better option compared to looping a pl/sql table — shirjai, Jun 12 '15 at 10:51
I don't know your code, but I don't understand why you need a loop, the := fills all the "rows" in one command. Do you mean that you have to process severeal table variables? If you have memory limitations you can use a temp table paging the result for further processing.. — Jon Tofte-Hansen, Jun 12 '15 at 10:57
Please give an example of what you are trying to achieve (with the desired outputs) and the code you have tried. — MT0, Jun 12 '15 at 14:26
below is the simplified version of what i am trying. i think i am making some conceptual mistake here . hence it is not working: — shirjai, Jun 15 '15 at 04:38

score 1 · Answer 1 · answered Jun 12 '15 at 10:59

BULK COLLECT is a mechanism for efficiently reading data into PL/SQL data structures so they can be processed by PL/SQL code. You can certainly use this approach for copying data from one table to another but I suspect it will probably take more time than the simpler approach of using an INSERT statement such as

BEGIN
  INSERT INTO SOME_TABLE (COL_1, COL_2, COL_N)
    SELECT COL_1, COL_2, COL_N
      FROM SOME_OTHER_TABLE
      WHERE SOME_FIELD = SOME_OTHER_VALUE;
END;

Best of luck.

Insert data from pl/sql table type to another pl/sql table type

1 Answers1