This forum is currently locked. You can't register or post questions at this time. (read more)

Selecting from PL/SQL collection

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

HenrikTuxen
Member
Posts: 2
Joined: Wed May 02, 2012 11:55 am

Selecting from PL/SQL collection

Postby HenrikTuxen » Wed May 02, 2012 11:58 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Selecting from PL/SQL collection

Postby Tim... » Wed May 02, 2012 12:17 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

HenrikTuxen
Member
Posts: 2
Joined: Wed May 02, 2012 11:55 am

Re: Selecting from PL/SQL collection

Postby HenrikTuxen » Thu May 03, 2012 10:53 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Selecting from PL/SQL collection

Postby Tim... » Thu May 03, 2012 11:08 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests