Home/ Questions/Q 238
In Process
Vina
Vina

Vina

View Profile
  • 1
Contributor
Asked: 2020-08-18T17:08:57+05:30

Update table when user confirms

  • 1

How to update a database table column using ajax call back process. I have a an interactive grid on a table A and when records in table A are updated the process should get user confirmation and update table B if user confirms by pressing ok.

  1. I have created a customer button for Update. I have a hidden data item which is incremented after the grid is saved.
  2. I have a dynamic action created on change of the hidden data item to execute javascript and call the ajax callback process.
  3. It works fine and give the confirmation message. However, when user presess Ok, i am not able to update the database table B

Any suggestion will help.

 

Thanks,

Vina

orclapexupdupdate

    7 Answers

    1. Vinish

      Vinish

      View Profile
      Enlightened
      This answer was edited.

      In the 2nd step, in which you are calling the Ajax process using the JavaScript code, put that JS code between the if condition, as shown in the below example:

      apex.message.confirm( "Confirm to update table B?", function( okPressed ) { 
if( okPressed ) {
// add your JavaScript code here
} 
});

      Or if this part is already working, then you can refer to the following post in which I have given an example to call the Ajax process using JavaScript.

      Calling Ajax Process from JavaScript in Oracle Apex.

      That example is related to the validation, you just ignore that part and use the PL/SQL code to update your table.

      Let me know if any questions.

      • 0
    2. Vina

      Vina

      View Profile
      Contributor

      I am able to get the Updated Successfully message statement as per the logic if I remove the update sql statement from the ajax callback process v_link_update_1

      • 0
    3. Vina

      Vina

      View Profile
      Contributor

      Tried sql%rowcount =0 in if condition and did get any message back.

      • 0
    5. Vina

      Vina

      View Profile
      Contributor

      to be more specific, below are the sequence of steps that need to happen.

      1.We have two tables A and Table 8. We have an interactive grid on table A. There is a custom Update button to update the changes made to records in table A.

      2. When the records in the IG are updated by clicking on Update button. First the updates need to be save.

      3. Then check  if there are no records in Table A with enddate column as null then user should receive a pop-up confirmation message if enddate in table B needs to be updated.

      4. If user presses Ok button.

      5.Then we need to have a pl/sql process to update table B.

      can you suggest the approach i should be taking.

      I have tried and achieved till step 4, by doing the below -

      1. create an IG based on sql. have custom button for update(dynamic action javascript).
      2.  Process to update records
      3.  add setvalue action in dynamic action for update button after saving to set new item value to 1
      4.  create dynamic action on "new item" to execute Java script which call apex callback process.
      5.  able to get pop-up message and user is able to click ok. With this was able to acheive till step 4 of problem description.
      6.  the update required after the pop up is not working.
      • 0
      • Vinish

        Vinish

        View Profile
        Enlightened

        Ok, I think you should follow this approach.

        Before setting the value to 1 for the hidden item, you should for the null enddate in the table A. If enddate is not null then only set the hidden item value to 1, so that the DA can execute then.

        You can set the item value using the below PL/SQL code:

        Declare
   n_count number;
Begin
   Select count(1) into n_count from employee_table_A
     where emp_Id = :p45_pop_up and enddate is null;

   If n_count = 0 then
      -- meaning no nulls present 
      apex_util.set_session_state('YOURHIDDENITEM', 1);
   End if;
Exception
   when others then
     null;
End;

        Set the P45_POP_UP item in the Submitted Items for the PL/SQL Code DA.

        Set the Returned Items as YOURHIDDENITEM for the PL/SQL code DA.

        Now when the hidden item value will be set to 1, then your DA will run, which will execute the JavaScript code to call the Ajax process to ask the user for the confirmation and will update the table B. Your v_link_update_1 process code should be something like below:

        Declare
v_cntr number;
v_count number;
v_pop_up number;
begin
update table_b
enddate = to_date(’08/18/2020′,’mm/dd/yyyy’)
where emp_id = :P45_POP_UP;

Commit;
-- no need to return any message using htp.p
End;

        Let me know if any queries.

        • 0

    You must login to add an answer.