I vaguely remembered seeing an SQL expression like:
select col1, col2 from my_function(some_value)

I wonder whether this is possible in Oracle? If so, how do you define
a function like that (e.g. what to return? )

Bing

Hello,

Thanks for this techy question and for using Google.

Yes, use of function in Oracle query is possible and it is easy too.

You can write a function with a return parameter like this:

CREATE OR REPLACE
FUNCTION cross_label(pKey1 IN NUMBER,pKey4 IN NUMBER) RETURN VARCHAR2
IS
  vReturn VARCHAR2(15);
BEGIN
  SELECT cross_label INTO vReturn
    FROM cross_lookup
   WHERE key1 = pKey1
     AND key4 = pKey4;
  RETURN(vReturn);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN('');
END;

Now, call this function in the select query like this

SELECT oe.pk, l1.label1, l4.label4, cross_label(oe.key1, cld.key4)
cross_label
  FROM outer_elim oe,  outer_elim_cld cld, lookup1 l1, lookup4 l4
 WHERE oe.pk = cld.pk
   AND oe.key1 = l1.key1
   AND cld.key4 = l4.key4

Check this site for more details with examples.
http://www.arrowsent.com/oratip/tip18.htm

A detailed explanation of creating functions and its structure can be
found here
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/statements_59.htm#2072792

There is a very well written document on creating user-defined
functions in Oracle. Check this link to find out more details
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=422&lngWId=5

An example of creating and using functions is here
http://www.fors.com/orasupp/d2k/plsql/16346_1.HTM

Search terms used:

User defined Functions in Oracle query

I hope this answers your question. Feel free to ask for
clarifications, and if you are satisfied with this answer, do rate it.

Regards
netcrazy

---

Request for Answer Clarification by seanomad-ga on 02 Jul 2002 13:44 PDT

netcrazy-ga, I have asked whether it is possible to select FROM a
function, not select a function from a table. e.g.

select col1, col2 from my_function(some_value)
but NOT
select col1, col2, my_function(some_value) from some_table

Please try again.

Thanks,

Bing

---

Clarification of Answer by netcrazy-ga on 03 Jul 2002 13:12 PDT

Hi again.

Yes, it is possible to select From "a function", but you cannot
directly specify a function in From clause.
You can select from a function in this way:
Select col1 from (select my_function(some_value) from DUAL) 

You’ll have to make use of DUAL table to get this. In addition, you
can use the same in this way too:
select my_function(some_value) from DUAL

This should give you the required result.

Please let me know whether this resolves your problem. I’m in a hurry
right now as have to catch my flight. If you have any clarifications
regarding this one, do leave a message and I’ll try to clear it.

Thanks
netcrazy

The first answer was not what I was asking at all. The second answer
told me mostly what I knew but lacks of supporting material or
authentic proof. Less than perfect. Thanks anyway.

Bing,

The suggestion to "select my_function(some_value) from dual" is
suitable if you are looking to have a SINGLE result returned from your
function.

A common reason that developers select from functions in Oracle is
that they want a multiple row result set returned from their function.

While researching your question, I came across an individual named
Thomas Kyte.  He works for Oracle and appears to be an expert on all
topics Oracle related.  He has contributed over 6 thousand answers to
the various Oracle newsgroups (2,580 to comp.databases.oracle.misc
alone!).

Mr. Kyte answered a question similar to yours and in his answer he
provides an example...

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF8&threadm=7t0434%24rj3%241%40autumn.news.rcn.net&rnum=11&prev=/groups%3Fq%3Doracle%2Bselect%2Bsyntax%2Bfunction%26start%3D10%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF8%26selm%3D7t0434%2524rj3%25241%2540autumn.news.rcn.net%26rnum%3D11

If you have further questions after reading the above discussion
thread, Oracle hosts a fourm called Ask Tom where you can ask him your
question directly.

http://asktom.oracle.com/ 


Searching hint...

The keyword FROM is an important part of your question yet Google
considers this a common word and eliminates it from the search.  To
get Google to put it back as part of the search criteria, prefix it
with the "+" sign.

Thanks for using Google Answers,

google1-ga