CODEWITHSUNDEEP
oracle-databaseplsqlcursorbulk-collect
Wonko the Sane
Wonko the Sane

Reputation: 10813

SELECT from a bulk collection

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.

Upvotes: 1

Views: 3532

Answers (2)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

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.

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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....

Upvotes: 0

Related Questions