REF CURSOR returning a PL/SQL Table.

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 4 Feb 2002 18:36:06 -0600
Message-ID: <ug04ga99j.fsf@rcn.com>

Oracle Version 8.1.7.

Between the stars is the section of code I'm working on.

---

PROCEDURE GetAvailAllocForSellingLine ( cvar IN OUT soft_cvar,

					in_src_type_pack_ids IN VARCHAR2,
					element_dlm in char,
					group_dlm in char,
					element_cnt in number
					)

AS

    query VARCHAR2(2000);
    nbr_elm_cnt number;
    e_nbr_elm_cnt EXCEPTION;
    l_data groupSize3Array := groupSize3Array(); BEGIN   nbr_elm_cnt := parse_parms.validateElementGroupNumber(in_src_type_pack_ids,

						element_dlm,
						group_dlm,
						element_cnt);

  l_data := parse_parms.parseGroupSize3(in_src_type_pack_ids,element_dlm,group_dlm,nbr_elm_cnt);

  insert into groupSize3ArrayTbl select * from TABLE ( cast (l_data as groupSize3Array ));

  query := 'select * from groupSize3ArrayTbl';   

  OPEN cvar FOR query;

  delete groupSize3ArrayTbl;

---

parse_parms is a package
validateElementGroupNumber is a function which returns a number. parseGroupSize3 is a PL/SQL function returning a PL/SQL table. cvar is a ref cursor which is returned to the client.

I would rather replace the following lines (Which is doing what I need but I'm not to sure of performance or any bugs that might be introduced)

  insert into groupSize3ArrayTbl
  select * from TABLE ( cast (l_data as groupSize3Array ));

  query := 'select * from groupSize3ArrayTbl';   

  OPEN cvar FOR query;

  delete groupSize3ArrayTbl;

with:

  query := 'select * from TABLE ( cast (l_data as groupSize3Array ))';

  OPEN cvar FOR query;

But I can't seem to get the ref cursor to point to a PL/SQL table. Is this doable and what might I be missing.

Thanks.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Mon Feb 04 2002 - 18:36:06 CST