3 Replies Latest reply: May 6, 2013 3:36 PM by KarenH RSS

    INSTEAD OF trigger on view to update a table. error in 4.2apex tabular rpt

    KarenH
      I have created a view (LANDINGS_VIEW') that I am hoping to use to add/modify data over several tables. I am using INSTEAD OF trigger to update/insert into the underlying tables. I am receiving the error:
      •ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "SAFIS.LANDINGS_V_IO_UPD_TRG", line 4 ORA-04088: error during execution of trigger 'SAFIS.LANDINGS_V_IO_UPD_TRG' (Row 1)
      I am only setting PRICE = 300.


      any thoughts? Am I setting this up propertly? thanks for your help!!
      Karen



      The LANDING_VIEW is set up as follows:
      -- Start of DDL Script for View SAFIS.LANDINGS_VIEW
      -- Generated 03-May-2013 10:25:38 from SAFIS@SAFISD.world
      
      CREATE OR REPLACE VIEW landings_view (
         landing_seq,
         dealer_rpt_id,
         unit_measure,
         reported_quantity,
         landed_pounds,
         dollars,
         disposition_code,
         grade_code,
         species_itis,
         market_code,
         price,
         area_fished,
         sub_area_fished,
         lease_num,
         gear_code,
         de,
         ue,
         dc,
         uc,
         local_area_code,
         fins_attached,
         explanation,
         late_report,
         modified_data,
         nature_of_sale,
         hms_area_code,
         sale_price,
         deleted )
      AS
      select l.LANDING_SEQ,
             l.DEALER_RPT_ID,
             l.UNIT_MEASURE,
             l.REPORTED_QUANTITY,
             l.LANDED_POUNDS,
             l.DOLLARS,
             l.DISPOSITION_CODE,
             l.GRADE_CODE,
             l.SPECIES_ITIS,
             l.MARKET_CODE,
             l.PRICE,
             l.AREA_FISHED,
             l.SUB_AREA_FISHED,
             l.LEASE_NUM,
             l.GEAR_CODE,
             l.DE,
             l.UE,
             l.DC,
             l.UC,
             l.LOCAL_AREA_CODE, 
             a.fins_attached, 
             a.explanation,
             a.late_report, 
             a.modified_data, 
             a.nature_of_sale,
             a.hms_area_code, 
             a.sale_price, 
             
             a.deleted 
        from landings l, 
             landings_hms a
        where  l.dealer_rpt_id = v('P110_DEALER_RPT_ID') and
              l.dealer_rpt_id = a.dealer_rpt_id(+) and
              l.landing_seq = a.landing_seq(+)
      /
      
      
      -- Triggers for LANDINGS_VIEW
      
      CREATE OR REPLACE TRIGGER landings_v_io_upd_trg
       INSTEAD OF
        UPDATE
       ON landings_view
      REFERENCING NEW AS NEW OLD AS OLD
      DECLARE
         v_first_day   date; 
         BEGIN
      update landings set landing_seq = :old.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 ;      
          
         
        
          /*  update landings_hms  set dealer_rpt_id = :old.dealer_rpt_id, 
                                   landing_seq = :old.landing_seq, 
                                   fins_attached = :new.fins_attached, 
                                   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 = sysdate, 
                                   ue = :new.ue, 
                                   dc = :new.dc, 
                                   uc = :new.uc ;                          
              
                   
              */       
      end;
      
      
      
      -- End of DDL Script for Trigger SAFIS.LANDINGS_KEH_V_IO_TRG
      /
      
      CREATE OR REPLACE TRIGGER landings_v_io_trg
       INSTEAD OF
        INSERT
       ON landings_view
      REFERENCING NEW AS NEW OLD AS OLD
      DECLARE
         v_first_day   date; 
         BEGIN
      insert into landings_keh (landing_seq, 
                                dealer_rpt_id, 
                                unit_measure,
                                reported_quantity, 
                                landed_pounds, 
                                dollars,
                                disposition_code, 
                                grade_code, 
                                species_itis, 
                                market_code,
                                price, 
                                area_fished, 
                                sub_area_fished, 
                                lease_num,
                                gear_code, 
                                de, 
                                ue, 
                                dc, 
                                uc, 
                                local_area_code)       
         values ( landings_seq.NEXTVAL, 
                  :new.dealer_rpt_id, 
                  :new.unit_measure,
                  :new.reported_quantity,
                  :new.landed_pounds, 
                  :new.dollars,
                  :new.disposition_code, 
                  :new.grade_code, 
                  :new.species_itis, 
                  :new.market_code,
                  :new.price, 
                  :new.area_fished, 
                  :new.sub_area_fished, 
                  :new.lease_num,
                  :new.gear_code, 
                  sysdate, 
                  :new.ue, 
                  :new.dc, 
                  :new.uc, 
                  :new.local_area_code)  ;
         
         insert into landings_hms (dealer_rpt_id, 
                                   landing_seq, 
                                   fins_attached, 
                                   explanation,
                                   late_report, 
                                   modified_data, 
                                   nature_of_sale,
                                   hms_area_code, 
                                   sale_price, 
                                   de, 
                                   ue, 
                                   dc, 
                                   uc, 
                                   deleted)
             values (:new.dealer_rpt_id, 
                     landings_seq.CURRVAL, 
                     :new.fins_attached, 
                     :new.explanation,
                     :new.late_report, 
                     :new.modified_data, 
                     :new.nature_of_sale,
                     :new.hms_area_code, 
                     :new.sale_price, 
                     sysdate, 
                     :new.ue, 
                     :new.dc, 
                     :new.uc, 
                     :new.deleted); 
                   
                     
      end;
      
      
      
      -- End of DDL Script for Trigger SAFIS.LANDINGS_KEH_V_IO_TRG
      /
      
      
      -- End of DDL Script for View SAFIS.LANDINGS_VIEW
      btw, I have succefully run the following update in sqlplus.
      update landings set landing_seq = 8604583, 
      dealer_rpt_id = 2660038, 
      unit_measure = 'LB', 
      reported_quantity = 3,
       -- landed_pounds = :new.landed_pounds, 
      dollars = 900, 
      disposition_code= '001', 
      grade_code = '10', 
      species_itis = '160200', 
      market_code = 'UN', 
      price = 30, 
      area_fished = null, 
      sub_area_fished =null, 
      -- lease_num = :new.lease_num, 
      gear_code = '050', 
      de = sysdate, 
      ue = 'keh', 
      dc = null, 
      uc = 'keh', 
      local_area_code = null 
      where landing_seq = 8604583; 
      I am using apex 4.2

      Edited by: KarenH on May 3, 2013 10:29 AM

      Edited by: KarenH on May 3, 2013 10:31 AM

      Edited by: KarenH on May 3, 2013 11:04 AM

      Edited by: KarenH on May 3, 2013 4:09 PM