6 Replies Latest reply: Apr 11, 2008 9:15 AM by 629346 RSS

    ERROR INSTALLING ILM 1.2

    Blade
      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
          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
            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
              Fahd.Mirza
              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
                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
                  Fahd.Mirza
                  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
                    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