Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Data disappearing

Data disappearing

From: Steven Deneir <Steven.Deneir_at_health.fgov.be>
Date: 1998/03/17
Message-ID: <6em17p$2uh$1@dalet.belnet.be> 







Problem description



The application has a certain part in which users can enter data concerning
expenses that persons had to make to prepare a reunion. After each reunion,
the persons that were present sign a paper and enter their hours of arrival
and some other necessary information. Then a user enters all the information
in the application, and launches some lists. After each month some global
lists are made in order to do the payments. But at that time some of the
data has disappeared.



The data that disappeares varies from all the records for a certain reunion
that no longer exist and data that changes (values for expenses, houres,… no
longer filled in). If I then look at the fields USER_CREATED, USER_MODIFIED
and the corresponding dates, they are usually NULL (after insert), or they
show the previous user that has done a modification! It also doesn’t happen
at predictable times… Sometimes it happens after some days, sometimes after
a week. The users don’t have any idea what they have just done when it
happens.



All this leads to an impression that a rollback has occurred… (apparently a
user has already seen a message like that but has never contacted me…)
Although? A little example : the user selects a reunion (screen1) from there
he goes to the invited persons (screen2 – problem table treated). He enters
the data and exits the screen. Normally the data should be saved


(automatically by the application : button code = exit_form(do_commit); ).

In the 1° screen he launches a list (Oracle Reports) and the data are OK. At
the end of the month he launches the global list and the data has
disappeared… The list that is OK has been launched 11/03/1998 and the field
DATE_MODIFIED has the value 10/02/1998. The user knows he entered the data
the same day he launched the list, so this is 11/03/1998 !



This scenario arrives only at 1 table : T_COMPTA_REUNION
DESC T_COMPTA_REUNION :


NO_RAPPORTEUR NOT NULL VARCHAR2(8)
NO_SEQ_REUNION NOT NULL NUMBER(7)
NO_SEQ_PRESENTE NOT NULL NUMBER(2)




PRESENT  VARCHAR2(1)


JETON_PRESENCE  NUMBER(4)
FRAIS_DEPL  NUMBER(4)
FRAIS_SEJOUR  NUMBER(4)
HEURE_DEPART  VARCHAR2(5)
HEURE_ARRIVEE  VARCHAR2(5)




BUT_REUNION  VARCHAR2(3)



TYPE_INVITE  VARCHAR2(1)



VOORZITTER_YN  VARCHAR2(1)


DATE_CREATED  DATE
DATE_MODIFIED  DATE
USER_CREATED  VARCHAR2(30)
USER_MODIFIED  VARCHAR2(30)




There exist 2 triggers on the table :


T_COMPTA_REUNION_BRI



BEGIN



  /* QMS$DATA_AUDITING */



  declare

     cursor c_dual
     is
     select sysdate
     ,      user
     from   dual;
     l_sysdate   date;
     l_user      varchar2(30);
  begin
     open c_dual;
     fetch c_dual
     into  l_sysdate
     ,     l_user;
     close c_dual;
     :new.DATE_CREATED := l_sysdate;
     :new.USER_CREATED := l_user;
     :new.DATE_MODIFIED := l_sysdate;
     :new.USER_MODIFIED := l_user;




  end;


END;


and  T_COMPTA_REUNION_BRU


BEGIN



  /* QMS$DATA_AUDITING */



  declare

     cursor c_dual
     is
     select sysdate
     ,      user
     from   dual;
     l_sysdate   date;
     l_user      varchar2(30);
  begin
     open c_dual;
     fetch c_dual
     into  l_sysdate
     ,     l_user;
     close c_dual;
     :new.DATE_MODIFIED := l_sysdate;
     :new.USER_MODIFIED := l_user;




  end;


END;


As far as I know there are only 2 .fmx files that access that table, and the
only sql-commands that enter data in the table are INSERT-commands, not 1
update-statement.



The first fmx is REUNION. On this form there exists a button that searches
all persons that need to be invited to the reunion and inserts them in the
table. Here follows the code :


DECLARE



   var_teller NUMBER(2);


   tmp_rapporteur VARCHAR2(8);


   CURSOR cur_compta_reunion IS


          SELECT no_rapporteur, no_seq_reunion, no_seq_presente, present,
jeton_presence, frais_depl, frais_sejour, heure_depart, heure_arrivee,
but_reunion, type_invite

          FROM t_compta_reunion
          WHERE no_rapporteur = tmp_rapporteur and no_seq_reunion =


:blk_reunion.no_seq_reunion;


   REC_COMPTA_REUNION cur_compta_reunion%ROWTYPE;
   CURSOR cur_membres IS

          SELECT no_section, no_rapporteur, type_person
          FROM t_sect_rapp
          WHERE no_section = :blk_reunion.no_section;


   REC_MEMBRES cur_membres%ROWTYPE;


   CURSOR cur_rapp IS

          SELECT no_rapporteur, code_statut
          FROM t_rapporteur
          WHERE no_rapporteur = tmp_rapporteur;


   REC_RAPP cur_rapp%ROWTYPE;



   CURSOR cur_dossiers IS

          SELECT no_responsable_service, no_responsable_dossier
          FROM t_dossiers d, t_compta_rapp c
          WHERE c.no_seq_reunion = :blk_reunion.no_seq_reunion
            AND d.no_csh = c.no_csh
            AND d.date_entree = c.date_entree;


   REC_DOSSIERS cur_dossiers%ROWTYPE;


   CURSOR cur_doss_rapp IS

          SELECT no_rapporteur
          FROM t_compta_rapp
          WHERE no_seq_reunion = :blk_reunion.no_seq_reunion;


   REC_DOSS_RAPP cur_doss_rapp%ROWTYPE;



The second fmx (INVITE) doesn’t even has insert/update statements, so it are
only the changes made by a user that are saved!



There are a lot of reports that access the table, but not one does
modifications in the database…



What is the problem? Where is it situated, in the application or in the
database?


Can anybody help me??



Thanks in advance



PS If you can reply, please send a copy to Steven.Deneir_at_health.fgov.be, I
would really appreciate it

--
--------------------------------------------------------------------------
Mail from Deneir Steven
Address StevenDeneir_at_writeme.com
Join the Internet Tour for each Koi Lover at
http://www.geocities.com/Heartland/Prairie/8983/
--------------------------------------------------------------------------


Received on Tue Mar 17 1998 - 00:00:00 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US