1 2 Previous Next 26 Replies Latest reply: Apr 8, 2011 1:23 PM by jwellsnh RSS

    How to use a button to fire PLSQL  anonymous block or stored procedure

    Doug
      I am converting from a Forms application to APEX, and things that used to be easy have become almost impossibly difficult. I have a page that represents a record with one item called "history_rec." This is a checkbox item. Clicking it causes the column for this record to be populated with the letter "H," indicating that this record is now historical and not active. I also have on this page a button labeled "Renew Record." This button redirects to the current page and sets the value of 5 page items to the values in the record being replaced by the new record.

      (All this, by the way, is for an insurance tracking system on loan collateral. The insurance has to be renewed annually, and this system is used to make certain that it is.)

      I also want the button to fire a process which updates the old record by populating the history_rec column. To do this I created a process called "make_history" which contains the following SQL code:

      "Update insure
      set history_rec = 'H'
      where insureseq = :P22_INSURESEQ"

      For the "Process Point," I have selected "On Demand: Run this process when requested by AJAX."

      Is this possible from my "Renew Record" button, and if so, how? If not, how should I proceed?
        • 1. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
          TexasApexDeveloper
          Have the process run on the button click....

          Thank you,

          Tony Miller
          Webster, TX

          Follow your passion; the rest will take care of itself.

          JMS

          If this question is answered, please mark the thread as closed and assign points where earned..
          • 2. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
            Doug
            I'm sorry if I'm being incredibly stupid, but if I knew how to do what you have told me to do, I would not have needed to ask the question.
            • 3. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
              jwellsnh
              Doug,

              This is how I do it.

              On your button:
              Under Optional URL Redirect, set Target is a URL

              Under URL Target, set to javascript:confirmDelete('Are you sure you want to update the old record by populating the history_rec ?','APPLY');

              On your PL/SQL anonymous block Process:
              Set your Condition Type to Request = Expression 1.

              Within Expression 1, set to APPLY

              Jeff
              • 4. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                TexasApexDeveloper
                When creating a page process, after defining the pl/sql block, the next button click sends me to a form asking Page 3 of creating a process on a page, I am prompted with the the selections on when I want my process to fire, the first option is When Button Pressed..

                No need to be snarky, we are ALL VOLUNTEERS here in the forum.... You don't like an answer, you can get a PROMPT refund..

                Thank you,

                Tony Miller
                Webster, TX

                There are two kinds of pedestrians -- the quick and the dead.

                If this question is answered, please mark the thread as closed and assign points where earned..
                • 5. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                  Doug
                  Thank you, Jeff. I appreciate your time and effort to enlighten this Apex newbie.

                  But there are two problems:

                  (1) The instruction you gave me went through the motions. I got the confirmDelete message with an "OK" and "Cancel" option, but the column history_rec was not updated. It remained null. After getting an error (which occurred after entering the "Redirect to URL" instruction), I did have to amend the PLSQL

                  update insure
                  set history_rec = 'H'
                  where insureseq = :P22_INSURESEQ;

                  by changing the "&P22_INSURESEQ." to what you see above.

                  (2) The instruction is placed in the "Action When Button Clicked" tab of my "Renew Record" button. That tab was already being used to take values from the existing record to set items in the new record. This was accomplished by the action "Redirect to Page in this Application" with the "Set these Items" and "With these items" fields completed. This instruction was nullified when I changed the action to the "Rediirect to URL" option.

                  So is it even possible in Apex to do two things with one button? I tried setting the items with values in a branch to the current page using all of the "Branch Point" options, but this did not work either.

                  Any ideas?

                  Edited by: Doug on Apr 2, 2011 10:22 AM
                  • 6. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                    jwellsnh
                    Doug,

                    Consider disabling your current button(s) and page process(s) and establish a new button. I was referring to an APEX 3 application on my last post which may have been misleading so here are instructions in APEX 4 terms.

                    Button:
                    Action When Button Clicked
                    Action Redirect to URL
                    URL Targert javascript:confirmDelete('Are you sure you want to commit the updates entered on the page?','APPLY');

                    Page Process:
                    Type:      PL/SQL anonymous block
                    Process Point On Submit - After ........
                    Process
                    BEGIN
                       UPDATE insure
                       SET    history_rec = 'H'
                       WHERE  insureseq = :p22_insureseq;
                       
                    -- Add a second SQL statement or as many as you need
                    
                    EXCEPTION
                       WHEN OTHERS
                       THEN
                          ROLLBACK;
                    
                          :p22_error_message := sqlerrm;
                    END;
                    Conditions
                    Condition Type Request = Expression 1
                    Expression 1 APPLY

                    The above process should then fire when you press your new button and click OK.
                    Hope this works for you.

                    Jeff
                    • 7. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                      Doug
                      Jeff, I know my doubts are premature, but I'm having them anyway. Can you confirm for me that it is possible in APEX to update a record in one table (or more than one table) and at the same time insert a record in the same table (or another table). In my Oracle Forms application, I have a number of screens in which multiple table updates and/or inserts occur. In Forms it is accomplished with key-commit, pre-insert, pre-update, pre-commit, post-commit, etc triggers. If I can't do it in APEX 4.0, then I may well have to wait until a later version is issued.

                      Now, back to the issue at hand. I did as you instructed on the Process. I set the source as follows:

                      BEGIN
                      UPDATE insure
                      SET history_rec = 'H'
                      WHERE insureseq = :p22_INSURESEQ;

                      -- Add a second SQL statement or as many as you need


                      EXCEPTION
                      WHEN OTHERS
                      THEN
                      ROLLBACK;

                      :p22_error_message := sqlerrm;
                      END;

                      Under Conditions for the process, I set Condition Type to "Request= Expression 1" and I set Expression 1 to "APPLY". I edited the button so that under Action When Button Clicked, the Action is set to "Redirect to URL"; Execute Validations is set to "Yes"; and the URL Target is set to "javascript:confirmDelete('Are you sure you want to commit the updates entered on the page?','APPLY');", and the Database Action is set to "SQL UPDATE action".

                      Result: If I first click the History_Rec checkbox, the action is processed, and the record is updated. If I do not click the History_Rec checkbox, there is no action and the record is not updated. Bottomline is that the process has provided the "comfirm" window for the action,but other than that, the same action was previously achievable by simply using the default "Apply Changes" button. Now, the "Apply Changes" button causes an error message (due to the EXCEPTION I assume) if used to update the History_Rec column but works fine for other columns.

                      OK, now remember that what I'm looking to do is to both update the History_Rec column and also to insert a new record for a new time period. So, seeing your comment: "-- Add a second SQL statement or as many as you need," I added SQL code to cause the insert immediately following the update code. Here's how the whole thing looks:

                      BEGIN
                      UPDATE insure
                      SET history_rec = 'H'
                      WHERE insureseq = :p22_INSURESEQ;

                      -- Add a second SQL statement or as many as you need

                      insert into insure(dktnr,insid,insty,bnder,bdate,inamt,inbeg,agref,uwref)
                      values(:P22_DKTNR,:P22_INSID,:P22_INSTY,:P22_BNDER,sysdate,:P22_INAMT,:P22_INBEG,
                      :P22_AGREF,:P22_UWREF);

                      EXCEPTION
                      WHEN OTHERS
                      THEN
                      ROLLBACK;

                      :p22_error_message := sqlerrm;
                      END;

                      I had high hopes for this one, but it did not work. Just like before, If I click the History_Rec checkbox, the History_Rec column will be updated, but otherwise not. In neither case will the new record be inserted. Note that there is another process for this page, which was generated automatically by APEX, called "Process Row of INSURE." In this process, under "Source: Automatic Row Processing (DML)," I set the "Allowed Operations" to both Insert and Update. I left the third option "Delete" unchecked. Doing this did not provide be the outcome I am looking for.

                      Desperately Seeking Solution, Doug
                      • 8. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                        jwellsnh
                        Doug,

                        You stated
                        In Forms it is accomplished with key-commit, pre-insert, pre-update, pre-commit, post-commit, etc triggers.
                        APEX will only do what you instruct it to do, you need to apply those triggers or any other back end Oracle processes as well in APEX.

                        Back to my previous post when I stated to add additional code for updates, inserts, etc., understand that you are executing PL/SQL code on Oracle. So what I am saying is remove the concept that APEX is doing something magical in the background. It my example I was only conveying how to get APEX to fire PL/SQL with confirmation and nothing more. If the tables being manipulated via the PL/SQL need triggers to aid the process, then you need to add the appropriate triggers to those tables.

                        Jeff
                        • 9. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                          Doug
                          Jeff, thanks for your speedy reply. I thought that I was instructing APEX in the PLSQL anonymous block process to (1) update the table insure's history_rec column and (2) insert a new record into the table insure with the columns and values listed. Is this not correct? Was I actually executing the javascript confirm and nothing else? If so, could you possibly give me some clues on how to achieve my two main purposes (the update and the insert)?
                          • 10. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                            jwellsnh
                            Doug,

                            Confirming your button press should be executing the PL/SQL defined in your page process. From your earlier post, it sounds like you need to add some back end processing like you have used in the past, via triggers.
                            In Forms it is accomplished with key-commit, pre-insert, pre-update, pre-commit, post-commit, etc triggers.
                            Jeff
                            • 11. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                              Doug
                              OK, I have now put the update of the insure table into a stored procedure, this:

                              create or replace procedure "INSURE_HISTORY_REC"
                              (oldseqno IN NUMBER)
                              is
                              begin
                              update insure
                              set history_rec = 'H'
                              where insureseq = oldseqno;
                              EXCEPTION
                              WHEN OTHERS
                              THEN
                              null;
                              end;

                              The source in the make_history process has this code which references the procedure:

                              BEGIN
                              insure_history_rec(:P22_OLD_SEQNO);
                              END;

                              The condition for the process is that the CREATE button be pressed.

                              The update code is not firing, but the new record is created.

                              Any ideas on why the update does not fire?
                              • 12. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                                jwellsnh
                                Doug,

                                It appears that you have more than one page process defined on your page and that one process is creating a new record or row in a table. Is the new record writing to your insure_history_rec table?

                                If you have multiple page processes defined, how are they sequenced and how is the process that is creating the new record conditioned to execute?

                                Maybe you could place an example up on apex.oracle.com that I or others could help you troubleshoot?

                                Jeff
                                • 13. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                                  Doug
                                  Jeff, thanks. You hit it. The problem was the sequence. As soon as I moved the MAKE_HISTORY process from the last position, it fired the stored procedure. By the way, the stored procedure is INSURE_HISTORY_REC. The table it acts upon is INSURE.

                                  There is one little glitch. After I changed the sequence of the processes, when I press the "Create" button I get a success message, "Action Processed." But then there is an error message: "Error Unable to fetch row" with this ORA code "ORA-01403: no data found." I assume the sequence may have something to do with this error, but I'm not sure. I've tried several other arrangements, but none rid me of the error. I am not aware that I'm attempting, in fact, to fetch a row at this point.

                                  There are 5 processes, as follows with the sequence numbers and process points in parentheses:

                                  (10) Process Row of Insure (After Page Submit: After Computations and Validations)
                                  (20) make_history (After Page Submit: After Computations and Validations)
                                  (30) Get PK (After Page Submit: After Computations and Validations)
                                  (40) reset page (After Page Submit: After Computations and Validations)
                                  (50) Fetch Row from INSURE (After Header)

                                  I created the MAKE_HISTORY and the PAGE RESET processes. The others were created automatically by APEX. Do you think the order of the processes is causing the error?

                                  After a successful process, what I would like to do is have the application return to page 21, but I have not tried that yet.
                                  • 14. Re: How to use a button to fire PLSQL  anonymous block or stored procedure
                                    jwellsnh
                                    Doug,

                                    Happy that you have made some progress.

                                    Which process is throwing the error, is it the very last process?
                                    "Error Unable to fetch row" with this ORA code "ORA-01403: no data found."
                                    Is page 21 the page you are developing?
                                    what I would like to do is have the application return to page 21
                                    What do you have for Page Branches already?

                                    Jeff
                                    1 2 Previous Next