SELECT from a bulk collection

Question

Is it possible to select from a bulk collection?

Something along these lines:

DECLARE
  CURSOR customer_cur IS
    SELECT CustomerId,
          CustomerName
    FROM   Customers
    WHERE  CustomerAreaCode = '576';

  TYPE customer_table IS TABLE OF customer_cur%ROWTYPE;
  my_customers  customer_table; 
BEGIN

  OPEN customer_cur;

  FETCH customer_cur
  BULK COLLECT INTO my_customers;

  -- This is what I would like to do
  SELECT CustomerName
    FROM my_customers
   WHERE CustomerId IN (1, 2, 3); 

END;

I don't seem to be able to select from the my_customers table.

You can't select from a PL/SQL collection; can you declare your types outside the procedure? You said you want to use the results, but how - can you just iterate over the collection, rather than try to select directly from it? — Alex Poole, Oct 31 '14 at 17:00
The actual query for the cursor includes a calculated column. Based on the value of this calculation, I want to use some data from that collection and union with other tables (as a `SYS_REFCURSOR`). If there's a way to "build" the cursor via iteration, that's a suitable alternative. — Wonko the Sane, Oct 31 '14 at 17:14

peter.hrasko.sk · Accepted Answer · 2014-10-31T21:46:57.827

3

Yes, you can. Declare yourself schema-level types as follows:

create or replace rec_customer_cur
as
object (
    customerid       integer, -- change to the actual type of customers.customerid
    customername     varchar2(100) -- change to the actual type of customers.customername
);
/

create or replace type customer_table
as
table of rec_customer_cur;
/

Then, in your PLSQL code, you can declare

CURSOR customer_cur IS
SELECT new rec_customer_cur(CustomerId, CustomerName)
FROM   Customers
WHERE  CustomerAreaCode = '576';

... and then use ...

SELECT CustomerName
INTO whatever
FROM table(my_customers)
WHERE CustomerId IN (1, 2, 3);

This is because schema-level types can be used in SQL context.

edited Oct 31 '14 at 21:46

answered Oct 31 '14 at 17:15

peter.hrasko.sk

4,043
2
19
34

2

You'd need to change the cursor to return the object type into the collection as well though; `SELECT rec_customer_cur(CustomerId, CustomerName) FROM ...`. Otherwise you'd hit PLS-00386. – Alex Poole Oct 31 '14 at 17:48
@AlexPoole - you Yada Yada'ed over the best part. :) Is this in a cursor declaration (later using `FETCH mycursor BULK COLLECT INTO my_customer_table`), or do you need to do a `SELECT rec_customer_cur(..) BULK COLLECT....` statement? – Wonko the Sane Oct 31 '14 at 19:32
1

I modified my answer according to @AlexPoole's comment. – peter.hrasko.sk Oct 31 '14 at 21:43

Lalit Kumar B · Answer 2 · 2014-10-31T16:26:39.950

0

If you want to also display the dataset returned by the select, then just use a REF CURSOR as an OUT parameter.

The SELECT ...FROM TABLE is a SQL statement, which needs a STATIC TABLE NAME, as a database object. It throws an error since the collection name is not actually a database table as an object.

To return the dataset, use SYS_REFCURSOR as OUT parameter.

open cur as select....

edited Oct 31 '14 at 16:26

answered Oct 31 '14 at 16:21

Lalit Kumar B

47,486
13
97
124

The above is actually a simplified example. I want to use the results of the bulk collection within the procedure to do some further processing. Later, I will return a SYS_REFCURSOR based on that processing. – Wonko the Sane Oct 31 '14 at 16:46

SELECT from a bulk collection

2 Answers2