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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 16 Jun 1999 13:24:39 GMT
Message-ID: <376aa558.2164011@newshost.us.oracle.com>

A copy of this was sent to "J. Wegener NOSPAM" <xjw_at_xdde.xdk> (if that email address didn't require changing) On Wed, 16 Jun 1999 14:12:47 +0200, you wrote:

>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
>

[snip]

looks like an Oracle8.0 Parsing issue. your example runs in 8i, release 8.1.5 without a hitch.

For now, the following will work for you:

create or replace
view Devices
as
select
a.id id

 ,a.description   description
 ,a.status    status
 ,a.update_timestamp  update_timestamp

from ( select * from THE ( select cast( GetDeviceTable() as DeviceTable ) from d ual ) ) 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...
>
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jun 16 1999 - 08:24:39 CDT