oracle package & web server question

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.

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:
3. security reasons. I know I can set privalages to certaina packages. But Can I do the same for views? I thnk so.
4. 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.
5. 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?
6. 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 Thu Sep 05 2002 - 16:24:55 CEST