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?

  • 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. – Justin Cave Dec 12 '15 at 5:08

You can create a pipelined function. Some examples here.

| improve this answer | |

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';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.