Re: Coding Question about user-defined types as parameters

On Apr 20, 9:24 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Apr 20, 7:46 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
>
>
>
>
>
> > I have a situation where I have a cursor of a subset of the columns in a
> > table:
>
> > For example, if the base_table contains 10 total columns but only have 3 in
> > the cursor as
> > below:
>
> > CURSOR cr_cursor
> > IS
> > SELECT column_1,
> > column_2,
> > column_3
> > FROM base_table;
>
> > If I want to pass the cursor as a parameter to functions/procedures it seems
> > as if I have to
> > specifically create a TYPE with hardcoded values for the columns:
>
> > TYPE the_type IS RECORD
> > (
> > column_1 VARCHAR2(10),
> > column_2 VARCHAR2(10),
> > column_3 VARCHAR2(10)
> > );
>
> > Instead of being able to create a TYPE like:
>
> > TYPE the_type cr_cursor%ROWTYPE;
>
> > If I create a procedure/function something like:
>
> > PROCEDURE test( p_parameter IN the_type )
>
> > Only the first version with the hardcoded columns will successfully compile.
>
> > Can anybody shed any light on what I'm doing wrong?
>
> Did you look at using a ref cursor?- Hide quoted text -
>
> - Show quoted text -

I second the use of a REF CURSOR. See the PL/SQL User's Guide and Reference manual.

HTH -- Mark D Powell -- Received on Sat Apr 21 2007 - 09:03:06 CDT