Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql function

Re: pl/sql function

From: Tim Cross <tcross_at_nospam.une.edu.au>
Date: 23 Jul 2002 10:36:35 +1000
Message-ID: <871y9v8elo.fsf@blind-bat.une.edu.au>


"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> writes:

> "Tim Cross" <tcross_at_nospam.une.edu.au> wrote in message
> news:8765z88tn4.fsf_at_blind-bat.une.edu.au...
> > "Peter" <depend3_at_yahoo.com> writes:
> >
> > > Is it possible to return two values when you write a function without
> > > concat? If yes, how do you do this?
> > > Thanks.

> > As far as I know, you can only return one "thing" - however that
> > "thing" could be a record which contains multiple other "things" - so
> > if you need to return two (or more) distinct values, define a record
> > which represents the values to be returned, put those values in the
> > record and return that.
> >
> > Alternatively, make one of the values you want to return an IN OUT
> > parameter to the function call and put one value in that parameter and
> > return the other.
>
> The latter of which is definitely programming with side-effects, and as this
> usually results in hard-to-debug programs, should be considered bad
> programming practice. If you want to make a gordian knot of your programs,.
> fine with me, but please don't recommend this to others.
>

Well, just a few comments -

  1. To some extent I agree - especially with respect to functions and I probably should have said it would be better to change the function to a procedure. However, this is a matter of taste/style. I tend to avoid using OUT or IN/OUT parameters in functions and have adopted the practice of only using IN parameters to functions which return a single value (which may be a record etc) and use IN/OUT or OUT parameters only in procedures - but this is just a matter of personal taste. Oracle don't seem to follow any such methodology and use both In and OUT parameters in functions as well as procedures.
  2. I don't agree with blanket statements such as "don't use that feature because its bad programming practice". What really matters is how you use the feature, how often and to what extent your use is an abuse of the feature. I agree programs can become difficult to follow if you end up with deeply nested funcitons/procedures which all pass a common value as an OUT parameter etc, but there are as many examples where using such a feature can create clearer and more easily understood algorithms than going through some contortion to avoid a standard feature of the language. To some extent, it is like the infamous goto statement - I was always told "never use goto, it is bad programming practice" and while agree it is a statement which can create difficult to follow code, there are situations where judicious use of goto statements can produce clearer and easier to understand code, even if such circumstances are rare.
  3. Oracle makes use of OUT parameters in both functions and procedures which make up their built-in packages. If it is a feature they feel justified in utilizing and one which they have included in the language design, I cannot see why developers should not take advantage of it.
  4. I'm not sure that using OUT is as clear an example of the "side effect problem" as using a value as IN OUT - If an argument is defined as OUT, you can make the assumption the parameter is not being used as input to the procedure/function and that it is being used to communicate a value back out to the calling block. This is much clearer than in other languages such as C where no indication is given as to how the parameter is being used - something may be passed by reference because it is being modified by the procedure/function or simply to improve performance by eliminating the need to copy large data structures or avoid stack overflow etc. Despite this "limitation" C and C++ are still very much alive.

Finally, while you are certainly entitled to your opinions regarding the "correct" way to do things and you are free to criticise advice given by others in this newsgroup, you do not have the right to tell me what I should or should not recommend to others. The amount of information provided by posters looking for solutions is often sketchy at best and it is often impossible to judge what is the best solution to their problem. I will continue to provide whatever suggestions I can think of as solutions to posted problems - it is up to the reader to decide on the merits of any suggestions provided in these newsgroups. I don't claim to be any sort of expert. My only motivation is to try and put back into a resource which I've found useful. If I see a question and believe I might be able to assist by either providing possible solutions, suggestions on where to find solutions or whatever, I will continue to do so, irrespective of the opinions of self-appointed group moderators who believe they have the right to try and dictate some sort of minimal technical standard which must be met before you can reply to posts in this "open" forum.

Tim

-- 
Tim Cross					E-Mail: tcross_at_pobox.une.edu.au
Analyst/Programmer                               Phone: 6773 3210
Applications Group			        Mobile: 0412 969193
University of New England
---
find / -iname microsoft -exec rm -rf {} \;
Received on Mon Jul 22 2002 - 19:36:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US