Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: function return recordset definition in a package

Re: function return recordset definition in a package

From: AndrewJ <dbasolutions_at_gmail.com>
Date: 11 Apr 2007 12:39:40 -0700
Message-ID: <1176320380.864753.188630@n76g2000hsh.googlegroups.com>


On Apr 11, 8:11 am, DA Morgan <damor..._at_psoug.org> wrote:
> nospamd..._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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Thanks, but I'm still do not see where the fieldnames and datatypes that comprise out_rec are established. I see no PL/SQL code that names the fields or establishes datatypes for out_rec (or the RETURN on the function).

All I see is an empty structure with 97 'spots' (I think there are that many NULL placeholders in that if block) that somehow end up with fieldnames and datatypes; and, end up giving me a real table on disk when it runs successfully.

e.g.:: How do I know the fieldname and datatype of the fifth element on row one of out_rec??

/s/
drew Received on Wed Apr 11 2007 - 14:39:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US