Google Answers Logo
View Question
Q: Oracle database: can you select from a function ( Answered 2 out of 5 stars,   1 Comment )
Subject: Oracle database: can you select from a function
Category: Computers > Programming
Asked by: seanomad-ga
List Price: $7.50
Posted: 02 Jul 2002 11:11 PDT
Expires: 01 Aug 2002 11:11 PDT
Question ID: 35870
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? )

Subject: Re: Oracle database: can you select from a function
Answered By: netcrazy-ga on 02 Jul 2002 12:02 PDT
Rated:2 out of 5 stars

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:

  vReturn VARCHAR2(15);
  SELECT cross_label INTO vReturn
    FROM cross_lookup
   WHERE key1 = pKey1
     AND key4 = pKey4;


Now, call this function in the select query like this

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

Check this site for more details with examples.

A detailed explanation of creating functions and its structure can be
found here

There is a very well written document on creating user-defined
functions in Oracle. Check this link to find out more details

An example of creating and using functions is here

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.


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.



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.

seanomad-ga rated this answer:2 out of 5 stars
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.

Subject: Re: Oracle database: can you select from a function
From: google1-ga on 04 Jul 2002 14:09 PDT

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

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

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

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. 

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,


Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy