Welcome, Guest
Username: Password: Remember me

TOPIC: returning a result set from nested table

returning a result set from nested table 5 years 5 months ago #47

In a stored procedure/function what is the best technique to return rows to a calling client from a NESTED TABLE collection set?
The administrator has disabled public write access.

Re:returning a result set from nested table 5 years 5 months ago #50

I think the best way to answer the question is to ask: what do you plan to do with the data when you get them?

If the program just passes the data to another process, then you may not have a choice because the next recipient may not be flexible. But if the next recipient is flexible, then you'd want to fetch the data in a form that lends itself well to interfaces.

But if the program is processing the data, the question becomes: is it better for the PL/SQL code to "reformat" the data so that it is easy for the program to fetch or is it better for the program to fetch a complicated structure, parse it, and process it.

I think the answer depends on two things: the database driver (e.g., jdbc) and the client programming language (e.g., java).

Please answer these questions first and then I'll continue.
The administrator has disabled public write access.

Re:returning a result set from nested table 5 years 5 months ago #51

It is JAVA middle tier using JDBC. They want the results in the form of rows i.e the standard resultset you would receive if the JAVA program issued a SELECT to the database
Last Edit: 5 years 5 months ago by Dennis .
The administrator has disabled public write access.

Re:returning a result set from nested table 5 years 4 months ago #52

I think an example of the table's ddl and an example of the desired output would be help.

But, the following demonstrates how the java code could execute a SQL statement to fetch a table based upon a nested table or if the java program executed a PL/SQL procedure that returns a ref cursor to a similar java program.
create or replace type loanlist as table of number;
/

create table borrower (
    name varchar2(30),
    loans loanlist)
nested table loans store as borrower_loans;

insert into borrower (name, loans) values
('jeff',loanlist(1,2,3));

commit;

rem If you don't want to use a ref cursor, then your program can do this:
select * from table(select loans from borrower);

rem If you want to use a refcursor, then your java program can execute
rem PL/SQL to parse the query.

var ref refcursor
begin
open :ref for
select * from table(select loans from borrower);
end;
/

rem  If you trace this, you'll see what your java program has to do
rem  to fetch from the ref cursor.
print ref
The administrator has disabled public write access.

Re:returning a result set from nested table 5 years 4 months ago #55

Jeff,
This is what I want to do. This is not a OLTP transaction so the overhead of using a ref cursor is not a problem.

create or replace package market_stats
AS
type rc is ref cursor;
function market_rslt (p_cursor in out rc) return rc;
end
/
create or replace package body market stats
AS
function market_rslt(p_cursor in out rc)
is
TYPE market_rec is RECORD (
storeno number(20)
,bal number(20,2)
);


TYPE type_mkt_tab IS TABLE OF output_rec;

tab_mkt type_mkt_tab;
BEGIN
-- now populate the table tab_mkt and then

-- return cursor. This is where I am getting syntax errors

open p_cusor for select * from TABLE(CAST( tab_mkt as type_mkt_tab ????

End;
The administrator has disabled public write access.

Re:returning a result set from nested table 5 years 4 months ago #56

I think where I was getting off course was your original question "best technique to return rows to a calling client from a NESTED TABLE collection set?"

I'm probably way beyond being able to change the way I interpret "best technique", which is "which technique will result in the least response time".

But now that I see more information, I think I can ask better questions now that I know that no technique has worked so far.

When you refer to the code "now populate the table tab_mkt" are you referring to a lot of code that's complicated or is it simple code? In other words, would it be difficult to change that code so that it inserts into a temporary table (as opposed to a permanent table).

If you create a temporary table, then constructing a select statement on that table is quite simple.
The administrator has disabled public write access.