You Asked

Can I bulk collect into an existing collection that already has data and preserve the original data? In other words append the new data to the existing data?

The output I'm looking for would be (order not important):

QWERTY
ASDF
Haikus are easy
But sometimes they don`t make sense
Refrigerator

The LiveSQL script is throwing an error: ORA-04088: error during execution of trigger 'SYS.DBCLOUD_BEFORE_DDL_DB_TRG' ORA-06512: at "SYS.DBCLOUD_SYS_SEC", line 1295

I don't get the error when I run it at my desk.

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/file/content_FGYNY47H8G9CC0RWU7A1XPTI9.html

and Connor said...

Probably the closest you can do is add a query to the nested table as part of your second query,

SQL> create or replace type String255List as table of varchar2(255);
  2  /

Type created.

SQL> create table U_TEST1 (COLUMN_1 varchar(255))
  2  /

Table created.

SQL> create table U_TEST2 (COLUMN_2 varchar(255))
  2  /

Table created.

SQL>
SQL> insert all
  2    into U_TEST1 (COLUMN_1) values ('QWERTY')
  3    into U_TEST1 (COLUMN_1) values ('ASDF')
  4  select * from dual
  5  /

2 rows created.

SQL> insert all
  2    into U_TEST2 (COLUMN_2) values ('Haikus are easy')
  3    into U_TEST2 (COLUMN_2) values ('But sometimes they don`t make sense')
  4    into U_TEST2 (COLUMN_2) values ('Refrigerator')
  5  select * from dual
  6  /

3 rows created.

SQL>
SQL> set serverout on
SQL> declare
  2      vTest String255List;
  3
  4  begin
  5      vTest := String255List();
  6
  7      select ut.COLUMN_1
  8          bulk collect into vTest
  9      from U_TEST1 ut;
 10
 11      -- a whole bunch of stuff goes on here such that simply unioning the two selects is not an option
 12
 13      select ut.COLUMN_2
 14          bulk collect into vTest
 15      from U_TEST2 ut;
 16
 17      for i in 1 .. vTest.count
 18      loop
 19          DBMS_OUTPUT.put_line (vTest(i));
 20      end loop;
 21  end;
 22  /
Haikus are easy
But sometimes they don`t make sense
Refrigerator

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> set serverout on
SQL> declare
  2      vTest String255List;
  3      vTest1 String255List;
  4
  5  begin
  6      vTest := String255List();
  7
  8      select ut.COLUMN_1
  9          bulk collect into vTest1
 10      from U_TEST1 ut;
 11
 12      -- a whole bunch of stuff goes on here such that simply unioning the two selects is not an option
 13
 14      select ut.COLUMN_2
 15          bulk collect into vTest
 16      from U_TEST2 ut
 17      union all
 18      select * from table(vTest1);
 19
 20      for i in 1 .. vTest.count
 21      loop
 22          DBMS_OUTPUT.put_line (vTest(i));
 23      end loop;
 24  end;
 25  /
Haikus are easy
But sometimes they don`t make sense
Refrigerator
QWERTY
ASDF

PL/SQL procedure successfully completed.

SQL>
SQL>

I've passed on the LiveSQL to the relevant people.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, August 28, 2017 - 4:08 pm UTC

Doh! Staring me right in the face. Thanx!

Follow up question, is there any particular reason to use one or the other of:

select cast(collect(value) as type)
into collection

or

select value
bulk collect into collection

August 29, 2017 - 1:46 am UTC

I generally prefer the latter, but simply for aesthetic reasons - just seems to read more like the function being performed.

Don Simpson, August 29, 2017 - 3:00 pm UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Bulk collect into an existing collection that already has data

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs