Re: Oracle pipelined table function table row type query

Barry Bulsara schrieb:
> Hi all, 10.1EE on W2K. I am attempting to write a pipelined table
> function that returns a type typTest. I want typTest to be a row type
> of an existing table, not a new object definition, but I keep getting
> errors. Does anyone have any suggestions.
>
> Thank you
> Barry
>
> CREATE OR REPLACE TYPE typTest AS TABLE OF test1%ROWTYPE;
> /
>
> Errors for TYPE TYPTEST:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 0/0 PL/SQL: Compilation unit analysis terminated
> 1/26 PLS-00329: schema-level type has illegal reference to
> SCOTT.TEST1
>

You can't do it like this.
You have to create an object type with all attributes like your test1 table columns and after that you can create type as table of your object type.
In this case however, your nested table type will not be like a row of your table, but like an array of such rows, so i am not sure, you want it really... Especially in context of pipelined tables, where usually one row ( as opposite to array of rows ) is piped.

Best regards

Maxim Received on Sun May 13 2007 - 10:13:02 CDT