Re: Oracle + PHP + long process

On Thu, 5 Oct 2006 15:03:17 +1000, Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> wrote:

>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
>overlooking?

 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 do.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool