Re: Coding Question about user-defined types as parameters

On Apr 21, 12:46 am, "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?

You haven't said what you're doing so it's a bit hard to tell what might be wrong about it. What you've described works fine:

CREATE OR REPLACE PACKAGE testpkg
AS

    CURSOR cr_cursor
    IS

        SELECT column_1, column_2, column_3
        FROM   base_table;

    PROCEDURE test( p_parameter IN cr_cursor%ROWTYPE ); END testpkg; Received on Sun Apr 22 2007 - 02:24:26 CDT