Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4498
  • Last Modified:

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

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
Jay Redd
Asked:
Jay Redd
  • 9
  • 6
  • 5
1 Solution
 
sdstuberCommented:
specify the schema with the table


insert into schema_name.table_name (x,y,z) values (1,2,3);
0
 
Jay ReddAuthor Commented:
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
 
sdstuberCommented:
>>>  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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
sdstuberCommented:
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
 
awking00Commented:
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
 
sdstuberCommented:
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
 
awking00Commented:
>>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
 
Jay ReddAuthor Commented:
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
 
awking00Commented:
What did the query on all_objects produce?
0
 
awking00Commented:
They can not have the same name in the same schema.
0
 
sdstuberCommented:
>> 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
 
Jay ReddAuthor Commented:
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
 
sdstuberCommented:

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
 
awking00Commented:
>>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
 
sdstuberCommented:
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
 
Jay ReddAuthor Commented:
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
 
sdstuberCommented:
>>> 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
 
sdstuberCommented:
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
 
Jay ReddAuthor Commented:
thanks!
0
 
Jay ReddAuthor Commented:
I was able to just filter it out for my purposes. Thanks for verifying how all this works for me!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now