Selecting from PL/SQL collection

[HenrikTuxen]

Hi all, I have a problem that I have been banging my head against for the last couple of weeks, namely that I have to do a SELECT from a PL/SQL collection.
The initial code is a follows:

Code: Select all

type all_objects_t is record (
   owner         all_objects.owner%type,
   object_name      all_objects.object_name%type,
   object_type      all_objects.object_type%type,
   status         all_objects.stats%type
);

type all_objects_table_t is table of all_objects_t;

all_objects_table   all_objects_table_t;

procedure load_objects
is
begin
   all_objects_table := all_objects_table_t;
   select owner, object_name, object_type, status
   bulk collect into all_objects_table
   from all_objects@dblink;
end;
/


How do I select in the collection, because doing a linear search is not really optimal?

Thanks in advance
//Henrik

[Tim...]

Hi.

You need to check out pipelined table functions.

http://www.oracle-base.com/articles/mis ... ctions.php

Remember, SQL doesn't know anything about PL/SQL objects, so they must be exposed to SQL. As such, type definitions must be public (in the package spec), not private (in the package body).

Cheers

Tim...

[HenrikTuxen]

Thanks Tim, that was the nudge in the right direction.
I'm posting the code results for others to see.

Script for creating the type and package specification

Code: Select all

set echo on
drop type all_objects_table_t;
drop type all_objects_t;
create type all_objects_t is object (
   owner            varchar2(30),
   object_name         varchar2(30),
   object_type         varchar2(19),
   status            varchar2(7)
);
/
show errors

create type all_objects_table_t is table of all_objects_t;
/

create or replace package ao as
   
    all_objects_table   all_objects_table_t;
       
    procedure load;
    procedure show(p_name in varchar2);
end;
/
show errors


Script for creating the package

Code: Select all

set echo on
create or replace package body ao as
    procedure load
    is
    begin
      dbms_output.put_line('LOAD start at ' || systimestamp);
        all_objects_table := all_objects_table_t();
       
        select all_objects_t(owner, object_name, object_type, status)
        bulk collect into all_objects_table
        from all_objects;
       
        dbms_output.put_line(all_objects_table.count || ' rows loaded');
      dbms_output.put_line('LOAD end at ' || systimestamp);
    end;
   
    procedure show(p_name in varchar2)
    is
        l_all_objects       all_objects_t;
        cursor cshow(pname varchar2)
        is
            select all_objects_t(owner, object_name, object_type, status) from table(all_objects_table)
            where object_name = upper(pname);
    begin
      dbms_output.put_line('LOAD start at ' || systimestamp);
        open cshow(p_name);
        loop
            fetch cshow into l_all_objects;
            exit when cshow%notfound;
            dbms_output.put_line(l_all_objects.object_type || ' ' || l_all_objects.object_name || ' has status ' || l_all_objects.status);
        end loop;
        close cshow;
      dbms_output.put_line('LOAD end at ' || systimestamp);
    end;
end;
/
show errors


Test run

Code: Select all

SQL> show errors
No errors.
SQL> begin
  2  ao.load;
  3  ao.show('ao');
  4  end;
  5  /
LOAD start at 03-MAY-12 11.49.47.387000000 AM +02:00
45289 rows loaded
LOAD end at 03-MAY-12 11.49.48.306000000 AM +02:00
LOAD start at 03-MAY-12 11.49.48.306000000 AM +02:00
PACKAGE AO has status VALID
PACKAGE BODY AO has status VALID
LOAD end at 03-MAY-12 11.49.48.401000000 AM +02:00

PL/SQL procedure successfully completed.


//Henrik

[Tim...]