Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Q: How to create a virtual table/view?
Hi,
Thanks for the reply.
I have tried to elaborate on your example, but I ran into the problem that I cannot get the view definition right. When I try to create the view shown below, Oracle reports the error "ORA-22906: cannot perform DML on expression or on nested table view column". Strangely, the select statement by itself runs without problems.
I have not worked very much with Oracle user defined types, so could you help me get the definitions shown below right?
Thanks,
Johan
REM *************************************REM Types
create or replace
type DeviceTable
as table
of DeviceRecord;
REM *************************************REM Function
DeviceRecord
( 1 ,'Concentrator' ,1 ,'1-MAY-99' ) ,DeviceRecord ( 2 ,'Diffusor' ,2 ,'1-JUN-99' )
REM *************************************REM View
Thomas Kyte skrev i meddelelsen <37677348.2671831_at_newshost.us.oracle.com>...
>you need oracle8.0 or up to do the following: > > > >create or replace type myTableType as table of number; >/ >REM now, we want to "select * from PLSQL_FUNCTION()" not from a table: > >create or replace function getMyTableType return myTableType >as > l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 ); >begin > return l_x; >end; >/ > > >REM here we go... selecting from it: > >select a.column_value val > from THE ( select cast( getMyTableType() as mytableType ) from dual ) a >/ > > > >you can create a more complex type to have columns, you can hide the select >cast..... in a view...Received on Wed Jun 16 1999 - 07:12:47 CDT