Very Computer

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

Two things:

1) Oracle V7 automatically caches the parse of your statement globally
across all processes.  Of course, it can trash that cache, but if you use
the statement often enough to matter, it probably isn't a big deal.

2) You'd probably be better off TRUNCATEing and INSERTing your temp
table, rather than recreating it all the time, if you really feel the
need to use one.

Note: I'm not sure that using a stored procedure will help; Oracle may
wish to leave the parse out of the compilation so that it can optimize
based on data distribution.

Quote:>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)

Just to answer this part of the question, the reason you must 'select...into..'
is that stored procedures are PL/SQL, which does not return rows like a
SQL query does.  There is no point in having a select statement in PL/SQL
without selecting it into something.  Cursors are the way to retrieve more
than one row, but it still wouldn't give you the result you are looking for.

Oracle 7 does cache the parsed queries.  Probably that's the easiest way to go.

--
Diana Tracy, System Designer            -- E*ment, Adventure

...

Quote:>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

.....

Quote:>What I expect back is 0 or more rows containing all users on the system
>whose username begins with 'M'.

Sybase stored procedures are very different from ORACLE stored procedures
in that you can fetch rows from the sybase s.p.  In other words,  a sybase
stored procedure returns a relation/result set/virtual table what have you.
 I was very dissapointed that ORACLE did not see fit to implement this
feature.  I think what you can do is create a package which contains a
cursor for the query.  then you can fetch the rows from a PL/SQL block.

for example:

create package test as
     cursor test_c is
            select * from dual;
END;
.
/

declare
   mydummy varchar2(1);
begin
   open test.test_c;
   loop
     fetch test.test_c into mydummy;
     exit when test.test_c%NOTFOUND;
   end loop;
   close test.test_c;
end;
/

Hope this helps,

Guy

----------------------------------------------------------------------


Melbourne Australia