This discussion is archived
7 Replies Latest reply: Jul 8, 2013 8:18 AM by lake RSS

Difference between Stored Procedure & Stored Function

1009739 Newbie
Currently Being Moderated

I want to know the difference between Procedure Procedure & Stored Function in compiler in oracle 10g.

 

Thanks in advance

  • 1. Re: Difference between Stored Procedure & Stored Function
    rgvg Explorer
    Currently Being Moderated

    procedures return nothing and functions return something.

  • 3. Re: Difference between Stored Procedure & Stored Function
    Christian Erlinger Guru
    Currently Being Moderated

    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.

     

    cheers

  • 4. Re: Difference between Stored Procedure & Stored Function
    lake Journeyer
    Currently Being Moderated

    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.

     

    One of the great things about forms is that you can execute plsql and sql both in a pretty free form way. 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.  Just thought I'd mention that. I think it's great to have as many solutions as possible at your fingertips. I'd like to see more. More ability to embed javascript "in there somewhere" to affect form behavior, easy ability to call java, html5 whatever. I want stuff to call! I want a lot of ways to do untoward things in forms! :-)

  • 5. Re: Difference between Stored Procedure & Stored Function
    Christian Erlinger Guru
    Currently Being Moderated
    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

     

    cheers

  • 6. Re: Difference between Stored Procedure & Stored Function
    Marwim Expert
    Currently Being Moderated

    Please stop asking this kind of question in this forum space. They are not related to Oracle Forms.

    You have been told more than once to use SQL and PL/SQL instead.

  • 7. Re: Difference between Stored Procedure & Stored Function
    lake Journeyer
    Currently Being Moderated

    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.)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points