12 Replies Latest reply: Nov 20, 2009 2:29 AM by 732136 RSS

    ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.4)

    732136
      Hi,

      I'm having the following error when I scan 3 tables in Lifecycle Events Calendar (ILM 1.4).

      ORA-12012: error on auto execute of job 242
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "ILM_TOOLKIT.ILM_TOOLKIT", line 3968
      ORA-06512: at line 1

      Because of this I can´t generate lifecycle events to implement for these 3 tables.

      I appreciate your help to solve this problem.

      Nuno
        • 1. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
          Klaker-Oracle
          Which database version are you using?
          Which version of APEX are you using?
          Are you trying to run the ILM Assistant against your own schema or are you using this with the demo schema?

          I presume the installation of ILM Assistant 1.4 completed without errors?

          Keith
          • 2. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
            732136
            Hi,

            Thank You for Your reply.

            Yes, the instalation was completed without errors.

            The Database version is 10.2.0.3.0 and APEX is 3.2.1.00.10.

            No, I'm not using against the demo schema. Unfortunately this happened in a test environment and because of this the
            project for table lifecycle management is stopped.

            I have 34 lifecycle tables and that error only ocurs when I scan those 3 specific tables.

            Probably this is a bug and I expect that Oracle give support to analyze this issue. I was told that ILM has no support inside the Global Customer Services.


            Nuno
            • 3. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
              Klaker-Oracle
              The ILM Assistant is not supported by Oracle Support. It is a free application that our development team created to help manage the ILM process.

              Can you remove the three tables that are causing the error from your scan and determine if the scan will then complete successfully. The next step is to try and determine what is different about these three specific tables compared to the other tables in your schema.

              Keith
              • 4. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                732136
                Hi,

                When I remove those tables from lifecycle the scan completes sucessfully.

                The only thing those 3 tables have in common is the long table names. One has 29 characters and the other two have 30. Probably the problem is related to
                the table name length.

                I made a simple test:

                - Created a table with a table name of 28 characters and included it in the ILM.
                - The scan for events in Lifecycle Events Calendar ended ok.
                - Droped the table
                - Created it again with a table name of 29 characters and included it in the ILM.
                - The scan for events in Lifecycle Events Calendar ended with ORA-06502.

                Probably that package does not support table names greater than 28 characters.


                Nuno
                • 5. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                  Klaker-Oracle
                  Thanks for isolating the problem. Next step is to see if we can get this fixed ASAP. I will keep you posted on progress.

                  Keith Laker | Senior Principal Product Manager | Data Warehousing
                  Oracle Data Warehouse, Database Product Management
                  Southgate Centre Two, 321 Wilmslow Road, Heald Green, Cheadle, Cheshire. SK8 3PW
                  • 6. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                    732136
                    Hi,

                    Can You tell when the fix will be avalable?

                    Regards,

                    Nuno
                    • 7. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                      Klaker-Oracle
                      I tested the fix earlier this week so we are now in the process of packaging this up for posting on OTN. I am not sure when we will be able to update the OTN link with the new version but it should be relatively soon.

                      Keith
                      • 8. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                        732136
                        Hi,

                        For this database there is a way to fix this problem without reinstalling the ILM?
                        If the problem is only in that package, can You provide the new version of it to replace the old one?

                        Regards,

                        Nuno
                        • 9. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                          732136
                          Hi,

                          I was implementing the accepted recommendations for the other tables and suddenly appeared more errors, this time in the
                          Lifecycle Events Calendar (not in the alert log) and when selecting tables for implemmenting accepted recomendations (Implement Recommendations screen).


                          Examples:


                          Lifecycle Events Calendar screen
                          #########################

                          Overdue Events
                          --------------------------------------------------------------------------------

                          report error:
                          ORA-06502: PL/SQL: numeric or value error: NULL index table key value

                          --------------------------------------------------------------------------------
                          Today's Events
                          --------------------------------------------------------------------------------

                          report error:
                          ORA-06502: PL/SQL: numeric or value error: NULL index table key value




                          Implement Recommendations screen
                          ################################


                          Generate implementation script for accepted recommendations.

                          Script execution option Save SQL script to a file
                          Submit SQL script to a queue for execution Tables to include in script All Tables                                                                  Selected Tables

                          Script execution date and time



                          Selected Tables

                          report error:
                          ORA-06502: PL/SQL: numeric or value error: NULL index table key value



                          Apparently this error has another origin.


                          Regards,

                          Nuno
                          • 10. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                            732136
                            Hi,

                            Today I did the same procedure and now the error didn´t appear. I tried to implement some recommendations and it worked! The ILM Assistant is having a very stange behaviour. Can You explain what is happening?

                            Regards,

                            Nuno
                            • 11. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                              Klaker-Oracle
                              First I would download the latest version of the ILM Assistant which we posted last night on OTN. There is a small problem with the PDF install guide which we are trying to fix but the separate link on the download page on OTN has a working PDF file you can use to help with the new installation.

                              In relation to the latest error, I have no idea why you are getting the error message you posted. Is there any additional information in the alert log?
                              • 12. Re: ORA-06502 when scanning for events in Lifecycle Events Calendar (ILM 1.
                                732136
                                Hi,

                                Thank You for your reply.
                                After scaning for new events, I tried this morning to implement more recommendions and
                                when i hit "Selected Tables" radio button (Implement Recommendations screen) the latest error apperad again. If I choose
                                "All Tables" and then submit the script the following error appear:


                                ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_JOB_STEP_CF'

                                A severe error has occurred. Possible causes may
                                include moving directly to a page using the browser
                                history or Back key, multiple users attempting to change
                                the same data or an application defect. Please visit the
                                Support page under the Help Tab to report the error.


                                I didn't use the browser history or the back key and I was the only user in the ILM Assistant.

                                The alert log does not have any errors in this period.

                                Regards,

                                Nuno Vaz