3 Replies Latest reply on Apr 29, 2012 1:52 PM by Prabodh

    PLSQL Insert and Special Circumstance

      I am using APEX 4.0.

      This one is strange. I have a system used to renew UCC filings, which must be renewed within six months of their expiration. I have a report (UCC Tracker Report) which lists all records falling within this date range. I click a sequence number (column link) for a record and go to a form detail page (UCC Detail) for the record identified by the sequence number. There are a number of data items on this page which will not change with the filing renewal; so I want to carry these data items into the new record. I do this with a button labeled "Renew UCC." It executes a PLSQL process which does two things: (1) it inserts a new record with the data preserved from the old record, and (2) updates the old record with an "H" in the "histrec" column (which makes this record history).

      Now, the second part works; the old record is always updated. The first part (the insert) does not work, except under a special circumstance. Back on the UCC Tracker Report page there is a button labeled "Enter New UCC Record." This button calls the same page (UCC Detail) that is called for the renewal, but, of course, since it is for new record, it will have null items. There is a button on it (Return to UCC Tracker Report) which will simply allow the user to return to the report page. If the user goes to this UCC Detail page, then returns to the UCC Tracker Report page, and then clicks a sequence number (column link) to bring up the record for renewal, clicking the Renew UCC button causes the PLSQL to insert the new record. Otherwise, it will not insert.

      Somewhere there must be a setting that needs to be changed for this form to operate as intended. Please help.

      **********************************Additional Wrinkle***************************

      After posting this question, I tried one more thing. I clicked the "Enter New UCC Record" button to go to the UCC Detail page and then edited the Action on the "Return to UCC Tracker Report" button to clear the cache on this page. After that the PLSQL insert would not work.

      Edited by: Doug on Apr 27, 2012 3:45 PM
        • 1. Re: PLSQL Insert and Special Circumstance
          The Form page where you Create/Edit does not really come into play in the duplication of record process , unless you are trying to insert a row on that page (which would be bad idea) in the Page rendering side. Analyzing the UCC Detail page may offer clues, but it can also confuse.

          Chances are you are inserting a new record while navigating out of the UCC Tracker Report page in a OnSubmit process. And that would be the right thing to do.

          You also need to make a distinction between "Row not getting inserted in the table" and "Row not showing in the Edit (UCC Detai)". If the parameter passing from UCC Tracker Report to UCC Detail page is not correct you will not see the newly created row even though it is present in the table.

          The scenario you are describing is a routine one.

          If you put up this scenario on apex.oracle.com it will be possible to help fix it. Otherwise one can only guess !

          • 2. Re: PLSQL Insert and Special Circumstance
            Thanks for your reply. The problem is not that the record is being inserted but I am not seeing it. I have done SQLPlus checks to see that it is not inserted, except under the special circumstance described. I will do as you suggest and place the problem in the Apex sandbox. I'll let you know when it is ready. By the way, is there a way for me to create a common userid and password for other folks to use when entering the sandbox or do they have to use the same one that I use?
            • 3. Re: PLSQL Insert and Special Circumstance
              You can, and should, create additional user(s) using Admin > Manager Users.