Select from PL/SQL table

By | January 28, 2009

This example shows how to make a select from the pl/sql collection (table).

First we need the ORACLE type definition with lets say two fields:

SQL> create or replace type dq_issue is object ( issue_code varchar2(2), importance number );

and the pl/sql collection defintion:

SQL> CREATE OR REPLACE TYPE dq_issue_tab IS TABLE OF dq_issue;

Somewhere in your code you would need to declare your variables and initialize the collection:

declare
  g_issues  dq_issue_tab := dq_issue_tab();
begin
  g_issues.extend;
  g_issues(g_issues.count) := dq_issue('10', 3);
  g_issues.extend;
  g_issues(g_issues.count) := dq_issue('20', 1);
/*
  ...
  ... create other entries here
  ...
*/
end;

finally we want to select our issues and sort it

for r_x in (
             select * from table(cast (g_issues as dq_issue_tab))
             order by importance
           ) loop
  /* ... do something useful with it here */
  dbms_output.put_line('issue_code=' || r_x.issue_code);
end loop;

5 thoughts on “Select from PL/SQL table

  1. bfayle

    It looks like the above example is missing the type create for dq_issue_tab

    1. Jacek Dobosz Post author

      The type dq_issue is created as object. See the first code box:
      create or replace type dq_issue is object ( issue_code varchar2(2), importance number );

  2. seaner

    Nice example, Jacek, just stumbled across this.

    Bump on bfayle’s post though – you have indeed created the object type dq_issue, but not the dependent type dq_issue_tab that you’re doing the actual CAST() on.

    Thanks again.

    1. Jacek Dobosz Post author

      Thanks for pointing this. I’ve overseen of course the collection type definition.

  3. seaner

    No problem!
    Another question – I recently needed to tune a looped query in one of my packages and ended up replacing it with a BULK COLLECT from a join with a table(cast(…)) of a collection type. I was impressed with the performance increase (~200x faster execution time for a small number of records – only about 200) but I’ve read that performance degrades with larger collections.

    Have you experienced any problems when doing this with thousands or tens of thousands of records?

    Thanks,

    .s.

Comments are closed.