If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > insert into view based on UNION ALL query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
insert into view based on UNION ALL query

I am unable to insert into a VIEW which uses a UNION ALL query. At least I'm sort of unable to. If I use a direct sql stmt against the VIEW it works. If I try to insert via TOAD or Oracle Forms, I get stopped.

When I remove the UNION ALL and the query below, leaving the same INSTEAD OF TRIGGER in place, there's no problem.

What could be causing this dichotomy between these environments? (Also, the ALL_UPDATABLE_COLUMNS says the view is not insertable, even though the direct SQL stmt still works).

Thanks,
Chuck
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
here's an example in 10gR1

Code:
SQL> create table dummy (f1 number);

Table created.

SQL> create view dummy_v  
2  as  
3  select f1 from dummy  
4  union all   
5  select f1 from dummy;

View created.

SQL> create trigger dummy_v_it  
2  instead of insert  
3  on dummy_v  
4  for each row  
5  begin  
6    insert into dummy values (:NEW.f1);  
7  end;  
8  /

Trigger created.

SQL> insert into dummy_v values (1);

1 row created.

SQL> select * from dummy_v;        
F1
----------         
1         
1

SQL> select * 
  2  from user_updatable_columns
  3  where table_name = 'DUMMY_V';

OWNER    TABLE_NAME   COLUMN_NAME  UPD INS DEL
-------- ------------ ------------ --- --- ---
FORBESC  DUMMY_V      F1            NO  NO  NO
-cf
Reply With Quote
  #3 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,986
On my Oracle database 10.2.0.1.0, Developer Suite 10g (9.0.4) and TOAD 8.0.0.47:

TOAD seems to be OK, at least while inserting records. Delete and update raise ORA-01732: data manipulation not legal on this view.

Forms can insert records if it is the first operation - I can insert as many records as I want. But, if I execute query and then try to delete or update fetched values or insert new ones, I receive FRM-40602: cannot insert into or update data in a view.

I can't explain why this happens, but - with some restrictions on Forms - I can perform insert through all mentioned tools.
Reply With Quote
  #4 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,412
When forms does a select, it loads the rowid of the row that it just read. If no rowid's are available (like on a union all view), it will NOT attempt to perform an update. This can be overridden by using the "KEY MODE" property on the database block. Below is from the forms help



By default, the ORACLE database uses unique ROWID values to identify each row. Non-ORACLE databases do not include the ROWID construct, but instead rely solely on unique primary key values to identify unique rows. If you are creating a form to run against a non-ORACLE data source, you must use primary keys, and set the Key Mode block property accordingly.

Value Description
Automatic (default) Specifies that Form Builder should use ROWID constructs to identify unique rows in the datasource but only if the datasource supports ROWID.

Non-Updateable Specifies that Form Builder should not include primary key columns in any UPDATE statements. Use this setting if your database does not allow primary key values to be updated.

Unique Instructs Form Builder to use ROWID constructs to identify unique rows in an ORACLE database.

Updateable Specifies that Form Builder should issue UPDATE statements that include primary key values. Use this setting if your database allows primary key columns to be updated and you intend for the application to update primary key values.
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
Cool beilstwh, that got us a step further. We're now allowed to fill in the fields with the new values for the INSERT, but when we click save we get a "FRM-40600: Record has already been inserted." error. It looks like it's due to the fact that the primary key shows up more than once. The same table is used on both sides of the UNION ALL query.

-cf

Last edited by chuck_forbes; 05-22-06 at 13:06.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 1,074
It turns out that we had the property ENFORCE PRIMARY KEY set to yes. Setting it to no allowed for INSERTS, but updates are still a problem. Forms tries to send a FOR UPDATE NOWAIT against the UNION ALL view which Oracle doesn't allow.

We're going to update the model with an XREF table which will allow us to elimate the UNION ALL in the view.

Thanks for your help,
Chuck
Reply With Quote
  #7 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,412
A primary key means just that a UNIQUE identifier for a particular row. Determine what columns would make up a unique identifer and set the primary key flag on the items in the block. This should allow your instead of trigger to work correctly.
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On