0 Replies Latest reply: Jun 2, 2014 10:23 AM by jmniard RSS

    ORA-01445 with tabular form

    jmniard

      hello,

      I use Oracle apex 4.2.5

      i create a tabular form based on a view BUDGETSAL_TAB_VIEW defined as below :

      *********************************************************************************

      CREATE TABLE BUDGETSAL_TABLE ("RUBSRH" VARCHAR2(150 BYTE),"TEMPS"  VARCHAR2(150 BYTE),"BUDGETSAL" NUMBER  );

      ALTER TABLE BUDGETSAL_TABLE ADD CONSTRAINT BUDGETSAL_TABLE_PK PRIMARY KEY (RUBSRH,TEMPS)

      INSERT INTO BUDGETSAL_TABLE (RUBSRH,TEMPS,BUDGETSAL)   VALUES   ('AUTRECS','201001',26);


      CREATE TABLE "RUBSRH_STANDARD_TABLE" ( "RUBSRH"   VARCHAR2(150 BYTE)   );

      INSERT INTO RUBSRH_STANDARD_TABLE(RUBSRH)   VALUES   ('AUTRECS');

      INSERT INTO RUBSRH_STANDARD_TABLE(RUBSRH)   VALUES   ('SALBASE');


       

      CREATE TABLE "TEMPS_STD_MOIS_TABLE"  ( "TEMPS" VARCHAR2(150 BYTE)   );

      INSERT INTO TEMPS_STD_MOIS_TABLE(TEMPS)   VALUES   ('201001');


       

      CREATE OR REPLACE FORCE VIEW BUDGETSAL_TAB_VIEW

      as

      with table1 as (

      select R.RUBSRH,T.TEMPS,substr(T.TEMPS, 1, 4) annee,

      (select B.BUDGETSAL from BUDGETSAL_TABLE B where  B.TEMPS=T.TEMPS and B.RUBSRH=R.RUBSRH) BUDSAL

      from TEMPS_STD_MOIS_TABLE T,RUBSRH_STANDARD_TABLE R  )

       

      select RUBSRH ||  '-' || annee  pk,rubsrh,annee,

      (select sum(BUDGETSAL) from BUDGETSAL_TABLE t1 where t1.temps = substr(t.temps, 1, 4) || '01'

      and t1.rubsrh= t.rubsrh   ) Janvier

      from table1 t

       

       

      CREATE OR REPLACE PROCEDURE write_to_BUDGETSAL

         (  p_rubsrh      IN varchar2

         , p_temps      IN varchar2  

         , p_budsal     IN number)

      AS

      BEGIN

         IF p_budsal IS NOT NULL  THEN

            UPDATE   BUDGETSAL_TABLE

               SET   BUDGETSAL = p_budsal

             WHERE   RUBSRH = p_rubsrh

                AND   TEMPS = p_temps ;

       

            IF sql%ROWCOUNT = 0 THEN

               INSERT INTO BUDGETSAL_TABLE ( RUBSRH, TEMPS,BUDGETSAL)

                 VALUES   (p_rubsrh,p_temps,p_budsal);

            END IF;

         ELSE

              UPDATE   BUDGETSAL_TABLE

               SET   BUDGETSAL = null

             WHERE   RUBSRH = p_rubsrh   AND   TEMPS = p_temps        

                         

               ;

         END IF;

      EXCEPTION

         WHEN OTHERS THEN

            raise_application_error (-20001, 'Error updating the budgetsal!!!');

      END;

      /

       

      CREATE OR REPLACE TRIGGER BUDGETSAL_TAB_VIEW_io_trg

         INSTEAD OF UPDATE

         ON BUDGETSAL_TAB_VIEW

         REFERENCING NEW AS new OLD AS old

         FOR EACH ROW

      DECLARE

         v_first_day   date;

      BEGIN

      write_to_BUDGETSAL(:new.RUBSRH, :new.ANNEE || '01' , :new.JANVIER);

      EXCEPTION

         WHEN OTHERS THEN

            RAISE;

      END BUDGETSAL_TAB_VIEW_io_trg;

      *******************************************

      in the tabular form wizard, i choose PK as primary key and Updateable Columns JANVIER.

      When i run the tabular form , i have the error ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table .

      If i remove the validation based on janvier column, it works, but i want to keep this validation.

       

      best regards

      jean marc