Forum OpenACS Q&A: Bizarre Oracle behavior - package problems

I added a function, content_revision.revision_name(), to acs-content-repository a few days ago.  Over the course of this work I kept finding that the content_item package was invalid, but I thought it was just the result of my work.

Now, however, I'm finding that it seems to come and go.  Earlier today I did something in bug-tracker and got the error, so I ran the packages-create.sql script from acs-content-repository. This recreates all those packages, and it fixed the problem.

An hour or so later I was back in bug-tracker and got it again.  No-one else works on this site so I know for sure there were no code changes to those packages.  This time I hit the back button and re-submitted - no error this time.  This is really funky! I've never seen an invalid package just "fix" itself, and I don't know if it means I have an Oracle problem or what.

This is Oracle 8.1.7.4 running on RedHat Advanced Server, which is based on RH 7.2.  The exact error message follows.  Does anyone have any clues as to what problem would cause this behavior?

ora8.c:3568:ora_tcl_command: error in `OCIStmtExecute ()': ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "BP_STAGING.CONTENT_ITEM" has been invalidated
ORA-04065: not executed, altered or dropped package "BP_STAGING.CONTENT_ITEM"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "BP_STAGING.CONTENT_REVISION", line 25
ORA-06512: at "BP_STAGING.CONTENT_REVISION", line 69
ORA-06512: at "BP_STAGING.BT_BUG_REVISION", line 22
ORA-06512: at line 3

SQL:
        begin
            :1 := bt_bug_revision.new (
                bug_revision_id =>  null,
                bug_id =>          :bug_id,
                component_id =>    :component_id,
                found_in_version => :found_in_version,
                fix_for_version =>  :fix_for_version,
                fixed_in_version => :fixed_in_version,
                resolution =>      :resolution,
                user_agent =>      :user_agent,
                summary =>          :summary,
                creation_date =>    sysdate,
                creation_user =>    :creation_user,
                creation_ip =>      :creation_ip
            );
        end;

Collapse
Posted by Janine Ohmer on
This, from a Metalink forum post, explains why the error goes away:

"Could you catch ORA-4068 error and repeat the code? I mean it is like this ...  you execute a procedure, if it is invalid, oracle will throw 4068. You check the error, if it matches 4068, re-execute the procedure, this will force a automatic recompilation and the execution will resume. "

But it does not explain why the package keeps on going invalid when no-one is changing anything...

Collapse
Posted by Dirk Gomez on
Is someone/something else changing some other procedure, view, table, whatever in the dependancy chain for this package?
Collapse
Posted by Janine Ohmer on
Definitely not someone else - I'm the only one working on this system.  Something else in the code could be, I suppose, but nothing I'm aware of.  Does anyone know of anything going on in the CR which could cause this?
Collapse
Posted by Tom Jackson on

Doesn't the CR rewrite pl/sql on every update to the datamodel? I think it replaces views, etc.

Collapse
Posted by Dirk Gomez on
From just looking at the code it seems that upon creating or dropping types or attriutes dynamic SQL is being executed which creates, drops or alters tables. (And some more DDL even...)

Look at the log file and try to locate procs from packages/acs-content-repository/sql/oracle/content-type.sql

Collapse
Posted by Jun Yamog on
Hi Tom,

I think that happens on PG, its was a brilliant hack by DanW to mimick the behaviour of Oracle.  I am not sure if this is the case for Oracle.  Maybe DanW can give some inputs, he is still the guy that pretty much is familiar with CR data model.

Collapse
Posted by ABHISHEK TANWAR on
ThE packages are invalidated when the user wants to make an attempt to the package specification in the same session. As the specification is compiled only once.. so when recompiling the same package spec..again in the same session..its state becomes invalid..

One Solution may be.. recompile the package using dynamic sql..