Solved

Oracle Table and Materialized View with Same Name - How to Update the Table

Posted on 2014-01-23
20
3,643 Views
Last Modified: 2014-01-23
Hi, probably another super easy one...

My client named a materialized view the same as his table. When I try to insert into the table, I get "ORA-01732: data manipulation operation not legal on this view" since it just assumes I mean the view.

How do I tell it insert into the table? Is there some way to do like INSERT INTO schema.alltables.tablename (..  or something? So it knows I mean the table and not the view?

I don't have the option of dropping or changing the view. It's the client's setup, as dumb as it may be. :(
0
Comment
Question by:Jay Redd
  • 9
  • 6
  • 5
20 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
specify the schema with the table


insert into schema_name.table_name (x,y,z) values (1,2,3);
0
 

Author Comment

by:Jay Redd
Comment Utility
I tried that earlier, same error.

 INSERT INTO schema_user.OWNER_DATA (ID,EXTE,PORT,FACID,ORG_ID)
 SELECT 207548,'P',1367113,9734303,67691401 from dual;
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>  it just assumes I mean the view.

either you're logged in as the mv owner or there is a synonym that points to the mv.  There are no "assumptions" in how names are resolved.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
what are the results of these 2 queries?

select  * from all_objects where object_name = 'OWNER_DATA';

select * from all_synonyms where synonym_name = 'OWNER_DATA';


also, with what user are logged in?
or more accurately, what is your current_schema resolving?

usually that will be the same as your user, but if you do an alter session you can change it.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
To determine schema that contains the table -
select owner, object_type from all_objects
where object_name = <NAME OF THE TABLE OR VIEW>
and then you can insert into owner.object_name ...
using the owner as the schema where the object_type is 'TABLE'
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
essentially, what we're getting at is...
what you have described and what you are seeing don't coincide.


You can't have the same table and mv in the same schema.

So, if you specify the table's owner along with the table name, then you are assured that your insert is operating on the table in that schema.

If you're not seeing that, then either you have hit a bug (unlikely) or your schema or your table name is not correct.

Or... your insert is working correctly but then a trigger fires causing some illegal operation.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>I tried that earlier, same error.
 INSERT INTO schema_user.OWNER_DATA (ID,EXTE,PORT,FACID,ORG_ID)<<
Then schema_user is the schema for the view and not the table.
0
 

Author Comment

by:Jay Redd
Comment Utility
When I look at the database in Sql Developer, I see under tables OWNER_DATA and under materialized views OWNER_DATA, there are no OWNER_DATA in my synonyms. I can open the table or the view. Are you trying to say they can't have the same name?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
What did the query on all_objects produce?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
They can not have the same name in the same schema.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>> Are you trying to say they can't have the same name?

yes, as noted in your previous question they can't have the same name.

HOWEVER....

a materialized view IS a table.  That's where the view query is "materialized"
So, in that sense I guess you say it's a "loophole" in the namespace

Are you getting the two confused?  Are you trying to update the mv's table directly?  Not all mv's are updateable
0
 

Author Comment

by:Jay Redd
Comment Utility
what are the results of these 2 queries?

select  * from all_objects where object_name = 'OWNER_DATA';

select * from all_synonyms where synonym_name = 'OWNER_DATA';

all_objects returns two rows, one with

OBJECT_TYPE = TABLE

and the other

OBJECT_TYPE= MATERIALIZED VIEW

Its all under the same user/schema.

nothing in synonyms.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility

OBJECT_TYPE = TABLE

and the other

OBJECT_TYPE= MATERIALIZED VIEW

Its all under the same user/schema.


then you're seeing what I'm describing above.  The MV and the Table are the SAME thing.


Here's an example...
SQL> create table testtable(n number);

Table created.

SQL> create materialized view testmv as select * from testtable;

Materialized view created.

SQL> select owner,object_name,object_type from all_objects where object_name = 'TESTMV';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SDS                            TESTMV                         TABLE
SDS                            TESTMV                         MATERIALIZED VIEW

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>all_objects returns two rows, one with
OBJECT_TYPE = TABLE
and the other
OBJECT_TYPE= MATERIALIZED VIEW<<
and what did they each show as OWNER?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
The  two objects do have distinct object id's because the TABLE part of an MV is just that, a TABLE with rows and columns,  the MV part contains the view query and refresh information.

They are managed in the data dictionary as two objects because each piece is responsibile for describing different aspects of the MV; but they are inseparable.
0
 

Author Comment

by:Jay Redd
Comment Utility
et_owner for both.

if they are the same thing, why should there be two rows? does this mean i just can't insert at all in this instance?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
Comment Utility
>>> why should there be two rows?

answered above

>>>  does this mean i just can't insert at all in this instance?

yes, the MV (and hence it's corresponding table) has not been defined as updateable.

To create a new row in the MV, you'd have to insert and/or update the table or tables that make up the defining query of the view.

So, in my example, you couldn't insert into testmv,  but you could insert to testtable which would then update testmv the next time it was refreshed.


SQL> create table testtable(n number);

Table created.

SQL> create materialized view testmv as select * from testtable;

Materialized view created.

SQL> select owner,object_name,object_type from all_objects where object_name = 'TESTMV';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SDS                            TESTMV                         TABLE
SDS                            TESTMV                         MATERIALIZED VIEW

SQL> select * from testmv;

no rows selected

SQL> insert into testmv(n) values(1);
insert into testmv(n) values(1)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> insert into testtable(n) values(1);

1 row created.

SQL> exec dbms_snapshot.refresh('TESTMV');

PL/SQL procedure successfully completed.

SQL> select * from testmv;

         N
----------
         1

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
To see MV/TABLE relations as well as updatable, look in all_snapshots

select owner,name,table_name,updatable from all_snapshots where name = 'OWNER_DATA';
0
 

Author Closing Comment

by:Jay Redd
Comment Utility
thanks!
0
 

Author Comment

by:Jay Redd
Comment Utility
I was able to just filter it out for my purposes. Thanks for verifying how all this works for me!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now