Oracle 8i: PL/SQL table use in Function

From: BettyL <betty_low_at_my-deja.com>
Date: 18 Jul 2001 06:46:39 -0700
Message-ID: <a6634b7.0107180546.30d837c4_at_posting.google.com>


New to using PL/SQL tables and types in Oracle 8i...

Create a package with the following:

TYPE NUMLIST IS TABLE OF FLOAT; Say, I have a function that I later want to use in a select query:

CREATE OR REPLACE FUNCTION TEST(fieldname IN FLOAT) RETURN FLOAT IS mytable NUMLIST;
temp FLOAT;
TYPE c1 IS REF CURSOR;
my_cur c1;
BEGIN
mytable := <--how to populate from field values passed into function OPEN my_cur FOR <--pass plsql table into REF CURSOR after sorting in ascending order
LOOP
FETCH my_cur INTO temp;
--processing goes here
END LOOP;
CLOSE my_cur;
RETURN temp;
END;



Select test(fieldname) from tablename;

***
Basically, the two questions are:
1) how do I populate the mytable variable which is of type NUMLIST with the values passed into function?
2) how do I sort the PL/SQL table in ascending order and pass it into a REF CURSOR for processing? I've seen some logic done with using the cast function...

Any help would greatly be appreciated. Thanks in advance. Received on Wed Jul 18 2001 - 15:46:39 CEST

Original text of this message