First the particulars:
Oracle7 Server Release 7.0.13.1.0 with procedural and distributed options.
PL/SQL Release 2.0.15.1.0
In attempting to use "stored procedures" I have run across a snag....
I would like to perform a query which is rather involved with multiple
sub-queries and a union with other sub-queries. I don't want to have
the parser parse this thing more than once, so I am trying to create
a stored procedure (like I was able to do in Sybase). The problem seems
to be that any "select" statement in a stored procedure must select
"into" a variable. This won't work very well due to the fact that
most of these queries return multiple rows. This doesn't seem to be
documented anywhere, but the parser generates a "expected 'into' but
found 'from'" type error when I do a "show errors" after I enter the
query. This procedure has 8 or so parameters where any or all default
to '%'. Let me give a simple example of what I'm trying to do: (note:
this is not the real query, just an example)
create procedure xx(name) as
begin
select * from all_users
where username = name;
end;
I then run it as such:
execute xx('M%');
What I expect back is 0 or more rows containing all users on the system
whose username begins with 'M'.
I've gotten some suggestions from people:
1) use a cursor. Not quite sure how to do this, not sure whether this will
return all of the rows at once.
2) use arrays passed in as parameters and select into the arrays. Not
quite sure that you can have arrays of strings here.
Advice on any of the above suggestions welcome.
I'm thought that I could trick the stored procedure by creating a table
from the results of the select, and then issuing another generic query
to return all the rows in that table. So I'd try something like:
create procedure xx(name)
as begin
create table xx_1 as
select * from all_users where username = name;
end;
execute xx('M%');
select * from xx_1;
This would add the overhead of building the table (to Oracle) but
maybe it would still be faster than re-parsing the query over and
over again. Another question came out talking with the boys...
If you parse the full query every time but use different values
in the "where" conditionals, is Oracle smart enough to figure out
that the query is really the same as the one it just got only the
values to the "where field = " are different? We need to be
cognizant of execution speed and server utilization due to the fact
that this is a real-time application with many clients connected and
accessing the database.
Any suggestions at all (Oracle are you listening) on how to do this
would be appreciated. I wanted to get the net.wisdom first before
going the Oracle support route. I've been told that I can get a
call in, but it would have to be through another contractor who is
also working on this contract.
Oh by the way, this is an HP 9000/700 class machine with 9.01 if
that makes any difference.
Kevin Smith