I really would like to know what exactly I should write in each forms based in procedure, to improve forms execution. Particulary what should I do in LOCK procedure?

i tried this,
but the query procedure which i wrote on emp table,
does not work if i pass the argument for deptno,
with out that if only one out parameter of ref cursor is
used for returning the result set it works,
what is it that i am missing while passing the argument
of deptno from data source argument, that it just does not work
I also tried where clause in form property but it does not work,
what do i have to do this to work for a in argument.
Pls help, though i know i should not aks a question in review column but then it has not been useful comletely,
and i am unable to understand where what i am missing to pass.

Regards,
Geeta

In addition to Tom's nice aspects, I would add:

o You can write unit tests for business rules implemented in the database.

o You can denormalise the relational data and make the Forms block work with richer entities. This is sort of like using object views with the trigger code moved into the stored procedures. I originally tried to base my app on object views but our version of SQL*Plus (3.3.4) couldn't deal with objects reliably (and SQL*Plus 8.1.5 fails to connect with a TNS:protocol adapter error, but I digress...)

o You get to edit your business code in any number of superior alternatives to the Forms PL/SQL Editor (Toad, SQL Navigator or - from SQL Plus - Emacs, vi or even... Notepad!)

It's just better to architect your application with the business logic close to the data. I see too many Forms with business rules locked into Forms program units or shared libraries which is unavailable to the database. In fairness, many of these were written when Forms 4 was young, but people are slow to embrace progress. I would go so far as to suggest that anyone [developing with Forms 5 and 8i or better] who puts business logic anywhere in a Form should be marched into the parking lot and shot (actually, no, just flayed; everyone deserves a second chance).

I fully endorse Tom's comment that building Forms this way is more work. If you want to save hours of pain, remember the following:

* If you recompile your stored package spec, reconnect
from Forms before attempting to generate (Forms will
crash or generate Internal Errors; restart Forms).

* Save early and often. Save before generating,
compiling, fetching a drink or blinking.

* The Data Block Wizard is your friend. In particular,
it saves you the pain of having to specify the columns
using the property editor and it doesn't trash the
properties of the items already in place. You find
yourself needing to use the Wizard when you change the
record type on which your block is based (which happens
a lot during initial development).

IMO, the benefits of testability and maintainability outweigh these comparitively minor inconveniences.

Cheers.

Everybody recommends basing blocks on stored procedures but nobody really demonstrates a full-blown example of how to do it: When basing a block on a stored procedure, you have to write your own query-logic. And it is difficult to provide the same functionality as Oracle Forms.
Specific problem: Lets say that field f is NUMBER field in a block based on a stored procedure... You can emulate query logic so that if user presses key-enter-query, puts a '9' in field f and presses key-execute-query the logic returns all rows matching '9' in field f. But if you enter '>8' in field f, normal Formsbehaviour is set aside and you receive an error 'illegal value in a number field'.

So - how do you fix that one? Customer has heard a lot about the advantages of blocks based on stored procedures but has never heard that he is no longer allowed to search for number values greater than 8 as he has always done.