Here you go again posting questions you can easily look up yourself in the wrong forum. As you have been told before (What is Advantage and Disadvantage in Stored Procedure? or What is Advantages and Disadvantages of Package?) you should post such kind of questions in the SQL and PL/SQL forum.
Yeah. Why do people keep asking this question? I have a feeling that somewhere there is a class or a recruiting effort where this question is asked. And you know, the true answer and what someone else thinks is the right answer can be two different things. Find someone who passed the class, got hired and ask them what they said.
Thus you can call functions and you can call plsql procedures from plsql code in triggers or stored programs or packages. So you don't have to chose one or the other in forms.
True that. What is important however is to define rules when to use procedures and when to use functions. Far too often I see code like this:
if do_something then message('OK'); else message('NOT OK'); end if;
whereas the implementation of the function is
function do_something return boolean is begin [...]; return true; exception when others then return false; end;
Which basically tells me that something went wrong but keeps quiet about what. This would be a sample where *not* to use a function
I've been guilty of doing that myself but you can put a when others exception in a procedure also :-)
You can have an issue in forms triggers where it throws an exception for no known reason and the only way to throttle it is to have the everything exception. I think there is a bug in there that is exposed under some circumstances and everyone has run into it, that's my theory. You're definitely right that having the everything exception in the code leads to impossible to debug problems. I think we could use some error handling enhancements.
I'd like to see an all-purpose error function in forms. Instead of the customer having to program to catch all the various kinds of errors that can occur, plsql, database, forms, .... I'd like to be able to configure the form to have a built in error handling system show the error with an option to programatically handle the error unseen. The forms thing that it shows errors on the bottom of the screen does not work at all in my experience where we deploy forms. Windows by default has large icons and they cover up the error area. Even if you get the user to change the icons to small the users never see those messages. And they will not display errors in forms via the help menu either. (help,display error). I would like to be able to filter error messages, translate error messages with some kind of translation table (make user understandable), and have them pop up on the screen in a window.
I think some of the database functions that are "error-happy" should be provided in additional constructs. It is a design decision to have this throw an exception when the record doesn't exist:
select mycolumn into myfield from sometable where mykey = 'X';
I think many of us would prefer to have it simply not throw an exception if the record doesn't exist, so we don't have to remember what on earth the exception was called, and test the results of myfield and/or a status field.
How else to do this? How about this?:
minselect mycolumn into myfield, mystatus from sometable where mykey = 'X';
Here we define a new form of select called minselect that is reluctant to throw exceptions, but puts a status code in
the status field should someone have to know the difference of whether myfield was null in an existing record or if there was no qualifying record.
What's with the minselect? Well we would have a minselect and a maxselect that would find ONE qualifying record, the min or the max to get around that exception problem #2, select returning multiple values. (We would have the status reflect the fact there were multiple values.)