>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
Upgrade to something supported.
Read doc 112680.1 on Metaclunk.
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
-----------------------------------------------Reply-----------------------------------------------
On Sun, 13 May 2007 06:51:21 -0700, Barry Bulsara wrote:
> CREATE OR REPLACE TYPE typTest AS TABLE OF test1%ROWTYPE;
This will not work. You first have to define type to mimic the
rowtype and then to create type with table of that.
--
http://www.mladen-gogala.com
-----------------------------------------------Reply-----------------------------------------------
On May 13, 2:51 pm, Barry Bulsara <bbulsar
@hotmail.com> wrote:
> 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
CREATE TYPE is documented in the SQL reference:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sta... There is no '%ROWTYPE' option. In fact the '%' symbol does not even
appear on the page, which might have been a clue when you were
checking the syntax before posting ;)
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
