How to select data out of an Oracle collection/array?

Question

Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an associative array:

TYPE changedData IS RECORD (id int, name varchar2(255), vendor_id int);
TYPE changedDataArray IS TABLE OF changedData INDEX BY varchar2(255);

And I've populated it and it has all the data, awesome.

So how do I get that sent back to the client resembling rows? I could do DBMS_OUTPUT but that doesn't help when I'm trying to run this by a program expecting a tabular response. I've looked all over the internet and I'm at a loss.

Is making a temporary table and inserting the data into that and then selecting the only method? I'd do that except I don't have access to create a temporary table on the server, and getting permissions here is pulling teeth so if I can avoid that, it'd be awesome. I mean, I have the array right there - changedDataArray. And yet there seems to be no way to actually output that in a tabular fashion to the client?

• Justin Cave: What do you mean by "a procedure not stored"? A PL/SQL procedure is, by definition, a stored procedure unless you're talking about an Oracle Forms application where there is a PL/SQL VM on the client machine. Do you maybe mean that you're using an anonymous block? If so, an anonymous block has not interface so you generally can't return data from an anonymous block.

Answer 1

You can create a pipelined function. Some examples here.

Answer 2

You cant select from associative array.

You have only one way:

create package zzz AS 
 TYPE changedData IS RECORD (id int, name varchar2(255), vendor_id int, idx varchar(255));
 TYPE changedDataArray IS TABLE OF changedData INDEX BY **pls_binary**;
 dat changedDataArray;
end zzz;

and select in SQL:

select * from table(zzz.dat) where idx = 'some_key';