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.
- I have created a customer button for Update. I have a hidden data item which is incremented after the grid is saved.
- I have a dynamic action created on change of the hidden data item to execute javascript and call the ajax callback process.
- 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
Vinish Kapoor
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.
Vina
Hi Vinish,
Second step worked fine. I am receiving confirmation message pop up. Now i want a table to be updated when user presses ok.
apex.message.confirm(data,function( okPressed ) {
if( okPressed ) {
// do somethig if ok button pressed
apex.server.process('v_link_update_1',
{
pageItems : '#P45_POP_UP'
}
,
{
dataType : 'text', success : function(data)
{
if(data != 'SUCCESS') apex.message.alert(data);
}
}
below is the v_link_update_1
Declare
v_cntr number;
v_count number;
v_pop_up number;
begin
update employee
enddate = to_date('08/18/2020','mm/dd/yyyy')
where emp_id = '99609' ;
select 10 into :CNTR_UPD_1 from dual;
If :CNTR_UPD_1 = 10 Then
htp.prn('Updated Successfully');
Else
htp.prn('SUCCESS');
End If;
End;
Vinish Kapoor
Your PL/SQL code should be like this:
Declare v_cntr number; v_count number; v_pop_up number; begin update employee enddate = to_date(’08/18/2020′,’mm/dd/yyyy’) where emp_id = :P45_POP_UP; If sql%rowcount > 0 Then htp.prn(‘SUCCESS’); Else htp.prn(‘Error while updating table.’); End If; End;I am assuming the page item P45_POP_UP is the employee id or if it is not then I think you should pass employee id page item to it.
Vina
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
Vina
Tried sql%rowcount =0 in if condition and did get any message back.
Vina
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 -
Vinish Kapoor
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:
Let me know if any queries.