Oracle + PHP + long process

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

TIA

Geoff M

On Thu, 5 Oct 2006 15:03:17 +1000, Geoff Muldoon <geoff.muldoon@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

Geoff Muldoon wrote:

>
> Oracle 10g RAC on Linux, with an end-user interface via Apache/PHP/AdoDB
> on a separate Linux box.
>
<snip>
>
> 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?
>

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.

C.

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

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

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

--
http://www.mladen-gogala.com