Putting everything into Oracle procedures is the approach the project I'm working on right now has taken. Any result-set to be retrieved from the database is obtained by calling a function which returns a reference cursor. The user connected to Oracle has no select, insert, update, delete privileges at all but the packages containing the procs/funcs are created with definer rights instead of executor rights so whilst running something in the package they can update/delete/select/insert. Works well and the only SQL outside of Oracle is a "begin ? := func_or_proc(parameters)" which we have of course put into a single perl module.

Yes, the thing is how much SQL manipulation do you need to do? For instance, do you sometimes need to add LIMIT clauses? Or count rows instead of just returning them?

Or maybe the columns you need back change dynamically?

Stored procedures and phrasebooks share some similar issues that dave mentions here

We don't do any SQL manipulation outside the database. If we need a result-set where one of the columns = X or like Y or some other comparison, the value being tested against is passed as an argument although the cases other than equality are almost non-existent. We have no cases of changing the number of rows in the result-set since there is a procedure/function which provides exactly what is required. I believe we've also got no occurrences of dynamic SQL in the packages. Perhaps we are lucky in being able to do this but I think it was more by design.

The outside Perl knows nothing at all about the structure of the database tables, it only knows about the procedures and functions we expose from the packages. Even though this application is fairly large we've had no real problems making it this way and as the procedure and function interfaces are well defined we can work on the database side independently from the Perl (and other languages) on the outside.

Putting everything into Oracle procedures is the approach the project I'm working on right now has taken.

Can you comment on how fast ORacle stored procs are compared to SQL? I know that the query optimizer has no access to stored functions in MySQL and that things often run many times slower.

Also, how easy is it to dynamically generate SQL with stored procedures? What if you had a search form with an number of optional fields and various comparison operators? Could you build up the SQL piece-wise and then evaluate it after constructing it?

Finally, I find the stored procedure language quite a bit more wordy than Perl. It would seem that if the database knew the relations of your tables that it could generate common things you would want:

1. given this id, get the related info in all tables which use this id as a foreign key
2. instead of returning a cursor to a result set, give me the count of the result set
3. paginate the result set: given an offset and row count, point the return cursor there

Now, the SP approach does have its advantages, but I'm just exploring some potential cons.

As far as I am aware there is no disadvantage to using cursors inside oracle stored procedures or functions. In fact, the cursors can be cached. I don't use mysql so cannot comment on that.

You can use dynamic sql in oracle stored procedures but it is generally not such a good idea as oracle cannot know the sql you will be running so optimization of it prior to running it is less likely. Our project uses no dynamic sql at all (we have gone out of our way to avoid it). If a procedure/function which generates and returns a cursor can take optional arguments (e.g., ones we we use in the SQL) then we default them to NULL and ensure the SQL does not perform the comparison on the column if it is NULL e.g., (where (param is NULL) or (param is not null and param = column).

I don't really see any cons to using procedures in oracle and for a) speed and optimization, b) keeping logic in the database near the actual schema and data it is unmatched. However, if your only option is building dynamic sql then this is easily done in Perl.