Re: Selecting from fuction

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 01 Oct 1999 10:40:57 -0400
Message-ID: <8MP0N4CIENNK9rFKHauN2bT=nlF2@4ax.com>

A copy of this was sent to Arturo <paraf_at_sympatico.ca> (if that email address didn't require changing) On Fri, 01 Oct 1999 00:03:39 GMT, you wrote:

>Hi,
>
>Thanks for the hint.
>
>In fact, I have to create a function which would return MULTIPLY rows and I
>don't have a clue how to declare such a function and how to construct return
>statement. I saw Forms*5 based on functions and they were returning more then
>one row.
>

In forms you can build blocks on functions that return a CURSOR variable (see the URL in my signature for a short paper on returning result sets from stored procedures for more info on that).

In Oracle8.0 and up, you can write functions that return 'sets' (not cursor variables) and these can be used in SQL. for example:

tkyte_at_8.0> create or replace type myTableType as table of number;   2 /

Type created.

tkyte_at_8.0>
tkyte_at_8.0> REM now, we want to "select * from PLSQL_FUNCTION()" not from a table:
tkyte_at_8.0>
tkyte_at_8.0> create or replace function getMyTableType return myTableType   2 as
  3 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );   4 begin
  5 return l_x;
  6 end;
  7 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> REM here we go... selecting from it:
tkyte_at_8.0> 
tkyte_at_8.0> select a.column_value  val

  2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   3 /

       VAL

---

         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

While there is a trick with plsql tables that will let you do this in 7.1 and up -- its pretty cumbersome and not nearly as nice as the above.

You can get a good idea on how to do that see

http://www.deja.com/getdoc.xp?AN=417733109&fmt=text

>I appreciate any help.
>
>Thank you, Arthur.
>
>Alan Shein wrote:
>
>> >>
>> I know that on Oracle8 I can use select statement to select from objects
>>
>> like functions. Can I do that on Oracle 7.3.4?
>>
>> Also, could you give me a simple example of such a function?
>>
>> <<
>>
>> Generally, the syntax is:
>>
>> SELECT function_name (parameter1, parameter2...) FROM DUAL;
>>
>> You select the function from DUAL and pass it parameters. For example, here
>> is the usage syntax for a function I created that returns the number of
>> business days between two dates:
>>
>> SELECT num_business_days ( '01-FEB-99' , '15-FEB-99' ) FROM DUAL;
>>
>> Or, you can use a built-in function, like sysdate (which takes no
>> parameters):
>>
>> SELECT sysdate FROM DUAL;
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 01 1999 - 09:40:57 CDT