12.7.3.1 oml_async_flag Argument

The special control argument oml_async_flag determines if a job is run synchronously or asynchronously. The default value is false.

Set the oml_async_flag Argument

• To run a function in synchronous mode, set oml_async_flag to false.

  In synchronous mode, the SQL API waits for the HTTP call to finish and returns when the HTTP response is ready.

  By default, pyq*Eval functions are executed synchronously. The default connection timeout limit is 60 seconds. Synchronous mode is used if oml_async_flag is not set or if it's set to false.

• To run a function in asynchronous mode, set oml_async_flag to true.

  In asynchronous mode, the SQL API returns a URL directly after the asynchronous job is submitted to the web server. The URL contains a job ID, which can be used to fetch the job status and result in subsequent SQL calls.

Submit Asynchronous Job Example

This example uses the table GRADE, created as follows:

CREATE TABLE GRADE (
NAME VARCHAR2(30),
GENDER VARCHAR2(1),
STATUS NUMBER(10),
YEAR NUMBER(10),
SECTION VARCHAR2(1),
SCORE NUMBER(10),
FINALGRADE NUMBER(10)
);

insert into GRADE values('Abbott', 'F', 2, 97, 'A', 90, 87);
insert into GRADE values('Branford', 'M', 1, 98, 'A', 92, 97);
insert into GRADE values('Crandell', 'M', 2, 98, 'B', 81, 71);
insert into GRADE values('Dennison', 'M', 1, 97, 'A', 85, 72);
insert into GRADE values('Edgar', 'F', 1, 98, 'B', 89, 80);
insert into GRADE values('Faust', 'M', 1, 97, 'B', 78, 73);
insert into GRADE values('Greeley', 'F', 2, 97, 'A', 82, 91);
insert into GRADE values('Hart', 'F', 1, 98, 'B', 84, 80);
insert into GRADE values('Isley', 'M', 2, 97, 'A', 88, 86);
insert into GRADE values('Jasper', 'M', 1, 97, 'B', 91, 83);

In the following code, the Python function score_diff is defined and stored with the name computeGradeDiff as a private function in the script repository. The function returns a pandas.DataFrame after assigning a new DIFF column by computing the difference between the SCORE and FINALGRADE column of the input data.

begin
    sys.pyqScriptCreate('computeGradeDiff','def score_diff(dat):
        import numpy as np
        import pandas as pd
        df = dat.assign(DIFF=dat.SCORE-dat.FINALGRADE)
        return df
    ');
end;
/

Run the saved computeGradeDiff script as follows:

select *
   from table(pyqTableEval(
         inp_nam => 'GRADE',
         par_lst => '{"oml_async_flag":true}',
         out_fmt => NULL,
         scr_name => 'computeGradeDiff',
         scr_owner => NULL
   ));

The VALUE column of the result contains a URL containing the job ID of the asynchronous job:

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
https://<oml-cloud-service-location-url>/oml/api/py-scripts/v1/jobs/<job id>
1 row selected.