Select from PL/SQL table

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:

  g_issues  dq_issue_tab := dq_issue_tab();
  g_issues(g_issues.count) := dq_issue('10', 3);
  g_issues(g_issues.count) := dq_issue('20', 1);
  ... create other entries here

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 comments to Select from PL/SQL table

  • bfayle

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

    • Jacek Dobosz

      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 );

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

    • Jacek Dobosz

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

  • 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?