Oracle + PHP + long process

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi all,

Oracle 10g RAC on Linux, with an end-user interface via Apache/PHP/AdoDB  
on a separate Linux box.

I need to create a PHP web interface to allow an end-user to run a PL/SQL  
stored proc that will do a force refresh of a series of materialized views  
following the loading (through another PHP interface) of additional flat  
file data into the underlying tables.  I want to allow multiple data  
uploads and then do a single refresh, so I won't be able to use fast  
refresh or other internal database trigger mechanisms.

My real problem is that the time taken to execute the stored proc is  
likely to considerably exceed the max script execution time for PHP, and I  
don't want the end-user interface to just sit there bubbling away waiting  
for the Oracle processes to return a response anyway.

I am considering creating a "master" PL/SQL stored procedure which would  
use the Oracle DBMS_SCHEDULER package to run a nearly-immediate job as a  
"background" (to the PHP interface) process.

Is this the way to go, or are there other better solutions that I am  


Geoff M

Re: Oracle + PHP + long process


Quoted text here. Click to load it

 Sounds like a reasonable approach to me; I was thinking of DBMS_JOB whilst
reading this, but since you're on 10g then DBMS_SCHEDULER is better. You may be
able to monitor the progress of the refresh through v$session_longops - I don't
remember whether mview refreshes show up in there, though - not all operations

Andy Hassall :: :: :: disk and FTP usage analysis tool

Re: Oracle + PHP + long process

On Thu, 05 Oct 2006 15:07:11 +0100, Andy Hassall wrote:

Quoted text here. Click to load it

I second that. DBMS_SCHEDULER is the way to go. Unfortunately,
materialized view refreshes do not show as longops.


Re: Oracle + PHP + long process

Geoff Muldoon wrote:

Quoted text here. Click to load it
Quoted text here. Click to load it

You mean apart from  
1) not using materialized views
2) ditching Oracle and rewriting with just about any other DBMS ?  

You could look at the thread started slightly later today in comp.lang.php
'process a BIG string' but using the DBMS scheduler is probably the
practical way to go.


Site Timeline