Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Q: How to create a virtual table/view?
A copy of this was sent to "J. Wegener NOSPAM" <xjw_at_xdde.xdk>
(if that email address didn't require changing)
On Tue, 15 Jun 1999 13:33:34 +0200, you wrote:
>Hi,
>
>I want to be able to create a virtual table/view similar to that of the
>Oracle dynamic performance views. Does anybody know a way of doing so?
>
>I am working on a system that utilizes a lot of hardware devices to measure
>such things as fluid temperatures and throughput. It is obviously possible
>to use the ORA_FFI package and access the output from the devices by calling
>a package or stored procedure, but having the output from the hardware
>devices presented as the rows and columns of a table or view strikes me as
>an elegant and convenient solution.
>
>Any comment is valued, thanks in advance.
>
>Cheers,
>Johan
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--