Oracle + PHP + long process

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Geoff Muldoon
    #1

    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

    Tags: None
    • Andy Hassall
      #2
      Re: Oracle + PHP + long process

      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_longo ps - I don't
      remember whether mview refreshes show up in there, though - not all operations
      do.

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

        Comment

        • Colin McKinnon
          #3
          Re: Oracle + PHP + long process

          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.

            Comment

            • Mladen Gogala
              #4
              Re: Oracle + PHP + long process

              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_longo ps - 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


                Comment

                Previous template Next
                Working...