Re: function return recordset definition in a package

nospamdrew_at_gmail.com wrote:
> I 'adopted' this code and have figured out 90% of what the package is
> doing. However, the code below reflects the remaining 10% I just
> can't get sorted out and understand how it determines the field names
> and sizes on the function return.
>
> Background
> A package in this package returns a DATE field to out_rec, but out_rec
> has that field defined as varchar2(7). This causes a procedure to
> fail and no results table is created. I get a "field too small"
> message.
>
>
> Problem
> How do I see the definition (field name and format) for the "RETURN
> t_TabScore" on the function definition... and/also/or, how about the
> definition for out_rec (or t_RecScore) from within f_score? I need to
> change that definition to a date-friendly format.
>
>
> The call to f_score looks like this:
> CREATE TABLE drew_tbl001 AS SELECT /*+ PARALLEL(q,16) */ * FROM
> TABLE(drew_pkg.f_score(CURSOR( .....<blah blah blah>
>
>
> Notes
> 1) The following code lists all occurrences and references of
> t_TabScore
> 2) The following code lists all occurrences and reference of
> t_RecScore
> 3) I get no errors during package compilation
> 4) The package runs successfully if I skip loading the 'date field'
> in f_score
>
>
>
> alter session set plsql_warnings='enable:all,warning:off'
>
>
> create or replace package drew_pkg ....
>
> TYPE t_Refcur IS REF CURSOR RETURN t_RecMCD ;
> TYPE t_Cursor IS REF CURSOR ;
>
> FUNCTION f_score ( pMCD t_refcur )
> RETURN t_TabScore
> PARALLEL_ENABLE (PARTITION pMCD BY HASH (individual_key))
> PIPELINED ;
>
>
> <other functions and procedures not listed here. Nothing >
> <references the pl/sql variables/structures in question >
>
> end;
>
>
> create or replace PACKAGE BODY drew_pkg
> IS
>
> FUNCTION f_score ( pMCD t_refcur )
> RETURN t_TabScore
>
> PARALLEL_ENABLE (PARTITION pMCD BY HASH (individual_key))
> PIPELINED
> IS
>
> --<<right here>> ....how do I see the field name and format for
> out_rec??
> out_rec t_RecScore;
> in_rec pMCD%ROWTYPE;
>
>
> BEGIN
> LOOP
> FETCH pMCD INTO in_rec;
> EXIT WHEN pMCD%NOTFOUND;
>
> out_rec := t_RecScore(
> NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
> ,NULL );
>
> <snip. code cut out >
>
> END LOOP ;
>
> CLOSE pMcd ;
> return
> end;
>
>
> <other functions and procedures not listed. Nothing >
> <references the pl/sql variables in question >
>
>
> end ;
>
>
>
> drew

Your code snippet initializes out_rec.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org