Re: Insert rows from a materialized view? Is it possible?

From: <nkunkov_at_escholar.com>
Date: 15 Jun 2006 10:15:48 -0700
Message-ID: <1150391748.439376.295090@c74g2000cwc.googlegroups.com>

DA Morgan wrote:
> nkunkov_at_escholar.com wrote:
> > Hi,
> > Is it possible to do an insert from a materialized view?
> > I'd like to call a function from the view to do the insert.
> > Will this work?
> > Do I need to use execute immediate?
> > Your help is greatly appreciated.
> > Thank you.
>
> You need to:
>
> 1. Better explain what you are trying to do.
> 2. State the Oracle version
> 3. Which type of Materialized View?
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Daniel, thanks for trying to help me.
I'm not at work right now so can't provide the exact code but I will try to give you some pseudo code. Please forgive syntax errors, this is just a skeleton of what I'm doing. Also, just wanted to say I'm not an Oracle guru, so most of this I have not done before.

I have to create a materialized view which will call a function for one or two of it's columns.
Here is a function that encrypts input value and returns it converted to a character but also does a table insert:

function getValue (varchar2 input) return varchar2 output varhcar2(200);
sqlCmd varchar2(1000);
is
output := DBMS_CRYPTO.encrypt(input,key......); newSequenceValue := get new sequence number from a sequence; --if sequence value doesn't exist in table B do the following insert sqlCmd := 'insert into my table values(output, newSequenceValue)' ; execute immediate sqlCmd;
return newSequenceValue;

---

Note, I didn't put commit and it works. I guess I have set autocommit on. Am I right? Do I need to put commit as part of the execute immediate statement?
should I add execute immediate 'commit'; after I did the first execute?

---

Now the view.....
I created a materialized view from table A. For one of the columns of table A I want to use the getValue function and return the output of the function instead of the real column. The function does the insert (if needed) into some other table (not table A) and returns some output that needs to be shown. This is my requirement, I know its strange but this what has to be done.

create materialized view

..................

............
as select column1, column2, getValue(column3) as column3 from table A

....................
....................

Note the function call here.
This seems to work. But I haven't tried to refresh the view, I only created it. Not sure if it will work on refresh. Besides, first I had a regular insert instead of execute immediate. That gave me an error when I tried creating a view. Something like can't commit from view. So I put execute immediate and that seems to work. But I am not sure this is something I can put on production. Will this actually work or I'm overlooking some major problems? Will it work on refresh? Do I need execute immediate or there is another way to insert from view using function? Should there be restriction on how to create the view? I think I can't use query rewrite. Please let me know what you think.
Your help is greatly appreciated.
Thanks
NK Received on Thu Jun 15 2006 - 12:15:48 CDT