[Zope] DCOracle problems after dropping/reloading tables

[Anthony Baxter]

Hi, 

This looks more like an Oracle problem than a Zope one. Try this:

Open an sqlplus session using the same connection string as your 
zope connector uses.

Do some of the same stuff that Zope does, to verify that it works.
Leave the sqlplus alive during your nightly refresh. 
After the drop/create is done, see if you can still do stuff in
sqlplus that the Zope server tries to do.

I'm guessing that the sqlplus will also be fragged. It may be possible
to do wierd Oracle magic to make existing clients work, or it may be
that you have to do something like:

make a method that just tests for the state of brokenness, called 'oraTest'
(for example).
Make a python method that does something like:
try:
    oraTest()
except: 
    close Oracle connector
    reopen Oracle connector
Then put this at the start of each request that might trigger the fault.

Anthony
[who's been constantly suprised/disappointed how much stuff in Oracle
that you'd _expect_ to be a "reference by name" is instead a "reference 
by some magic internal ID that you can't find mwahahahaha"]


>>> Mark Langkau wrote
> Hi,
> 
> We're using Zope 2.3.2 and DCOracle2 Beta-4 against a remote Oracle
> 8.1.6 database on RH Linux 6.2.
> 
> We have a data mart (MCMART) where tables and views are dropped and
> refreshed nightly. This seems to be the cause of the following error:
> 
> -- Error Message --
> 
> an exception occurred in a DTML method or document.
> 
> Error type: DatabaseError
> Error value: (4068, 'ORA-04068: existing state of packages has been
> discarded\012ORA-04061: existing state of package body
> "MCMART.MC_MART_RPT"
> has been invalidated\012ORA-04065: not executed, altered or dropped
> package
> body "MCMART.MC_MART_RPT"\012ORA-06508: PL/SQL: could not find program
> unit
> being called\012ORA-06512: at line 1')
> 
> Innermost method:
> /mc/mc_mart_dyn/mc_dd/daily_sales/show_daily_sales.dtml
> 
> ------------------
> If we reconnect to the Oracle database, the error goes away.
> 
> The connections from Zope to Oracle remain open during these nightly
> refreshes, and we are thinking this is causing the error. Is there a way
> to programatically close and reopen these connections? Or better yet,
> have Zope/DCO2 detect the error and try to close/reopen the connection?
> A reasonable solution might be to have the Oracle view refresh process
> use wget (or similar) to call a script telling DCO2 to close/reopen the
> database connection. (But then I'm not sure how to handle logging in, so
> Joe Hacker doesn't help me by doing this himself all day long ;-)
> 
> Has anyone seen this before? I see that a new version of DCO2 was
> released last night, but I haven't had time to check that out yet for
> new functionality. Have I overlooked something that is in the current
> version?
> 
> Thanks for your help.
> 
> Cheers,
> Mark
> 
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
> 

-- 
Anthony Baxter     <anthony@interlink.com.au>   
It's never too late to have a happy childhood.