Re: PL/SQL Table as Result of stored Function

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 13 Sep 1999 14:07:40 +0200
Message-ID: <7ripfn$4u4$1@oceanite.cybercable.fr>

You cannot use a function that returns a PL/SQL table in a select statement. Here's the doc:

To be callable from SQL statements, a stored function must meet several requirements, one of which is that its arguments have SQL datatypes such as CHAR, DATE, or NUMBER. None of the arguments can have non-SQL types such as BOOLEAN, TABLE, or RECORD.

But you can do something like this:

v734>select * from t;

      CODE PRICE_TYPE COST
---------- ---------- ----------

         1 t10                10
         2 t20                20
        10 t100              100
        10 t101              200

4 rows selected.

v734>declare
  2
  3 type PRICE_RecType IS RECORD (
  4 price_type varchar2(10) := '',
  5 cost number := 0);
  6
  7 TYPE PRICE_TabTyp IS TABLE OF PRICE_RecType   8 INDEX BY BINARY_INTEGER;
  9
 10 price_tab price_tabtyp;
 11
 12 function GetPriceInfo(code in number) RETURN  13 PRICE_TabTyp IS
 14 PRICE_tab PRICE_TabTyp;
 15 cursor c (p_code number) is
 16 select price_type, cost from t where code = p_code;  17 i integer := 0;
 18 price_type t.price_type%type;
 19 cost t.cost%type;
 20 BEGIN
 21
 22 open c (code);
 23 loop

 24       i := i+1;
 25       fetch c into price_type, cost;
 26       exit when c%notfound;
 27       price_tab(i).price_type := price_type;
 28       price_tab(i).cost := cost;

 29 end loop;
 30
 31
 32 RETURN PRICE_tab;
 33
 34 END;
 35
 36 begin
 37
 38 dbms_output.enable (1000000);
 39
 40 price_tab := getpriceinfo(10);
 41

 42     for i in 1..price_tab.count loop
 43        dbms_output.put_line('Type: '||price_tab(i).price_type||
 44                             ', cost: '||price_tab(i).cost);
 45     end loop;

 46
 47 end;
 48 /
Type: t100, cost: 100
Type: t101, cost: 200

PL/SQL procedure successfully completed.

or like this

v734>create or replace package my_pkg is   2
  3 type PRICE_RecType IS RECORD (

  4     price_type varchar2(10) := '',
  5     cost number := 0
  6     );

  7
  8 TYPE PRICE_TabTyp IS TABLE OF PRICE_RecType   9 INDEX BY BINARY_INTEGER;
 10
 11 function GetPriceInfo(code in number) RETURN PRICE_TabTyp;  12 pragma restrict_references(GetPriceInfo, WNDS, RNPS, WNPS);  13
 14 end;
 15 /

Package created.

v734>create or replace package body my_pkg is   2
  3 function GetPriceInfo(code in number) RETURN PRICE_TabTyp   4 is
  5 PRICE_tab PRICE_TabTyp;
  6 cursor c (p_code number) is
  7 select price_type, cost from t where code = p_code;   8 i integer := 0;
  9 price_type t.price_type%type;
 10 cost t.cost%type;
 11 BEGIN
 12 open c (code);
 13 loop

 14       i := i+1;
 15       fetch c into price_type, cost;
 16       exit when c%notfound;
 17       price_tab(i).price_type := price_type;
 18       price_tab(i).cost := cost;

 19 end loop;
 20 RETURN PRICE_tab;
 21 end;
 22
 23 end;
 24 /

Package body created.

v734>declare

  2     price_tab my_pkg.price_tabtyp;
  3     cursor c is select distinct code from t;

  4 begin
  5
  6 dbms_output.enable (1000000);
  7

  8     for rec in c loop
  9        price_tab := my_pkg.getpriceinfo(rec.code);
 10        dbms_output.put_line ('Code: '||rec.code);
 11        for i in 1..price_tab.count loop
 12           dbms_output.put_line('-   Type: '||price_tab(i).price_type||
 13                                ', cost: '||price_tab(i).cost);
 14        end loop;
 15     end loop;

 16
 17 end;
 18 /
Code: 1
- Type: t10, cost: 10
Code: 2
- Type: t20, cost: 20
Code: 10
- Type: t100, cost: 100
- Type: t101, cost: 200

PL/SQL procedure successfully completed.

Hope this will help.

jeremy twiggs a écrit dans le message
<01befdce$319521e0$0b0410ac_at_jeremy.hotelscene.co.uk>...
>Help, can anybody point me in the right direction?
>
>Can I write a SQL statement that calls a stored PL/SQL function
>that uses a PL/SQL table for its return parameter?
>
>i.e:
>
>TYPE PRICE_RecType IS RECORD (
> price_type varchar2(10) := '',
> cost number(5,5) := 0);
>
>TYPE PRICE_TabTyp IS TABLE OF PRICE_RecType
> INDEX BY BINARY_INTEGER;
>
>CREATE or REPLACE function GetPriceInfo(code in number(6)) RETURN
>PRICE_TabType IS
> PRICE_tab PRICE_TabTyp;
>BEGIN
>
> blah, blah, blah...
>
> RETURN PRICE_tab;
>
>END;
>
>then use it like
>
>SELECT
> a.code,
> GetPriceInfo (a.code)
>FROM
> a_table a
>
>such that it will return the columns
>code, price_type and cost.
>
>
>
Received on Mon Sep 13 1999 - 07:07:40 CDT