REF CURSOR returning a PL/SQL Table.

From: <galenboyer_at_hotpop.com>
Date: 4 Feb 2002 17:19:09 -0600
Message-ID: <ur8o0kfcx.fsf@grossprofit.com>

Oracle Version 8.1.7.

Between the *******************************************************'s

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 do what I need)

  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 Boyer

Received on Mon Feb 04 2002 - 17:19:09 CST