| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Views in Oracle-8
A copy of this was sent to Kazimierz Subieta <subieta_at_ipipan.waw.pl>
(if that email address didn't require changing)
On Mon, 26 Jul 1999 11:24:23 +0200, you wrote:
>Hi all!
>
>I need some information concerning view capabilities in Oracle-8.
>Questions to true Oracle-8 gurus (I am familiar with Oracle-7):
>
>1. Is it possible to use method invocations in view definitions?
> Which SQL clause (select, from, where, group by, order by, etc.)
> can involve method invocations?
Yes -- given that the method does not write to the database in 8.0 (it can using autonomous transactions in 8.1).
You can use it in a select, where, group by, order by etc.
>2. Does Oracle-8 support stored methods, i.e. method stored
> in a database (on the same principle as stored procedures)?
Yes.
>3. Is it possible to define recursive views (given a relation
> parents-children, define the view "ancestor")?
do you mean hierarchical? if so, yes, there is a connect by clause to let you build trees from data in the database. For example:
SQL> select lpad( ' ', level*2 ) || ename
2 from emp
3 connect by prior empno = mgr
4 start with mgr is null
5 /
LPAD('',LEVEL*2)||ENAME
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
14 rows selected.
EMP is a table with a MGR column and an EMPNO column. We can build the org chart out of the database using connect by and start with as above. this shows who works for who.
>4. Is it possible to define views with parameters?
> If so, which is the method of transmission: call-by-value,
> call-by-name, other? Is it possible to pass an SQL query
> as a parameter?
>
You can parameterize a view. It would look like this in 8.0 and before:
SQL> create or replace package parms
2 as
3 procedure set_parm( p_val in varchar2 ); 4 function get_parm return varchar2; 5 pragma restrict_references( get_parm, wnds, rnds, wnps );6 end;
Package created.
SQL>
SQL> create or replace package body parms
2 as
3
3 g_parm varchar2(255);
4
4
4 procedure set_parm( p_val in varchar2 ) 5 is 6 begin 7 g_parm := p_val; 8 end; 9 9 function get_parm return varchar2 10 is 11 begin 12 return g_parm; 13 end;
Package body created.
SQL>
SQL> create or replace view emp_v
2 as
3 select ename, empno from emp
4 where ename = ( select parms.get_parm from dual )
5 /
View created.
SQL>
SQL> exec parms.set_parm( 'KING' )
PL/SQL procedure successfully completed.
SQL>
SQL> select * from emp_v;
ENAME EMPNO
---------- ----------
KING 7839
You can also use predicates on a view which is another way of 'passing'
parameters to views -- eg:
select * from emp_v where <any condition you want>...
In 8.0 and before you cannot dynamically execute SQL in a SELECT statement (you cannot pass a query). In Oracle8i, release 8.1, you can dynamically execute SQL in a view.
>If the answer to the first question is positive, I have several other
>questions:
>
>5. Which language can be used to write methods that are to be
> invoked in views: PL/SQL, SQL3, Java, C++, other?
PL/SQL
C
Java
>6. Can the programmer use in view definitions methods with side
> effects, i.e. methods working with own local variables, methods
> changing the database state, etc.?
In 8.0, you can change variable states but not database state.
In 8.1, yes you can with autonomous transactions. If you are interested in reading about autonomous transactions, see the link in my .sig below.
>7. Does the view mechanism support polymorphism (dynamic binding)?
no.
>8. Are queries involving views (having method invocations) optimized?
> Which methods are used (query modification, caching results of
> method invocations, others)?
In 8.0, minimally. In 8i, you can do alot more (influencing the optimizer, writing your own index structures, extending the analyze command and so on)
>9. Are views having method invocations updateable? Are view updating
> rules the same as in Oracle-7?
All views are potentially modifiable view INSTEAD-OF triggers (if we cannot figure out how to update the view, you can help us out with the code to do it).
>10. Does the view mechanism support the so-called "check option"?
> Does it work for updates of views having method invocations?
>
Yes.
>I also welcome answers to some of the above questions.
>Thank you in advance,
>Regards,
>Kaz
--
See http://govt.us.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 Mon Jul 26 1999 - 07:51:00 CDT
 |
 |