Re: REF CURSOR returning a PL/SQL Table.

On 5 Feb 2002, billiauk_at_yahoo.co.uk wrote:
> Galen
>
> When you use the TABLE expression to CAST a result set, you
> need to use an alias:-
>
>
> Instead of:-
>
> query := 'select * from TABLE ( cast (l_data as
> groupSize3Array
> ))';
>
> Try:-
> query := 'select t.* from TABLE ( cast (l_data as
> groupSize3Array
> )) t';

Adrian,

I tried what you said and I still didn't get output to my screen, whereas I was able to get it by inserting into a table, although this time I used a global temporary table. (I switched to using a temporary table because I figured I might need to go this way and that is a much better solution and probably about the same in performance.)

HERE IS THE TEMPORARY TABLE CREATE STATEMENT:     create global temporary table groupSize3ArrayTbl1(v1 number, v2 number, v3 number)     on commit preserve rows;

HERE ARE THE ORACLE OBJECTS I AM USING:     create or replace type groupSize3Scalar as object     ( variable1 number, variable2 number, variable3 number)     /

    create or replace TYPE groupSize3Array is table of groupSize3Scalar     /

HERE IS THE SNIPPET OF CODE THAT RETURNS OUTPUT TO THE SCREEN USING THIS TEMPORARY TABLE.     l_data groupSize3Array := groupSize3Array();

        ...

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

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

    query := 'select * from groupSize3ArrayTbl1';

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

    OPEN cvar FOR query;

RESULTS TO THE SCREEN:         V1 V2 V3
---------- ---------- ----------

       101	   20	     102
	30	  103	      40
       104	   20	      30

NOW, WHEN I TRY WHAT YOU SAID (basically uncomment and comment some lines), I GET NOTHING TO THE SCREEN.

    l_data groupSize3Array := groupSize3Array();

        ...

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

• insert into groupSize3ArrayTbl1 select * from TABLE ( cast (l_data as groupSize3Array ));
• query := 'select * from groupSize3ArrayTbl1'; query := 'select t.* from TABLE ( cast (l_data as groupSize3Array )) t';

    OPEN cvar FOR query;

RESULTS TO THE SCREEN: no rows selected

An interesting note is that the query does work without the alias on insert and just to be sure, I tried the second way without the alias again

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

    OPEN cvar FOR query;

and of course, no dice. (Just grab'n for straws on that one)

> Also, the groupSize3Array MUST be created on the database and
> not be a PL/SQL declared type for the CAST expression to work.

This is the case from above.

I am pretty sure this can be done. What is the simple piece I'm missing, arghh!!

THANKS A BUNCH.

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