Re: Procedures-Views mix?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 25 May 1999 12:58:01 GMT
Message-ID: <374d9e2f.89035916@newshost.us.oracle.com>

A copy of this was sent to ayurchen_at_aug.edu (if that email address didn't require changing) On Tue, 25 May 1999 10:26:15 GMT, you wrote:

>In Oracle8 (with some restrictions
> in Oracle7) you can use functions
> in a view.
>
>CREATE OR REPLACE FUNCTION say_hello_to(thename VARCHAR2)
> RETURN VARCHAR2
>AS
>BEGIN
> RETURN 'Hello ' || thename;
>END;
>/
>
>Function created.
>
>CREATE VIEW hello_view
>AS
> SELECT say_hello_to(username) hello from all_users
> /
>
>View created.
>
>SELECT * FROM HELLO_VIEW;
>
>HELLO
>-----------------------------------------------------
>Hello SYS
>Hello SYSTEM
>Hello OUTLN
>Hello DBSNMP
>Hello AURORA$ORB$UNAUTHENTICATED
>Hello SCOTT
>Hello DEMO
>Hello ORDSYS
>Hello ORDPLUGINS
>Hello MDSYS
>
>10 rows selected.
>
>
>Regards
>Dante
>
>In article <3745B724.E1C415FF_at_aug.edu>,
> Alex Yurchenko <ayurchen_at_aug.edu> wrote:
>> Hi everyone
>>
>> I'd like to create a "view with parameters", or basically I want a
>> procedure which returns records. Is there is any way to do that.
>Thanks.
>>

In Oracle8.0 and up, yes:

SQL> REM instead of putting a type in a spec, do this:
SQL> 
SQL> create or replace type myScalarType as object
  2  (   x number,
  3      y number )

  4 /

Type created.

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

Type created.

SQL> 
SQL> REM now, we want to "select * from PLSQL_FUNCTION()" not from a table:
SQL> 
SQL> create or replace function getMyTableType return myTableType
  2  as
  3      l_x myTableType := myTableType( myScalarType(1,2),
  4                                      myScalarType(3,4),
  5                                      myScalarType(5,6),
  6                                      myScalarType(7,8) );
  7  begin
  8      return l_x;

  9 end;
 10 /

Function created.

SQL> 
SQL> REM here we go... selecting from it:
SQL> 
SQL> select *

  2 from THE ( select cast( getMyTableType() as mytableType ) from dual )   3 /

         X Y
---------- ----------

         1          2
         3          4
         5          6
         7          8






>> --


>>


>> home e-mail: snake76_at_mindspring.com


>> work e-mail: ayurchen_at_aug.edu


>> home page:   http://www.mindspring.com/~snake76


>>


>> ********************************************************


>> Religion is the opium of the people.


>>  C. Marx


>> ********************************************************


>> Friends will be friends


>>  "Queen"


>> ********************************************************


>> Knowing what


>> thou knowest not


>> is in a sense


>> omniscience


>>  P. Hein


>> ********************************************************


>> Teaching should be such that what is offered is perceived


>> as a valuable gift and not as a hard duty.


>>  A. Einstein


>> ********************************************************

>>
>>
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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 Tue May 25 1999 - 07:58:01 CDT