If you have something that is quite complicated then do build a stored procedure for it, but make use of the convenience of data-aware controls. However, I have been "playing" around with DOA's ability to use a cursor create from a procedure and it works pretty well.This allows you to still use data aware controls etc..I have taken the pkgapply demo a little further to do a refresh immediately upon editing and also to do optimistic locking (both simple when I read the help pages).
WRT storing the SQL on the server, I was thinking of deriving a component from the DOA Dataset that had a new property of an SQL reference, and it used this to extract the SQL from the Database.
I would then do some simple caching locally to improve speed etc..
If you're new to it (or even if you aren't) I strongly recommend three books by Steven Feuerstein (O'Reilly Press): _PL/SQL Programming_, _Advanced PL/SQL Programming_ and _Oracle built-in Packages_.
John Just for the record, I have not abandoned data-aware controls.
Andrew, You've raised some points that are the subject of almost theological debate among database designers.
As a background to this discussion, everything that Wayne says about stored procedures is correct. One school says perform all modifications to the database through parameterized stored procedures, simply passing values to the server.
All this adds up to *huge* performance improvements. Multiple applications, or one monolithic app broken into smaller pieces, all have access to these common server supplied procs.
Depending on the server (I don't personally know Oracle) there may be a security advantage as well - access only through the procedures means no-one can mess with the data through ad-hoc queries.
The other approach is to use data-aware controls with parameterized queries, and to prepare the queries.