8 Replies Latest reply on Jan 12, 2019 5:54 PM by fimi

    report errors with triggers

    fimi

      I'm developing on oracle.apex.com. All my triggers show two different report errors (Object Browser / Triggers / Object Details / Columns).

      These are

                       ORA-00040: active time limit exceeded - call aborted and

                       ORA-56720: I/O data limit exceeded - call aborted

       

      When i click on errors appears 'this trigger has no errors' and compilation says the same.

       

      I have no idea what i did wrong and how to get rid from the report errors. What does this mean 'call aborted'? Can the trigger not be executed correctly?

       

      Would be so nice to get some help.

       

      Michael

        • 1. Re: report errors with triggers
          fac586

          fimi wrote:

           

          I'm developing on oracle.apex.com. All my triggers show two different report errors (Object Browser / Triggers / Object Details / Columns).

          These are

          ORA-00040: active time limit exceeded - call aborted and

          ORA-56720: I/O data limit exceeded - call aborted

           

          When i click on errors appears 'this trigger has no errors' and compilation says the same.

          These are runtime rather than compilation errors and are therefore not reported by the compiler.

          I have no idea what i did wrong and how to get rid from the report errors. What does this mean 'call aborted'? Can the trigger not be executed correctly?

          Exactly that. What are the triggers attempting to do? Post the trigger code (as text, not screenshots).

          • 2. Re: report errors with triggers
            fimi

            All my triggers are built like this one:

             

            CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_HH_ENERGY_USAGE_TAB" 
              before insert on "HH_ENERGY_USAGE_TAB"              
              for each row 
            begin  
              if :NEW."EU_ID" is null then
                select "HH_ENERGY_USAGE_TAB_SEQ".nextval into :NEW."EU_ID" from sys.dual;
              end if;
            end;

            /
            ALTER TRIGGER  "BI_HH_ENERGY_USAGE_TAB" ENABLE
            /

            and additional a screenshot

            • 3. Re: report errors with triggers
              fac586

              fimi wrote:

              The ORA-00040: active time limit exceeded - call aborted error is caused by query execution time exceeding the limits imposed by Resource Manager policies on apex.oracle.com. This is a frequent occurrence on this instance when running queries against the APEX metadata views, and now apparently also on database data dictionary views. This may be partially attributable to known performance issues, but it must be borne in mind that as far as anyone outside of Oracle is concerned, apex.oracle.com is neither a production system nor a full development environment and should not be expected to function as such.

               

              I experienced this error earlier today on the APEX template utilization report.

               

              joelkallman-Oracle: Should this be expected from APEX/data dictionary-based reports with the current Resource Manager settings on apex.oracle.com?

              • 4. Re: report errors with triggers
                joelkallman-Oracle

                Hi Paul,

                 

                Thank you for raising this issue.  I hate to say it, but this is not an easy fix.  If we relax the resource plan too much, then that could impact the rest of the instance.  There are some data dictionary queries (like this one) which we've tried to optimize but still have work to do - especially on such a large data dictionary as the one on apex.oracle.com.  Hopefully, when we rearchitect apex.oracle.com in 2019, this will result in a much smaller data dictionary and we won't run into issues like this one.

                 

                Happy New Year!

                 

                Joel

                • 5. Re: report errors with triggers
                  Scott Wesley

                  As an alternative, this is now possible on apex.oracle.com, if your system is also able to use 12c features

                   

                  create sequence seq_test;
                  create table tab_seq_test  (id number default seq_test.nextval);
                  

                   

                  No need for trigger.

                  • 6. Re: report errors with triggers
                    fimi

                    Hi Scott,

                     

                    i had the same idea and will start now to replace all my triggers.

                     

                    Thank for the help to all, I have learned a lot by this discussion.

                     

                    Michael

                    • 8. Re: report errors with triggers
                      fimi

                      Hi Scott,

                       

                      you have written

                       

                      I'm developing on apex.oracle.com. My applications are built in the embedded app builder, so i don't have a system on my own.

                       

                      NO need for trigger. I created the sequence and the table to which i added a column named note

                      By using a trigger an entry in note and click on insert generates a new line.

                       

                      Without a trigger i get a new line only with a manual entry in the id column.

                      The alternative by sven weller didn't work as well.
                      This is really not the way i looked for after all my triggers have reported errors.

                       

                      Can you please advice me how to get values in the pk-column without triggers. As i've learned earlier these report errors are not to fix without rearchitectering apex.oracle.com. A big challenge.

                      Hope still to continue my work on this wonderful platform.

                       

                      Michael