Re: oracle package & web server question
Date: Thu, 05 Sep 2002 23:17:53 GMT
Message-ID: <BCRd9.149404$kp.765196_at_rwcrnsc52.ops.asp.att.net>
Well, I'll take a stab at this.... One of the concepts you are dealing with is why should a package be faster than other queries. Consider that the package is actually "compiled" and resides in the database. Which means, as I understand it, optimization is already been done and there is no need to pass the query to the database for optimization and execution, so there should be some performance gains.
As for how to process your function or procedures.... You didn't mention what environment you are working in, but you did mention JSP, ASP, etc... Please understand that you can start a procedure or a function in a package and it can call other procedures or functions without ever returning anything to the browser until everything is completely done. So assuming you have a set of steps that would walk you through your process, then you just need to lay them out in some sort of logical order in your package then process the initial procedure/function in the package. Additionally, you don't have to pass (although you can) resultsets between procedures or functions. You could do something similar to:
procedure myProcess
IS
CURSOR c_getwork IS
select fieldname1, fieldname2, etc..
from sometable
where this = that;
begin
for x in c_getwork loop /* loop through the resultset */
update someothertable
set otherfield1 = x.fieldname1,
otherfield2 = x.fieldname2,
etc, etc
where somethingelse = somevalue;
commit;
end loop;
end;
If on the otherhand you really need or want to pass resultsets between procedures or functions, then you will have to place the resultsets in "table types". That is declare your results as some sort of Record type, then declare a table of that Record type... such as
TYPE emp_rec IS RECORD(
empid integer,
fname varchar2(35),
lname varchar2(35)
);
emp_table_type is TABLE of emp_rec
INDEX BY BINARY_INTEGER;
myemployees emp_table_type;
If you do something like that in the package specification, outside of a procedure or function definition, then you can use those types in your package to pass recordsets between procedures or functions.
James Cummings
"Chaos, panic, disorder.... my work is done."
"Gus" <tuolden_at_rice.edu> wrote in message
news:41648312.0209050624.6ba062b_at_posting.google.com...
> Oracle GURUs out there, here is a set of questions from a newbe in
> this area.
>
> I'm trying to understand the package (functions & procedure) concept.
> But I still have one thing that I have not yet understood.
>
> All the functions/procedures that I have developed so far
> INSERT,
> UPDATE,
> DELETE rows from tables
>
> But when it comes to SELECTING rows I have a confussion.
> I can retrieve a single row with a procedure and the value is returned
> through an OUT variable with a value BLA.
> Same things goes for the function (either throught RETURN statement or
> OUT variables), but when I want to return mutlple rows I know I need a
> cursor. But how to I return a "set of rows" to a statement?
>
> So you undertand my problem a little better, here is what I'm trying
> to do:
> - generate package with some procedure/ functions that perfoms
> actions on tables.
> - give rights to certain users to these objects
> - using JSP, ASP, PHP .... execute this package.function to
> retrieve mutiple rows. IE: give me all the poeple who have not payed.
>
> Questions:
> 1) How to do this? I know how to run a simple query for example
> "SELECT * from PEOPLE WHERE payed == FALSE" & this returns to me a set
> of rows. But now I want to run something like "EXECUTE
> PeoplePackage.HaveNotPayed()" & this return a set of rows for me.
> 2) The reason I'm doing this is 4 fold:
> a) security reasons. I know I can set
> privalages to certaina packages. But Can I do the same for views? I
> thnk so.
> b) performace reasons. According to certain
> books, tutorials I have learned that packages may function faster than
> certain queries. This concept still does not convince me. When
> retrieving N rows from a certain table, does this change the
> perfomance? What I mean is time-wise a query running in a view Vs
> query running in a stored procedure.
> c) Further validation: I am validating certain
> data in the web server before being sent to the DB. There can be
> further validartion in the DB server. But I still dont know if this is
> necesary? Performance wise it could cause a big effect to have double
> validation. Or maybe just leave the validation in 1 of the server?
> d) Security 2: Not the same telling PHP to run
> "SELECT * FROM ....." that telling it to run "EXECUTE PROCEDURE_BLA".
> If someone hacks into the code, they will also have to hack the DB
> server. More securtiy. I am right?
>
>
> Are these reasons justification enough to change throuhg the use of
> packages or should I stick to conventional queries?
>
> Sorry about the many questions.
Received on Fri Sep 06 2002 - 01:17:53 CEST