This discussion is archived
6 Replies Latest reply: Apr 11, 2008 7:15 AM by 629346 RSS

ERROR INSTALLING ILM 1.2

rafa Newbie
Currently Being Moderated
Hi,
We have tried to install ILM v 1.2 en we got the below issue:


Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY ILM_TOOLKIT.ILM_TOOLKIT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
16717/3 PL/SQL: SQL Statement ignored
16723/11 PLS-00414: no column 'RUNNING' in table

Package body created.

No errors.

Package body created.

No errors.

Context created.


Type body created.


Type body created.

No errors.
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "ILM_TOOLKIT.ILM_TOOLKIT" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
  • 1. Re: ERROR INSTALLING ILM 1.2
    543302 Newbie
    Currently Being Moderated
    I apologize for the slow reply. I thought I was set up to watch this thread, so I missed the posting.

    It sounds like we've embedded a reference to a database column that may only exist in 10g and above. ILMA is designed to install on 9i and above, so we have to be careful when referencing various metadata items that have emerged in later releases. In this case, we may have missed the reference.

    We will investigate this bug and get back to you as soon as possible.

    Thanks,

    Greg
  • 2. Re: ERROR INSTALLING ILM 1.2
    629346 Newbie
    Currently Being Moderated
    Hi,

    We were able to reproduce this error on a 9i installation. It seems that there was an issue with one of the 9i specific metadata tables. Although the features that use this table are disabled in 9i, there are still references to it in the package that were causing the build to fail. Our apologies for this oversight.

    There is a simple work around that will enable the install process to work properly -
    In create_meta92.sql, replace the definition of the ILM_SCHEDULER_JOB_RUN_DETAILS with the following -

    CREATE OR REPLACE FORCE VIEW "ILM_TOOLKIT"."ILM_SCHEDULER_JOB_RUN_DETAILS"
    (log_id, rec_id, script_id, log_date, owner,job_name, job_subname,
    status, error#, req_start_date, actual_start_date, run_duration,
    instance_id, session_id, slave_pid, cpu_used, destination,
    additional_info,table_owner,table_name,running)
    AS
    SELECT s.log_id, s.rec_id, s.script_id, s.log_date, s.owner,s.job_name, s.job_subname,
    s.status, s.error#, s.req_start_date, s.actual_start_date, s.run_duration,
    s.instance_id, s.session_id, s.slave_pid, s.cpu_used, s.destination,
    s.additional_info,s.table_owner,s.table_name,0
    FROM ilm_toolkit.ilm$_scheduler_job_run_details s
    WHERE (s.virtual = 1 and s.status = 'NOT SCHEDULED')
    OR (s.virtual = 2);

    Then run ilma_uninstall.sql to cleanup the previous failed installation, and rerun ilma_install.sql.
  • 3. Re: ERROR INSTALLING ILM 1.2
    FahdMirza Oracle ACE
    Currently Being Moderated
    I am getting the same problem while using Oracle 10g R1, can I use the same solution, Adam?

    rgards
  • 4. Re: ERROR INSTALLING ILM 1.2
    629346 Newbie
    Currently Being Moderated
    Each different version of Oracle uses its own specific view definition files, so for 10gR1 it is create_meta101.sql. There is a similar issue in that file that needs to be addressed. The views need to be changed to fill in dummy values for features that only exist in later versions. The definitions for the last 3 views in that file (ILM_SCHEDULER_*) should be changed to the following -

    CREATE OR REPLACE FORCE VIEW "ILM_TOOLKIT"."ILM_SCHEDULER_JOB_RUN_DETAILS"
    (log_id, rec_id, script_id, log_date, owner,job_name, job_subname,
    status, error#, req_start_date, actual_start_date, run_duration,
    instance_id, session_id, slave_pid, cpu_used, destination,
    additional_info,table_owner,table_name)
    AS
    SELECT s.log_id, s.rec_id, s.script_id, s.log_date, s.owner,s.job_name, s.job_subname,
    s.status, s.error#, s.req_start_date, s.actual_start_date, s.run_duration,
    s.instance_id, s.session_id, s.slave_pid, s.cpu_used, s.destination,
    s.additional_info,s.table_owner,s.table_name
    FROM ilm_toolkit.ilm$_scheduler_job_run_details s
    WHERE (s.virtual = 1 and s.status = 'NOT SCHEDULED')
    OR (s.virtual = 2);

    create or replace force view ilm_toolkit.ilm_scheduler_chains
    (owner_name,chain_name)
    as select null,NULL
    from dual
    where 1 = 0;

    create or replace force view ilm_toolkit.ilm_scheduler_steps
    (owner,chain_name,step_name,program_name)
    as select null,NULL,null,null
    from dual
    where 1 = 0;

    Message was edited by:
    Adam S
  • 5. Re: ERROR INSTALLING ILM 1.2
    FahdMirza Oracle ACE
    Currently Being Moderated
    Adam, the problem still persists, and the only difference is that now there are more errors. I have run the script given by you , and then have uninstalled the ILMA, and when I reinstall it, the above errors repeat and after that some new errors are displayed and the installation process terminates.

    The new errors are:

    126/3 PL/SQL statement ignored

    127/22 PLS00905: object ILM.Toolkit.ILM_Schedule_job_run_detail is invalid.

    Please help in this regard or tell me which version of database and apex run without such errors with ILM?.

    regards
  • 6. Re: ERROR INSTALLING ILM 1.2
    629346 Newbie
    Currently Being Moderated
    Did you replace the view definitions in the script create_meta101.sql or just ran what I had posted as a separate script? The install process will call create_meta<XXX>.sql based on your database version, so unless you modified that file, the old incorrect view definitions will be used again. If you uninstall ILMA, modify that file, and reinstall, everything should work properly.

    Adam