Oracle + PHP + long process

[Geoff Muldoon]

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

[Andy Hassall]

On Thu, 5 Oct 2006 15:03:17 +1000, Geoff Muldoon <ge***********@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 :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Colin McKinnon]

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.

[Mladen Gogala]

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