| 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: a couple q's on procedures / functions
A copy of this was sent to Dan Hess <dah23_at_cornell.edu>
(if that email address didn't require changing)
On Fri, 11 Jun 1999 11:16:33 -0400, you wrote:
>Forgive me, I'm a newbie to oracle, having tried it for the first time
>just a couple of days ago.
>
>My questions are:
>
>1 - Is it possible to create a view inside a procedure or function? I
>don't need the view to persist outside the function, just to remain long
>enough for me to change and query it. I haven't been able to do this so
>far.
>
yes but you don't want to.
you can create the view with dbms_sql in v8.0 and below. In 8.1 and up you can just:
..
execute immediate 'create view foo as ...';
why don't you want to? because you would have to dynamically reference this view all over the place in the procedure. If the object doesn't exist when the procedure is compiled, all references to the object from that procedure must be dynamic....
what are you trying to do?
>2 - Can someone show me an example of local variables in procedures and
>functions? I tried sticking the declare statement in several places
>without luck.
>
create or replace function foo return number as
x number;
begin
x := 5;
declare
y number;
begin
y := x+5;
x := y*2;
the initial declare of X is the 'normal' way but you can stick a DECLARE BEGIN/END block almost anywhere to start a new scope...
>Thanks for your help!
>
>- Dan
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--
 |
 |