RE: View based on a procedure

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_at_logicacmg.com> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: 19 February 2007 14:28
To: mark_ai_at_yahoo.com
Cc: oracle-l_at_freelists.org
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

--
http://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.
--
http://www.freelists.org/webpage/oracle-l