This discussion is archived
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 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points