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;
It looks like the above example is missing the type create for dq_issue_tab
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 );
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.
Thanks for pointing this. I’ve overseen of course the collection type definition.
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.