Sign Up

❇️ OrclQA.Com is a question and answer forum for programmers.
❇️ Here anyone can ask questions and anyone can answer to help others.
❇️ It hardly takes a minute to sign up and it is 100% FREE.

Have an account? Sign In


Have an account? Sign In Now

Sign In

Forgot Password?

Don't have account, Sign Up Here

Forgot Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Have an account? Sign In Now

Sorry, you do not have a permission to ask a question, You must login to ask question.

Forgot Password?

Need An Account, Sign Up Here
Sign InSign Up

OrclQA.Com

OrclQA.Com Logo OrclQA.Com Logo

OrclQA.Com Navigation

  • Ask Question
  • Write a Tutorial
  • Online Courses
Search
Ask A Question

Mobile menu

Close
Ask a Question
  • Home
  • Blog
  • New Questions
  • Tutorials
    • Oracle
    • Oracle Apex
    • Python
  • Tags
  • Users
  • Badges & Points
  • Image to Base64
  • PL/SQL Beautifier
  • Ask Question
  • Write a Tutorial
  • Online Courses
Vina
Vina

Vina

  • 1 Question
  • 4 Answers
  • 0 Best Answers
  • 26 Points
View Profile
  • 1
VinaContributor
Asked: August 18, 20202020-08-18T17:08:57+05:30 2020-08-18T17:08:57+05:30

Update table when user confirms

  • 1
Advertisement

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.

 

Advertisement

Thanks,

Vina

Advertisement

orclapexupdupdate
  • 7
  • 577
  • 0
  • 0
  • Share
    • Share on Facebook
    • Share on Twitter
    • Share on LinkedIn
Answer
    Advertisement

    7 Answers

    1. Vinish

      Vinish

      • 1 Question
      • 863 Answers
      • 74 Best Answers
      • 4k Points
      View Profile
      Vinish Enlightened
      2020-08-18T17:22:16+05:30Added an answer on August 18, 2020 at 5:22 pm
      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
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
      • Vina

        Vina

        • 1 Question
        • 4 Answers
        • 0 Best Answers
        • 26 Points
        View Profile
        Vina Contributor
        2020-08-18T17:39:38+05:30Replied to answer on August 18, 2020 at 5:39 pm

        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;
         

        • 0
        • Share
          Share
          • Share on Facebook
          • Share on Twitter
        • Vinish

          Vinish

          • 1 Question
          • 863 Answers
          • 74 Best Answers
          • 4k Points
          View Profile
          Vinish Enlightened
          2020-08-18T18:37:13+05:30Replied to answer on August 18, 2020 at 6:37 pm

          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.

          • 0
          • Share
            Share
            • Share on Facebook
            • Share on Twitter
    2. Vina

      Vina

      • 1 Question
      • 4 Answers
      • 0 Best Answers
      • 26 Points
      View Profile
      Vina Contributor
      2020-08-18T17:41:05+05:30Added an answer on August 18, 2020 at 5:41 pm

      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
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
    3. Advertisement
    4. Vina

      Vina

      • 1 Question
      • 4 Answers
      • 0 Best Answers
      • 26 Points
      View Profile
      Vina Contributor
      2020-08-18T19:26:22+05:30Added an answer on August 18, 2020 at 7:26 pm

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

      • 0
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
    5. Vina

      Vina

      • 1 Question
      • 4 Answers
      • 0 Best Answers
      • 26 Points
      View Profile
      Vina Contributor
      2020-08-18T20:16:18+05:30Added an answer on August 18, 2020 at 8:16 pm

      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
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
      • Vinish

        Vinish

        • 1 Question
        • 863 Answers
        • 74 Best Answers
        • 4k Points
        View Profile
        Vinish Enlightened
        2020-08-19T04:59:44+05:30Replied to answer on August 19, 2020 at 4:59 am

        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
        • Share
          Share
          • Share on Facebook
          • Share on Twitter

    You must login to add an answer.

    Forgot Password?

    Advertisement

    Sidebar

    Ask Question
    Write a Tutorial
    Advertisement

    Recent Blog Posts

    • Vinish

      Oracle LENGTH()

    • Vinish

      Oracle INSTR() Function

    • Vinish

      Oracle INITCAP() Function

    • Vinish

      Oracle Concat

    • Vinish

      Oracle CHR Function

    Advertisement
    Advertisement

    Explore

    • Home
    • Blog
    • New Questions
    • Tutorials
      • Oracle
      • Oracle Apex
      • Python
    • Tags
    • Users
    • Badges & Points
    • Image to Base64
    • PL/SQL Beautifier

    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
    © 2021 OrclQA.Com. All Rights Reserved.
    Privacy Policy - About Us