Results 1 to 7 of 7
  1. #1
    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

  2. #2
    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

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    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.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,434
    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.

  5. #5
    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 14:06.

  6. #6
    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

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,434
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •