AndrewJ wrote:
> 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
You snipped any code that does any real work.
If you want further help ... post the code.
--
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
Received on Wed Apr 11 2007 - 16:03:23 CDT
