Run long Oracle stored procedure from PHP

Question

I have a stored procedure which I run from PHP using:

//Request does not change
$sql = 'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;';            

//Statement does not change
$stmt = oci_parse($conn,$sql);                     
oci_bind_by_name($stmt,':POP',$pop);           
oci_bind_by_name($stmt,':SEG',$seg);           
oci_bind_by_name($stmt,':DUR',$dur);           
oci_bind_by_name($stmt,':VIEW',$view);           
oci_bind_by_name($stmt,':PAGE',$page);    

//But BEFORE statement, Create your cursor
$cursor = oci_new_cursor($conn)

// On your code add the latest parameter to bind the cursor resource to the Oracle argument
oci_bind_by_name($stmt,":OUTPUT_CUR", $cursor,-1,OCI_B_CURSOR);

// Execute the statement as in your first try
oci_execute($stmt);

// and now, execute the cursor
oci_execute($cursor);

// Use OCIFetchinto in the same way as you would with SELECT
while ($data = oci_fetch_assoc($cursor, OCI_RETURN_LOBS )) {
    print_r($data}
}

The problem is I have millions of rows and complex logic in stored procedure. When I execute SP_GET_MY_DATA through SQL developer, it takes around 2 hours to complete it.

PHP is timing out when I do it. I cannot increase the max_execution_time in PHP as well.

How can I run this on Oracle or using PHP without timing out? Please help.

user_1680777 · Answer

I answered how to use Oracle Scheduler to run a long running procedure asynchronously pretty comprehensively in this answer on the DBA stack exchange. See https://dba.stackexchange.com/a/67913/38772

TL;DR is

-- submit this as a background job
BEGIN
  dbms_scheduler.create_job ( 
      job_name => 'MY_BACKGROUND_JOB'
    , job_type => 'STORED_PROCEDURE'    
    , job_action => 'SP_GET_MY_DATA'
    , enabled => TRUE
    , auto_drop => TRUE
  );
END;

You'll have to do a little more work if you want to pass in parameters to the procedure. You may find this answer helpful https://dba.stackexchange.com/q/42119/38772/

For additional reference with all the gory details, the relevant chapter from Oracle's documentation is at https://docs.oracle.com/database/121/ADMIN/scheduse.htm

user_8931764 · Answer

Don't increase max_execution_time, set it to 0, allow it to run indefinitely. If you are going to return loads of rows, make sure to either increase memory (ini_set) or allow immediate buffer flushing so that it can output directly to client.

The latter will also prevent clients from prematurely disconnecting because they didn't get any data. (ob_implicit_flush(true);)

Run long Oracle stored procedure from PHP

2 Answers