2 Replies Latest reply: Dec 9, 2013 10:05 AM by CraigB RSS





      We Can create procedure  in diff. ways like database procedure, Program unit in oracle form or in library.


      How can we decide in which location procedure reside , so it will be useful.

        • 1. Re: Procdeure
          Christian Erlinger

          You put a procedure in a library when you can make use of it in multiple forms. This is not different from any other programming language: encapsulate logic where it makes sense to reuse that logic again. There is no exact rule of thumb for this. Don't overgeneralize code you'd need for one form because you want to use a logic (which can be used by one form module *only*) 100 years from now when one form gets added and might need a similar logic. And of course don't do the other extreme: don't write spagetti code .


          As far as stored procedures go: they can be used not only from forms, but from other tools as well. So the first thought might be: "Hey, I put everything in stored procedures, so I don't have to write that again in another tool". In essence this is correct, however you should be careful with that; I've seen some stored procedures which work well with the form they are designed for, but won't work at all if they need to be ported to e.g. java. And of course if the whole logic would have been done in forms it would have also been way easier.


          I tend to use stored procedures when for one process (e.g. add a person to the system) there are multiple tables involved, so from outside I'd simply tell the stored procedure to create a new person; what the stored procedure does (create a person, add it to some detail tables, what ever else) doesn't necessarily be the concern of the caller.


          You are of course constrainted by the technology as well; some things are available in stored procedures only (bulk processing for example - however I'd do that in a stored procedure anyway), so you should consider that as well.



          • 2. Re: Procdeure

            Here are the basic rules I use when determining where to put code.


            1.  If the routine is reusable and applies ONLY to Forms (eg: setting Item properties, making items visible or disabled, etc) I put these in a Forms PL/SQL Library and attach the library to my forms.

            2.  If the routine is reusable performs a lot of data fetching or manipulation then these I store in the database and call them from Forms.

            3.  If the routine is NOT reusable and is specific to one form, then I use Forms Program Units.


            Hope this helps.