SOLVED (Was: PL/SQL - Returning resultset-like values from functions)

From: Carsten Alexander <acamat_at_web.de>
Date: Tue, 13 Aug 2002 13:43:24 -0000
Message-ID: <ajarfr$19tbu1$1@ID-86275.news.dfncis.de>

Hi Adrain,

I just played around with your sample code. It works the way I intended! :))). I only had a problem with the "mysterious" second BEGIN-END Block...

"Adrian Billington" <billiauk_at_yahoo.co.uk> wrote in message news:dee17a9f.0208120344.12eede07_at_posting.google.com...

> --
> -- Object type (unfortunately no %rowtype support)...
> --
>
> [SNIP: Your sample code]
>
> SELECT id, str
> FROM TABLE(CAST(myFunction() AS myArrayType));
>

Here's my code:

CREATE TYPE CPosition AS OBJECT(

 Longitude NUMBER(13,12),
 Latitude  NUMBER(13,12),
 Distance  NUMBER(11, 9)

);

CREATE TYPE tPosition AS TABLE OF CPosition;

CREATE OR REPLACE FUNCTION FPosition RETURN tPosition IS
BEGIN  DECLARE objPosition tPosition;

 BEGIN   objPosition := tPosition(

   CPosition( 0.125,-0.125,12.5),
   CPosition(-1.225, 1.125, 5.7),
   CPosition( 0.001,-7.258, 0.5));

  RETURN objPosition;

 END; END; So I can query:

---: SQL> SELECT * FROM TABLE(CAST(FPosition() AS tPosition));
---:
---:LONGITUDE  LATITUDE  DISTANCE
---:--------- --------- ---------
---:     .125     -.125      12.5
---:   -1.225     1.125       5.7
---:     .001    -7.258        .5

---: SQL> SELECT Longitude FROM TABLE(CAST(FPosition() AS TPosition));
---:
---: LONGITUDE
---: ---------
---:      .125
---:    -1.225
---:      .001

Thank your so much for your help!

--
Kind regards,
Carsten

Received on Tue Aug 13 2002 - 08:43:24 CDT