Apache::DBI and altered packages

[Geoffrey Young <gy...@laserlink.net>]

hi all...

I was wondering if anyone has found a good way around persistent connections
and package recompiles.  With Apache::DBI, on occasion when someone
recompiles a package and doesn't tell me, I see

ORA-04061: existing state of package body "FOO.BAR" has been invalidated
ORA-04065: not executed, altered or dropped package body "FOO.BAR"
ORA-06508: PL/SQL: could not find program unit being called

my Oracle gurus here tell me that whenever a package changes any open
connections will get this error.  Since the connection itself ok (just not
the stuff I need to use) the only solution currently available seems to be
$r->child_terminate() so that at least that child doesn't barf every time.
However, this leaves the current request in a lurch...

I was thinking that maybe something like Apache::DBI::reconnect() was needed
- call DBI->disconnect then DBI->connect again.

that is unless someone has dealt with this in another way - someone
mentioned that perhaps the proper ALTER OBJECT permissions would force a
recompile - I dunno...

--Geoff

Re: Apache::DBI and altered packages

[Perrin Harkins <pe...@primenet.com>]

> my Oracle gurus here tell me that whenever a package changes any open
> connections will get this error.  Since the connection itself ok (just not
> the stuff I need to use) the only solution currently available seems to be
> $r->child_terminate() so that at least that child doesn't barf every time.
> However, this leaves the current request in a lurch...
>
> I was thinking that maybe something like Apache::DBI::reconnect() was
needed
> - call DBI->disconnect then DBI->connect again.

If you change the ping() method to check whether or not your package still
works, Apache::DBI will automatically get a new connection when it fails.

- Perrin

Re: Apache::DBI and altered packages

[jared still <jk...@teleport.com>]

Geoff,

I'm going to make a non-technical suggestion:  You need to have  
change control implemented so that packages in your database
are not changing during business hours.

If you are 24x7, then you need to schedule an outage, and notify
your customers of scheduled downtime.

This is more of a management and logistics problem than a Perl
or Apache or Oracle problem

On Fri, 22 Dec 2000, Geoffrey Young wrote:

> I was wondering if anyone has found a good way around persistent connections
> and package recompiles.  With Apache::DBI, on occasion when someone
> recompiles a package and doesn't tell me, I see
> 
> ORA-04061: existing state of package body "FOO.BAR" has been invalidated
> ORA-04065: not executed, altered or dropped package body "FOO.BAR"
> ORA-06508: PL/SQL: could not find program unit being called
> 
> my Oracle gurus here tell me that whenever a package changes any open
> connections will get this error.  Since the connection itself ok (just not
> the stuff I need to use) the only solution currently available seems to be
> $r->child_terminate() so that at least that child doesn't barf every time.
> However, this leaves the current request in a lurch...
> 
> I was thinking that maybe something like Apache::DBI::reconnect() was needed
> - call DBI->disconnect then DBI->connect again.
> 
> that is unless someone has dealt with this in another way - someone
> mentioned that perhaps the proper ALTER OBJECT permissions would force a
> recompile - I dunno...
> 
> --Geoff

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;)
jkstill@teleport.com
jared_still@enron.net