2 Replies Latest reply: May 7, 2013 4:29 PM by KarenH RSS

    using view, on collection to update table w/ INSTEAD OF trigger - ORA-01031

    KarenH
      I have created a view LANDINGS_COLLECTION_VIEW, based on an apex collection called SPECIES_COLLECTION. When I select from LANDINGS_COLLECTION_VIEW it looks perfect. When I try and update the collection, I receive the following;
      ORA-01031 INSUFFICIENT PRIVLEDGES
      I have SELECT, INSERT, UPDATE, DELETE granted to the tables LANDINGS and LANDINGS_HMS. I also have select granted on LANDINGS_COLLECTION_VIEW.

      I was hoping to use the INSTEAD of trigger to udate the two underlying tables. Is that possible?

      thanks
      Karen

      the view and INSTEAD OF TRIGGERS on the view are as follows:
      -- Start of DDL Script for View SAFIS.LANDINGS_COLLECTION_VIEW
      -- Generated 07-May-2013 16:49:48 from SAFIS@SAFISD.world
      
      CREATE OR REPLACE VIEW landings_collection_view (
         seq_id,
         landing_seq,
         species_itis,
         grade_code,
         market_code,
         unit_measure,
         disposition_code,
         gear_code,
         reported_quantity,
         price,
         dollars,
         c013,
         c014,
         additional_measure_flag,
         addnl_reported_quantity,
         addnl_unit_quantity,
         fins_flag,
         finsattached,
         finsnotattached,
         finsunknown,
         fins_code,
         area_fished,
         sub_area_fished,
         local_area_code,
         explanation,
         nature_of_sale,
         hms_area_code,
         sale_price,
         hms_flag )
      AS
      SELECT
      seq_id,
      c003 landing_seq,
      c004 species_itis, 
      c005 grade_code,
      c006 market_code, 
      c007 unit_measure,
      c008 disposition_code,
      c009 gear_code,
      c010 reported_quantity,
      c011 price,
      c012 dollars,
      c013,
      c014 ,
      c017 additional_measure_flag,
      c018 addnl_reported_quantity,
      c019 addnl_unit_quantity,
      c020 fins_flag,
      c021 finsattached,
      c022 finsnotattached,
      c023 finsunknown,
      c024 fins_code,
      c025 area_fished, 
      c026 sub_area_fished,
      c027 local_area_code,
      c028 explanation,
      c029 nature_of_sale,
      c040 hms_area_code,
      c041 sale_price,
      c050 hms_flag
      from apex_collections
       where collection_name = 'SPECIES_COLLECTION'
      /
      
      -- Grants for View
      GRANT SELECT ON landings_collection_view TO public
      /
      
      -- Triggers for LANDINGS_COLLECTION_VIEW
      
      CREATE OR REPLACE TRIGGER landings_cv_io_upd_trg
       INSTEAD OF
        UPDATE
       ON landings_collection_view
      REFERENCING NEW AS NEW OLD AS OLD
      DECLARE
         xlanding_seq number := null; 
      BEGIN
      
      update landings set landing_seq = :new.landing_seq, 
                             --   dealer_rpt_id = :old.dealer_rpt_id, 
                                unit_measure = :new.unit_measure,
                                reported_quantity = :new.reported_quantity,   
                              --  landed_pounds = :new.landed_pounds, 
                                dollars = :new.dollars,
                                disposition_code= :new.disposition_code,  
                                grade_code = :new.grade_code, 
                                species_itis =  :new.species_itis, 
                                 market_code = :new.market_code,
                                price =  :new.price, 
                                area_fished = :new.area_fished, 
                                sub_area_fished = :new.sub_area_fished, 
                             --   lease_num = :new.lease_num,
                                gear_code = :new.gear_code, 
                          --      de = :new.de, 
                          --      ue = :new.ue, 
                          --      dc = :new.ue, 
                          --      uc = :new.uc, 
                                local_area_code =  :new.local_area_code 
                                where landing_seq = :new.landing_seq;                                                    
                                     
          
       /* check to see if the updated landing had a corresponding hms record */  
           select landing_seq into xlanding_seq 
           from   landings_hms 
           where  landing_seq = :new.landing_seq;
        
           if xlanding_seq is not null then
              update landings_hms  set --dealer_rpt_id = :new.dealer_rpt_id, 
                                       landing_seq = :new.landing_seq, 
                                       fins_attached = :new.fins_code, 
                                       explanation = :new.explanation,
                                      -- late_report = :new.late_report, 
                                    --   modified_data = :new.modified_data, 
                                       nature_of_sale = :new.nature_of_sale,
                                       hms_area_code = :new.hms_area_code, 
                                       sale_price = :new.sale_price  
                                 --      de = :new.de, 
                                 --      ue = :new.ue, 
                                 --      dc = :new.dc, 
                                 --      uc = :new.uc 
                                       where landing_seq = :new.landing_seq;                          
           -- else
           -- check if is an hms speciea, and insert into landings_hms
           end if;
           
              
                   
      exception when no_data_found then null;  when others then null;             
      end;
      
      
      
      -- End of DDL Script for Trigger SAFIS.LANDINGS_KEH_V_IO_TRG
      /
      
      
      -- End of DDL Script for View SAFIS.LANDINGS_COLLECTION_VIEW
      Edited by: KarenH on May 7, 2013 5:02 PM
        • 1. Re: using view, on collection to update table w/ INSTEAD OF trigger - ORA-01031
          Denes Kubicek
          Karen,

          You can't use instead of triggers on views based on a collection. That would be nice but it doesn't work. You seem to be fighting with a problem recently and trying different techniques to solve it. What is the actual problem?

          Denes Kubicek
          -------------------------------------------------------------------
          http://deneskubicek.blogspot.com/
          http://www.apress.com/9781430235125
          http://apex.oracle.com/pls/apex/f?p=31517:1
          http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
          -------------------------------------------------------------------
          • 2. Re: using view, on collection to update table w/ INSTEAD OF trigger - ORA-01031
            KarenH
            Hi Denes, thanks for getting back to me (again and again).

            We use collections throughout our application. They refer to columns such as c001, c002, etc...so have become hard to debug. We have also recently had to add new fields to the collections that in turn update additional tables. I am now in the process or reviewing all of our code, much of what was written in 2008 when we were just learning APEX. We are now hoping to simplify and clean things up.

            I had hoped to use a tablular form based on a view, and make the columns hidden/displayed based on the value in c050 (or HMS_FLAG in view). Basically, it feels like a tangled rat's nest at the moment...and I am daunted by the task of unraveling...and also slightly excited to try new things. We are using 4.2 and will be moving to template 25.

            So, for example, I was hoping to get replace the following tabular region:
            SELECT
            seq_id,
            apex_item.text(1,seq_id,'','','id="f01_'||seq_id,'','') "DeleteRow",
            
            apex_item.text_from_LOV(c004,'SPECIES')||'-'||apex_item.text_from_LOV(c005,'GRADE')||'-'||apex_item.text_from_LOV(c006,'MARKETCODE')||'-'||apex_item.text_from_LOV_query(c007,'select unit_of_measure d, unit_of_measure r from species_qc') unit,
            
            apex_item.select_list_from_LOV(6,c008,'DISPOSITIONS','onchange="getAllDisposition('||seq_id||')"','YES','0','  -- Select Favorite --  ','f06_'||seq_id,'') Disposition,
            
            apex_item.select_list_from_LOV(7,c009,'GEARS','style="background-color:#FBEC5D; "onFocus="checkGearPreviousFocus('||seq_id||');"onchange="getAllGears('||seq_id||')"','YES','3333','-- Select Favorite --','f07_'||seq_id,'') Gear,
            
            apex_item.text(8,TO_NUMBER(c010),5,null,'onchange="setTotal('||seq_id||')"','f08_'||seq_id,'') Quantity,
            
            apex_item.text(9,TO_NUMBER(c011),5,null,'onchange="getPriceBoundaries('||seq_id||')"','f09_'||seq_id,'') Price,
            
            apex_item.text(10, TO_NUMBER(c012),5,null, 'onchange="changePrice
            ('||seq_id||')" onKeyDown="selectDollarsFocus('||seq_id||',event);"','f10_'||seq_id,'') Dollars,
            
             decode(c013,'Y',apex_item.text(11, c014,30,null,'style="background-color:#FBEC5D;" onClick="onFocusAreaFished('||seq_id||');"','f11_'||seq_id,''),'N','N/A') Area_Fished,
            
            decode(c017,'Y',apex_item.text(12, c018,4,null,'style="background-color:#FBEC5D; "onBlur="setUnitQuantity('||seq_id||')"','f12_'||seq_id,''),'N','N/A') UNIT_QUANTITY,
            
            decode(c017,'Y',apex_item.text(13, 'CN',3,null,'readOnly=readOnly;','f13_'||seq_id,''),'N','N/A') UNIT_COUNT,
            
            decode(c050,'Y',apex_item.checkbox(14,'Y','id="f14_'||seq_id||'" style="background-color:#FBEC5D; " onClick="alterYes('||seq_id||');" onKeyPress="alterYes('||seq_id||');"',c021),'N','N/A') FinsAttached,
            
            decode(c050,'Y',apex_item.checkbox(15,'N','id="f15_'||seq_id||'" style="background-color:#FBEC5D;" onClick="alterNo('||seq_id||');" onKeyPress="alterNo('||seq_id||');"',c022),'N','N/A') FinsNotAttached,
            
            decode(c050,'Y',apex_item.checkbox(16,'U','id="f16_'||seq_id||'" style="background-color:#FBEC5D;" onClick="alterUnk('||seq_id||');" onKeyPress="alterUnk('||seq_id||');"',c023),'N','N/A') FinsUnknown,
            
            decode(c050,'Y',apex_item.textarea(17,c028,3,null,'onchange="setExplanation('||seq_id||')"','f17_'||seq_id,''),'N','N/A') Explanation,
            
            decode(c024,'N',apex_item.select_list_from_LOV(18,c029,'HMSNATURE','onchange="saveNature('||seq_id||')"','YES','A','-- Select Nature of Sale --','f18_'||seq_id,''),'U',apex_item.select_list_from_LOV(18,c029,'HMSNATURE','onchange="saveNature('||seq_id||')"','YES','A','-- Select Nature of Sale --','f18_'||seq_id,''),'Y','N/A') Nature_Of_Sale,
            
            decode(c020,'Y',
            apex_item.select_list_from_LOV(40,c040,'HMS_AREA_CODE',
            'style="background-color:#FBEC5D;
                  "onchange="saveHMSAREA('||seq_id||', this.value)"','f40_'||seq_id,''),
            'N','N/A') HMS_AREA_CODE,
            
            c020,c050,
            
            decode(c020,'Y',
            apex_item.text(41,TO_NUMBER(c041),5,null,'onchange="setSalePrice('||seq_id||',this.value)"','f41_'||seq_id,''),
            'N','N/A') Sale_Price
            
            from apex_collections
             where collection_name = 'SPECIES_COLLECTION' order by seq_id desc
            {code}
             
            and replace it with this one which uses a view, with the INSTEAD of trigger on UPDATE.
            {code}
            select 
            "ROWID",
            "SEQ_ID",
            "LANDING_SEQ",
            "SPECIES_ITIS",
            "GRADE_CODE",
            "MARKET_CODE",
            "UNIT_MEASURE",
            "DISPOSITION_CODE",
            "GEAR_CODE",
            "REPORTED_QUANTITY",
            "PRICE",
            "DOLLARS",
            "C013",
            "C014",
            "ADDITIONAL_MEASURE_FLAG",
            "ADDNL_REPORTED_QUANTITY",
            "ADDNL_UNIT_QUANTITY",
            "FINS_FLAG",
            "FINSATTACHED",
            "FINSNOTATTACHED",
            "FINSUNKNOWN",
            "FINS_CODE",
            "AREA_FISHED",
            "SUB_AREA_FISHED",
            "LOCAL_AREA_CODE",
            "EXPLANATION",
            "NATURE_OF_SALE",
            "HMS_AREA_CODE",
            "SALE_PRICE",
            "HMS_FLAG"
            from "#OWNER#"."LANDINGS_COLLECTION_VIEW"
            {code}