Oracle Forms: How to rollback and prevent record locking when exit_form or clear_form.

Hi,  thanks for your answer,  I posted an attention since I did not know if the right forum,  this is my first time I post a question in Expert Exchange.

To answer your question:
1a. No error displayed on forms but it did not allow me to exit, I could not exit from the form.doesnt the clear_form(FULL_ROLLBACK); give you errors ? It is not a valid first argument
1b. I updated it to clear_form.


2.  I changed to clear_form(NO_VALIDATE,FULL_ROLLBACK);   I tried test and seems the lock was released and I will continue testing,  however,  the other problem is that the stored procedure,  which does not have a commit (the forms will do it),  save the status history of the record in the detail table.   I select "NO" when I don't want to save the changes.   How come if I do clear_form(NO_VALIDATE, FULL_ROLLBACK)?  
     It also happened in the key_exit trigger,  if I do a clear_form,  the detail information of the current master table is saved.

3.   If you have a post method (no commit),  how do you create a stored procedure to rollback the post changed of the given record?
      Any reference that I can know about?

Hi,  thanks for your information.
I did create the package just to test using a temporary code in stored procedure to save in  a temporary table.   I issue a call to the package in database containing the procedure that you send me as sample
PROCEDURE rollback IS
  BEGIN
    rollback;
  END;


I need to know if it is just a rollback?  or dbms_standard.rollback_nr?

Yes  I know I searched too,  it works with just using the rollback.  I did create the package just to test using a temporary code in stored procedure to save in  a temporary table.   I issue a call to the package in database containing the procedure that you send me as sample.
It worked fine testing with temporary code.

The post method rolledback in database with your recommendation to clear the post method in key_exit to use clear_form and exit_form(no_validate) and when-button-pressed trigger change to clear_form(NO_VALIDATE,FULL_ROLLBACK) worked so no locked record stay in database.
I am still having the issue that the master-detail information is saved when stored procedure is executed an a no save request was issue from forms.   I even call the stored procedure in database to rollback and it does not do it.  

KEY-EXIT TRIGGER
declare
      v_resp number;
begin

set_form_property(:system.current_form, validation, property_false);
enter;  --- This is required to mark form_success as changed. This will not validate as VALIDATION is turned off.



/*  Prompt for save if form status is changed */
if :system.form_status = 'CHANGED'then
   v_resp := message_resp('YES_NO_CANCEL', 'Do you want to save the changes you have made?');
   /* If the user responds to above prompt as Yes, turn on validation and initiate
             it at form scope. */
      if v_resp = ALERT_BUTTON1 then -- User answered Yes
                        
         set_form_property(:system.current_form, validation, property_true);
         validate(form_scope);
         if form_success then
      exit_form(do_commit);
        end if;
                              
        /* if the user response is a 'No', exit the form without validating */
      
      elsif v_resp = ALERT_BUTTON2 then
             MY_PACKAGE_STORED_PROCEDUE.rollback; /* TEST ROLLBACK STORED PROCEDURE */
      clear_form;      
          exit_form(no_validate);
       else
               null;
      end if;
  else
      exit_form;
   end if;        
end;


IN WHEN-BUTTON-PRESSED TRIGGER TO NAVIGATE TO ANOTHER FORM I HAVE:

 -- If there are changes pending, ask user to commit or discard them
     if :system.form_status = 'CHANGED' then
         v_resp := message_resp('YES_NO_CANCEL', 'Do you want to save the changes you have made?');
     end if;
           
if v_resp in (ALERT_BUTTON1, ALERT_BUTTON2) or
                 :system.form_status = 'QUERY' then
      if v_resp = ALERT_BUTTON1 then -- User answered Yes
                  commit_form;
       elsif v_resp = ALERT_BUTTON2 then -- User answered No
          MY_PACKAGE_STORED_PROCEDUE.rollback;  /*TEST TO ROLLBACK STORED PROCEDURE*/
      clear_form(NO_VALIDATE, FULL_ROLLBACK);
                        
       end if;
           
       if :system.form_status in ('QUERY', 'NEW') then
                pl_id := get_parameter_list('tempdata');
                   ......
                   ......
                                call_form('calling_form',
                            hide,
                            do_replace,
                            no_query_only,
                            pl_id);
                   .....
               end if;

In the forms,  I have 1 master table's datablock and 5 detail datablock,  3 detail datablock are display only information,  but two detail datablock do transactions
DATABLOCK MASTER M_1
DATABLOCK DETAIL D_1   -- iT is a detail having status history of master table - M_1 -- In the form it display many records
DATABLOCK DETAIL D_2   -- it is a detail having attribute information detail of master table - M_1 and it display all records related to MASTER M_1 - many records displayed.
  Code exists in WHEN-VALIDATE_RECORD_TRIGGER of Detail D_2 that If something changed in D_2 for a given record and requires add information to master's detail D_1,  a stored procedure is executed to insert the master M_1 current status to status history,  user can add many more records to D_2 and the form calls stored procedure to add information to master's detail D_1.
  User can place the cursor to Master table M_1,  so that is a when-new-record-instance fired, and then click on exit or press the button to navigate to other form,  but the stored procedure call to insert the master status to the status history is added.  I can't find what is causing this since there is no commit in the forms.

I assume you already checked that your actions hit the  branch "MY_PACKAGE_STORED_PROCEDUE.rollback"  ?

The stored procedure adding status history has no
PRAGMA AUTONOMOUS_TRANSACTION in it ?

The stored procedure adding status history is a database produre adding records in the status-history table or a forms procedure adding records to the D1_1 block ?

If the answer to the last question is a database procedure how do you synchronize the D1_1 block with consecutive changes in M_1 or D_2 ?

Just curiosity , why did you split the master table in 2 blocks ? You can have 1 block with items distributed on 2 forms pages.
That requires some extra navigations actions but using 2 blocks requires other extra actions.

In answer to your questions:
1.- Yes,  I did create a test stored procedure to update a temporary table and the action of
     calling my_package_stored_procedure.rollback worked.   However in the current form I having an issue did not work when the stored procedure to insert information is called did not work but it worked to release the post method.


2.- No,  it does not have PRAGMA AUTONOMOUS_TRANSACTION.

3.- The stored procedure adding status history is a database procedure adding records in the status-history table,  it is not a forms procedure adding records to the D_1,  after the user hit commit,  there is a execute query,  so the information is displayed.  D_1 acts as a view only tabular datablock area in the form.



4.-  M_1 is the master table,  D_1 is the status history table, and D_2 is the documents table,  in the form D_1 is view only,  but the action to call the stored procedure and the user press commit,  an execute query exits,  when the execute query completes,  the values of D_1 are displayed.   The form has flags to identify the type of document,  based on document a status is assigned to M_1 in the form,  but prior to assigning the new status to M_1,  the stored procedure is called to insert the M_1 to current status to status history.  
In D_2,  in the when-validate-record trigger there is a condition:
    if attribute is not null then
         set_status(....)
    end if;

In oracle forms I have this program unit:
procedure set_status (p_status .....)
...
begin
BEGIN
  savepoint start_status;
  if p_status <> get_item_property('M1_TABLE.status', database_value)
    and p_status <> :M1_TABLE.status then
       my_package.sp_status_upd(:M1_TABLE.status, :M1_TABLE.userid);
       begin
           :M1_TABLE.status:= p_status;
            ....
            ....
            set_record_property(to_number(get_block_property'M1_table', current_record)),
                              'M1_Table', status, changed_status);

             set_record_property(:system.cursor_record, 'D1_TABLE', status, changed_status);

       exception
      when others then
          rollback to savepoint start_status;
         raise form_trigger_failure;

       end;                                                            
end if;
EXCEPTION
  when others then            
       rollback to start_status;
       .....                                       
end;                                        );            

In stored procedure my_package.sp_status_upd it only have a condition and an insert statement, no commit or rollback.

The syncronization is calling that stored procedure,  but seems the problem is that it does not roll back,  the form allow the user to select many records from D_2 and the when-validate-record keeps calling the procedure to set M_1 status and insert into status history in D_1


5.- No,  they are 3 different tables in database and in forms,  the master is not splitted in two datablocks.

What is causing this to fail the rollback?

Hello flow01,

I explained that by having my_package_stored_procedure.rollback did not work, I had it defined as
PACKAGE BODY MY_PACKAGE ....
PROCEDURE rollback IS
  BEGIN
    rollback;
  END;

However,  I tried and changed the procedure name as the following and the rollback process is now working in oracle forms.
PACKAGE BOY MY_PACKAGE ...
PROCEDURE MY_SP_ROLLBACK IS
BEGIN
  rollback;
END;

From oracle forms I call:
MY_PACKAGE.MY_SP_ROLLBACK;

This worked well.

I thanks so much for all your help.