Hi,

I look for a way to write a stored procedure, that returns more than 1
"row"s.

I want to do something like

select MyProc('A','B','C') from dual;

and get

ID TEXT
1 1stRow
2 2ndRow
3 3rdRow
..
999 999thRow

and so on.

If Possible it would be nice to do something like

select * from MyProc('A','B','C')

or

select * from MyProc('A','B','C') X
join MyTable Y on X.ID=Y.ID

Is there a way? (Oracle 9/11)

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

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

How are you wanting to do this i.e. from within PL/SQL, from java over
jdbc, from another language via odbc, dbi? The answers are going to be
different depending on your language/environment.

If your using pl/sql, java or perl, you can use a ref cursor, but there is no
need to do the select from dual, you can just call the procedure to get
the refcursor (as an IN/OUT parameter (or in the case of perl, but it
between begin ... end; - you could use a function that
returns a refcursor as well) and then just use the refcursor to query as
a resultset.

For the second two - well, I've never wanted to do anything like that
and suspect perhaps you may be asking the wrong question? Maybe explain
why you want to do this as I suspect there is possibly a better solution
that will achieve what you want.

Tim

--
tcross (at) rapttech dot com dot au

Tim X schrieb am 17.06.2011 in <877h8ldkxk.fsf (AT) puma (DOT) rapttech.com.au>:

Thank you for the answer, but I am afraid I am looking exactly for a
select construction.
I have data, that describes the changes of a road network. And I want to
write a PL/SQL- Procedure, where I give a piece of a road and want back
all the changes up to a special date. And because it is nessesary to
join the result in different ways to different tables I search for a
SELECT .. statement.

(very simplified) Example:
ID UNTIL NAME_OF_PIECE LENGTH FOLLOWER OPERATION
01 01.01.2011 RP01234 500 DELETED
02 01.01.2011 RP54321 1000 05 DIVIDED
03 01.01.2011 RP54321 1000 06 DIVIDED
04 10.01.2011 RP98765 500 07 CONNECTED
05 10.01.2011 RP54300 300 07 CONNECTED
06 02.06.2011 RP00021 700 ..
07 02.06.2011 RP98054 750 08 CHANGED
08 17.06.2011 RP98054 700 DELETED

If I Ask
WhatHappend(RP54321,200,500,31.12.2010,05.06.2011)
I need
STEP DATE NAME_OF_PIECE FROM TO
A 31.12.2010 RP54321 200 500
B 01.01.2011 RP54300 200 300
B 01.01.2011 RP00021 0 100
C 10.01.2011 RP98054 700 800
C 01.01.2011 RP00021 0 100
D 02.06.2011 RP98054 700 750
D 01.01.2011 RP00021 0 100

Maybe it is a little heavy to understand, but I have to combine this
lines with other information via SQL, so that I need a "table" as
output.
Sure, I could fill a table, but I don't want to.

I work on Oracle 9 and 11, use PL/SQL

Thanks in advance
Andreas Mosmann

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

A little mistake inside the needed result...

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

changed line 3,5,7, insteat of 100 must be 200
If I Ask
WhatHappend(RP54321,200,500,31.12.2010,05.06.2011)
I need
STEP DATE NAME_OF_PIECE FROM TO
A 31.12.2010 RP54321 200 500
B 01.01.2011 RP54300 200 300
B 01.01.2011 RP00021 0 200
C 10.01.2011 RP98054 700 800
C 01.01.2011 RP00021 0 200
D 02.06.2011 RP98054 700 750
D 01.01.2011 RP00021 0 200



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

Hi Andreas!

Andreas Mosmann wrote:

What about

select * from table(MyFunction('A','B','C'))

MyFunction then will be a table function (for a small amount of data only, otherwise
you might run out of memory), for more rows you can use a pipelined table function
(introduced in Oracle 9i). This one returns rows before the execution is finished.

You will need to create database types for the row type of the function. Additionally
you will create a table type as TABLE OF <row type from above>.

Hope that helps

Gunter in Orlando, Florida

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

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.

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.

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 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.

Unfortunately, its really difficult to provide precise advice without a
deeper understanding of the problem and outcome you are after. I could
easily be totally misunderstanding what you are doing and advice which
may be perfect in one situation is absolutely wrong in another even when
the problems look similar or you are dealing with the same problem
domain.

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.

Tim

--
tcross (at) rapttech dot com dot au

On 18.06.2011 02:57, Tim X wrote:
Yet another option would be to have a procedure that constructs an SQL
statement according to various input parameters and then executes it or
returns it. That approach could also help avoid retyping redundant
parts of queries.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

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

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.

minute to develop the query or a very short answering time and a many
hour develop of query for each user.
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.

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.

some rows by a PL/SQL stored procedure and use this in a select.

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.

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


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

Hi!

Andreas Mosmann wrote:
PL/SQL can access SQL, it does not work the other way round.

Gunter in Orlando

Andreas Mosmann wrote:
Before you can extend a collection you have to create it
(with the table type constructor).

It looks like you want to create a large set of data, so use the pipelined version:

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;