HOWTO: STORED PROCEDURE RETURNING MANY ROWS

#11

Gunter Herrmann schrieb am 20.06.2011 in
<4dffb7d7$0$7617$9b4e6d93 (AT) newsspool1 (DOT) arcor-online.net>:

Quote:

It looks like you want to create a large set of data, so use the pipelined
version:
Not really. This function will return something between null and 20

lines, but

Quote:

FUNCTION fun_something
RETURN some_table_type PIPELINED
IS
lrec_output some_row_type;
BEGIN
-- some loop here

lrec_output_row := some_row_type(something, somethingelse,
somethingmore);
PIPE ROW (lrec_output_row);
-- end of loop here
RETURN; -- note that you return nothing!
END;
works fine.

Many thanks, you gave me the answers, I needed. I'll post the complete
solution (abstracted) for others, maybe someone needs it.

Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

#12

Andreas Mosmann schrieb am 16.06.2011 in
<1308210796.89 (AT) user (DOT) newsoffice.de>:

Special thanks to Gunther Herrmann, he gave me the answers, I searched
for.
The following example is only to demonstrate, how to write a stored
procedure (function), that returns many rows.
I would prefer to stuff it into a package, but I found no working way
and so I did it like the following:

--create a rowtype definition for the wished table
create type T_RETURN_LINE as object ( ID number, Datum Date, Text
varchar2(256));

--create a table definition
create type T_RETURN_TABLE is table of T_RETURN_LINE;

--a simple function that returns some useless Rows with some useless
Content
create function ReturnManyRows(InputFrom in number,InputTo in number)
return T_RETURN_TABLE PIPELINED is
i number;
MyRow T_RETURN_LINE;
begin
for i in InputFrom..InputTo loop
MyRow:=T_RETURN_LINE(NULL,sysdate,'HELLO');
MyRow.ID:=i;
PIPE ROW (MyRow);
end loop;
return;
end;

--The select- statement
select * from table(ReturnManyRows(1,5));

Maybe anyone can use it.

Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

#13

Andreas Mosmann <mosmann (AT) expires-30-06-2011 (DOT) news-group.org> writes:

Quote:

Tim X schrieb am 18.06.2011 in <87y610c5ws.fsf (AT) puma (DOT) rapttech.com.au>:

If I understand you correctly, I think your approach is flawed. If you
are going to select data simply as input to use in a join with data from
another query, your better off just doing it all as 1 sql statement.
No, because I had within every statement the complex logic of the procedure
above. I did not mention but in fact the result set of the stored procedure is
to be combined many times with different other tables/views from users, that do
not have to understand the logic behind the scene.

Your right, I don't understand the details of what you need. However, I
still suggest you need to give up on the approach you have settled on
and come from a different direction.

Quote:

The problem with your approach is that you are working hard to
circumvent all of Oracle's efforts to make things as optimized as
possible.
I have to decide whether I prefer some seconds more answering time and a minute
to develop the query or a very short answering time and a many hour develop of
query for each user.

The former is always better. However, I would suggest there is something
very wrong somewhere if the options are that simple.

Quote:

I know, I could solve my problem by creating a table/temp table as result of my
stored procedure. But that is not my question. There are about 10000 records to
be processed, this can not so much time that I want to create/drop tables with
5 rows each time I use the procedure.

Temporary tabes etc are almost never the right solution.
If you only have 10,000 records and yet ALL the SQL is becoming
extremely complex with lots of repetition, I would begin to suspect that
there is something very wrong with the underlying design of the
database.

Quote:

Consider two simple scenarios. In the first one, you use various SQL
statements to extract the final set of data you want. The statements are
placed inside a procedure that allows you to both pass various values
used to define/control the result set (via bind variables) and set a ref
cursor to pass back which you can then query and further process or
display the data. Oracle will e able to use any relevant indexes,
exploit caching of SQL statements and take full advantage of the
optimiser.

Now consider your (as I understand it) approach. You want to define
procedures that will return a result set whih you then want to use in
further queries involving joins and other operations. In this case,
you want to effectively generate a pseudo table via a procedure call
which you then use in joins and additonal SQL to extract the final
result. However, there wil be no indexes, optimiser statistics and
little caching opportunities that the DB can use to make things as
efficient as possible.
I am not absolutely sure, because the result set of the procedure is very
small. For this I do not need an index. I know, that oracle likes corresponding
indexes in two combined tables but nevertheless I can tell it to use an index
for the corresponding table.
Something like
select /*+ index(B) */ ...
from MyResultSet A
join AnyOtherTable B on A.B_ID=B.A_ID
So I suppose this wouldn't be my problem.

There are many ways to solve my problem but there sure is no simple sql I could
use. I definitely need program code and definitely have to combine it with
other tables. But I would prefer to do it on the server and not on the client.
And I prefer to do it without temporary tables. And I do not want to write the
same procedure 1000 times for each scenario a user could need.

All admirable goals and worth striving for. However, the model you are
trying to apply doesn't look right.

Quote:

I also suspect there are other pitfalls with the approach you are
adopting. At a guess, I would think you have hit upon this idea because
you observed that many of your SQL queries had a common element, making
it fairly logical to want to isolate that bit of common SQL in one place
- less typing, potentially more maintainable, cleaner code etc. All good
objectives to strive for. However, you also need to consider how Oracle
processes queries and strive for a balance.
This I try. But I can not pass the pitfalls before I know how to return some
rows by a PL/SQL stored procedure and use this in a select.

Well, he bad news is I don't think you can do it the way your hoping.
Others have shown how you could use a procedure, but I don't think you
can do what your wanting. One of the problems I can see is that apart
from ref cursors, I don't know of a way you can return multiple values
from a function (procedures don't return values) other than by using
PL/SQl collection types, but SQL doesn't understand PL/SQL specific data
types.

Quote:

If you are looking at ways to minimise SQl and ensure consistency or
make things easier to maintain, maybe look at other Oracle features. For
example, rather that your suggestion of a procedure that returns a
result set, perhaps a better solution would be to define a materialised
view and join with that view to get your final result? Perhaps use
functions/procedures that will return a different ref cursor depending
on some argument or maybe the whole performance can be improved by using
a PL/SQL collection type, which is passed around to different procedures
for additional processing so that there is only 1 initial query etc.
I think I exactly look for the answer of my question. Materialized views do not
work because I probably need 1 View for each row of my table. With collection
types I still did not work but I suppose I could not combine it with other
tables by select statement.

Don't think of the materialised view as representing just the rows of
interest. Instead, think of the materialised view as representing a
smaller sub-set of your data - a part of your sql - which has been
factored out into the view. You then join with the view and add
additional conditions to further refine the result set. This
should/could make your SQL simpler and avoid the problems of having
multiple instances of very complex SQL that is difficult to maintain.

Quote:

Tim
Thank you
Andreas

By the way:
function MyProc(Param1 in char,Param2 in char...) return MyPackage.MyRefTable;
can be compiled but if I use it like

Select MyProc('P1','P2') from dual

I get the error ora-00902,if

Select * from MyProc('P1','P2')

I get ora-00933

Look at the section from the SQL Reference manual that talks about the
limitations of user defined functions that need to be followed in order
to make the function callable from SQL. The basic rule is that the
function can only accept and return values that are understood by SQL.
While you don't show your definition of 'MyRefTable, I suspect it is a
PL/SQL type which is not recognised by SQL

Remember that SQL and PL/SQL are two different processing engines with
their own data types. While PL/SQL knows about most (all?) the types SQL
has, the opposite is not true. There are many types (especially
collection types) which SQL knows nothing about or can only partially
parse.

Tim

--
tcross (at) rapttech dot com dot au

#14

Tim X schrieb am 22.06.2011 in <87zklae3ab.fsf (AT) puma (DOT) rapttech.com.au>:
Hi Tim,

I do not know whether you read my answers to Gunther.
In fact with his help I found exact the answers I looked for.
So I can say: Yes, there is a way to return many rows by a stored
procedure (function) and there is a small example how this can be done.

Maybe the structure of database is stupid.
Maybe this way takes much ressources.
But all this is not the question at this moment.

If there is a better way to solve the problem I described - I am
interested.
But in this thread it is not my main interest to talk about database
structure or database ressources, sorry.
Whether it is useful to follow the way above depends on many things,
such as maintaining code, handling queries etc. And in my - maybe very
special - case it seems to be a useful way to do something like

select P.F1,P.F2,Q.F3,Q.F4
from MyProc(Param1,Param2) P
join MyQuery Q on Q.Key1=P.Key1 and Q.Key2=P.Key2

or anything like this.

I hope you can accept this even if it is another way you prefer.

Thank you
Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

HOWTO: STORED PROCEDURE RETURNING MANY ROWS

comp.databases.oracle.misc comp.databases.oracle.misc

Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS - 06-21-2011 , 12:55 AM

Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS - 06-21-2011 , 01:19 AM

Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS - 06-22-2011 , 02:25 AM

Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS - 06-22-2011 , 08:18 AM