Re: Q: How to create a virtual table/view?

From: J. Wegener NOSPAM <xjw_at_xdde.xdk>
Date: Wed, 16 Jun 1999 14:12:47 +0200
Message-ID: <7k849p$dj3$1@news101.telia.com>

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 DeviceRecord
as object
(
 id number
,description varchar2(200)
,status number
,update_timestamp date

);

create or replace
type DeviceTable
as table
of DeviceRecord;

REM *************************************

REM Function
create or replace function GetDeviceTable return DeviceTable as
 l_x DeviceTable := DeviceTable

    DeviceRecord

     (
     1
     ,'Concentrator'
     ,1
     ,'1-MAY-99'
     )
    ,DeviceRecord
     (
     2
     ,'Diffusor'
     ,2
     ,'1-JUN-99'
     )

   );
begin
 return l_x;
end;

REM *************************************

REM View
REM Not good...
create or replace
view Devices
as
select
 a.id id
,a.description description
,a.status status
,a.update_timestamp update_timestamp

from THE ( select cast( GetDeviceTable() as DeviceTable ) from dual ) a;

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