Forum OpenACS Development: Need help with a PL/SQL function that returns a table in a .tcl...

Hi all!

What we have is a tcl page that calls a PL/SQL function and this function returns a table.

The problem is that we don't know how to retrieve the table that is returned by the PL/SQL function efficiently.

Seems to be easy but we haven't managed to solve this and we found that other people had the same problem.

Our first approach is to save the resulting table from the PL/SQL function in an auxiliar table, and for each row inserted in that auxiliar table, increase one unit an integer (this should be done in the PL/SQL function). This integer should be returned to the tcl page instead of the table. With this integer, the tcl page could retrieve the rows from row number 0 until the integer's value (as if it was a sequence more or less... but this is not the point).

But this doesn't solve the basic problem: How to manage a table returned by a PL/SQL function in a tcl page?

Which is the best way to do this?

Thanks for your help!

Alejandro Beya

mailto:alejandro.beya@project-open.com
http://www.project-open.com/knowledge/

I will try to explain it!
The data resulting from the PL/SQL call is not a Oracle Table, but an "index-by table" datatype, so we don't know how to get this data out from a db_api call using TCL (a db_foreach or something...), and this is why we thought about storing the data in a temporal Oracle table while runnig the procedure to retrieve the information later in a second query, but it isn't very nice 😟

We can't change the PL/SQL function's returning data type, so it must be an index-by table.
Our idea is to write a second PL/SQL function, which would contain a call to the function returning the index-by table, and do something with the data in this table to return it to the TCL calling page... but have no idea about doing it.

Any suggestion?

thanks a lot!