RE: View based on a procedure
- From: "M. Alroy Mascrenghe" <mark_ai@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 19 Feb 2007 21:30:39 -0800 (PST)
Tthanks Guys, for all your replies esp for reminding about the autonomous
transactions and table() I was able to solve the problem thru these
"Clarke, Andrew" <andrew.clarke@xxxxxxxxxxxxx> wrote: The problem lies in the
>> The procedure does a lot of heavy calculations writes to a table.
>> And the view should read from that table.
Doing DML in a function requires the use of the autonomous_transaction
pragma, which is fraught with problems. What should the query do if the
insert fails?
Cheers, APC
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: 19 February 2007 14:28
To: mark_ai@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: View based on a procedure
On 02/19/2007 02:06:26 AM, M. Alroy Mascrenghe wrote:
> Guys
> Is there a way to base a view/materialised view on a procedure?
Everytime the view is selected from the procedure must be run. The
procedure does a lot of heavy calculations writes to a table. And the
view should read from that table. I know DML in select wont work. Is
there a workaround, can use some other structure instead of a table in
the procedure maybe like (varrays)?
>
You can create a pipelined procedure that returns PL/SQL table and then
select * from table(proc),
in a very similar fashion to select * from table(dbms_xplan.display);
--
Mladen Gogala
http://www.mladen-gogala.com
--
//www.freelists.org/webpage/oracle-l
This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an intended
recipient then please promptly delete this e-mail and any attachment and all
copies and inform the sender. Thank you.
---------------------------------
Now that's room service! Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
Other related posts:
