We help IT Professionals succeed at work.

Package not recompiled automatically when calling Oracle package from IIS web server

Hi,

I have one problem. We are running oracle database 11g, and IIS web server on windows 2003 server. The web server talk to oracle throug one package web_package. Every time we compile other package other_package that the web_package depends on we get the ORA-06508 PL/SQL could not find program unit being called.
The other_package is tho valid. The only way to fix this error is to restart the IIS service.

Can you help me, why the other_package is not autiomatic recompiled when the web server calls the oracle web_package ?
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
From some old C code on a project I once worked on I have seen some race conditions where the code would not wait the few seconds/milli-seconds it took to recompile an invalid package.

The programmers never coded for that time and it died every time the package invalidate itself.

You might be running into a similar situation.  I would probably work directly with Oracle Support to start tracing and see if this can be confirmed.
Mark GeerlingsDatabase Administrator
SILVER EXPERT
Commented:
I have also seen cases where the automatic recompile did not work as expected when a non-Oracle client tool was involved.  With Oracle client tools, the automatic recompile usually works as expected.  I've never figured out why.
Jayesh AcharyaTechnichal Consultant
Commented:
when you compile , maybe recompile the invalid objects withi type of script:

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/


OR you could use the oracle util to recompile teh whoel schema,

EXEC UTL_RECOMP.recomp_serial('SCOTT');

but just be carful when recompileing the schema or database

for further reading you can go to :
http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

hope this helps

Author

Commented:
Did not solve my case, sorry.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.