Re: Creating view

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 24 Nov 1999 08:07:11 -0500
Message-ID: <1mon3s8kms4el99bc8s1imhousab9rf2u4@4ax.com>

A copy of this was sent to Pornpira Vachareeyanukul <pornpira_at_beta.tricity.wsu.edu>
(if that email address didn't require changing) On Tue, 23 Nov 1999 17:41:02 -0800, you wrote:

>Hi again,
>
>Thanks for the response,
>
>I guess now I want to know how to go about doing this.
>I've tried create view test as
>pl/sql stuff here because I want to do loop to process the data
>
>But when I ran it, it gave the error that it expected "select" after
>"create view test as".
>

In Oracle8, you can do a select but not a view. In Oracle8i, you can create the view as well. Here is an example:

tkyte_at_8i> create or replace type myScalarType as object   2 ( x int, y date, z varchar2(30) )
  3 /

Type created.

tkyte_at_8i>
tkyte_at_8i> create or replace type myTableType as table of myScalarType;   2 /

Type created.

tkyte_at_8i> 
tkyte_at_8i> REM now, we want to "select * from PLSQL_FUNCTION()" not from a table:
tkyte_at_8i> 
tkyte_at_8i> create or replace function getMyTableType return myTableType
  2  as
  3          l_x     myTableType := myTableType();
  4  begin
  5          for x in ( select user_id, created, username from all_users )
  6          loop
  7                  exit when l_x.count > 10;
  8                  l_x.extend;
  9                  l_x(l_x.count) := myScalarType( x.user_id, x.created,
initcap( x.username ) );
 10          end loop;
 11          return l_x;

 12 end;
 13 /

Function created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> create or replace view myView ( userid, creation_date, uname )

  2 as
  3 select x, y, z
  4 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   5 /

View created.

tkyte_at_8i> select * from myview;

    USERID CREATION_ UNAME

---------- --------- ------------------------------
         0 20-APR-99 Sys
         5 20-APR-99 System
        11 20-APR-99 Outln
        18 20-APR-99 Dbsnmp
        20 20-APR-99 Tracesvr
        21 20-APR-99 Ctxsys
        35 26-APR-99 Oem
        23 20-APR-99 Ordsys
        24 20-APR-99 Ordplugins
        25 20-APR-99 Mdsys
        32 20-APR-99 Aurora$Orb$Unauthenticated

11 rows selected.

tkyte_at_8i>

In Oracle8, you would have to

  3 select x, y, z
  4 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   5 /

without the create view part else you'll get:

tkyte_at_8.0> create or replace view myView ( userid, creation_date, uname )   2 as
  3 select x, y, z
  4 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   5 /
  from THE ( select cast( getMyTableType() as mytableType ) from dual ) a

       *
ERROR at line 4:
ORA-22906: cannot perform DML on expression or on nested table view column

>Thank you,
>Mimi
>
>On Tue, 23 Nov 1999 lily99_at_my-deja.com wrote:
>
>> Yes, you can.
>>
>> In article <Pine.OSF.4.10.9911221709260.27211-
>> 100000_at_beta.tricity.wsu.edu>,
>> Pornpira Vachareeyanukul <pornpira_at_beta.tricity.wsu.edu> wrote:
>> > Hi,
>> > I'm trying to creat a view from multiple tables. But before creating
>> one,
>> > I'd like to do some data processing and use the result creating the
>> view.
>> > Is it possible data processing before creating the view?
>> > Thank you in advance,
>> > Mimi
>> >
>> >
>>
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>>
>>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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 Nov 24 1999 - 07:07:11 CST