Skip to Main Content
  • Questions
  • Advise for Analytics-related Workflow Automation

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sam.

Asked: November 01, 2017 - 6:10 pm UTC

Last updated: November 03, 2017 - 7:22 am UTC

Version: 11.2.0.4 Enterprise

Viewed 1000+ times

You Asked

Hello,

I work in the Analytics department where I support a team of many Data Scientists.

We use Oracle Database Enterprise v11.2.0.4 as our back-end database and I have developed several automation using PL/SQL procedures, functions, etc.

I have created a custom "workflow" table containing tasks as rows that are sorted in a specific sequence in order for the procedure to then "pick-up" these rows and start performing tasks like creating objects (table, mv or a view) dynamically or calling another procedure or a package from another user's schema, etc. All this is done via a scheduled job which invokes that procedure, in turn, the procedure loops through all the "tasks" it needs to perform and executes them dynamically without human intervention.


Typically, a Data Scientist will perform the following steps when working on a project:-

1. Create some tables (in Oracle database using the Oracle SQL Developer tool) after scrubbing raw data from internal or external source systems.

2. Read the contents of cleaned-up rows from these tables from Oracle using R or Python tools and perform further Statistical and Analytical processing. In many cases, the results of these Statistical analysis made in R or Python would result into creating "resultant" tables in Oracle using the R code or Python code by Data Scientist.

3. Use the reporting/dashboarding tools to then read from the "resultant" tables to create reports.


Using my custom "workflow" table and procedure method, I was wondering how I could enhance my code in PL/SQL such that it can swiftly call R or Python tool and provide the appropriate R or Python code file name that the Data Scientist wrote their code in and also, have PL/SQL procedure "wait" for these external programs to complete processing and creating "resultant" tables back into Oracle and somehow, get a "signal" that it is done, so that it proceeds to the next task.

Basically, I'm looking for a way to "stitch" the workflow so hand-off between Oracle and an external program/tool such that it waits to receive some sort of a "signal" that it is done, before the procedure loops through the next record.

Any advise, tips or ideas would help. My apologies for not supplying any PL/SQL code and I'm not expecting any code from your end as I'm just looking for some ideas or approaches that I could take before I code away.

Thanks in advance!

and Connor said...

Couple of options you could consider here

1) PL/SQL can call Java, which means you could have PL/SQL call a Java routine which then calls a host command, eg

https://asktom.oracle.com/pls/asktom/asktom.search?tag=running-os-commandprogram-from-java

https://asktom.oracle.com/pls/asktom/asktom.search?tag=java-procedure-for-host-calls-on-unix-environment-200010

2) External table

An external can call a pre-processor script. This was originally designed to format the incoming data, but of course, you can manipulate to run *anything* you want.

So a "select * from my_external_table" might really just be serving the purpose of run a script and the output from the query is simply the return code from the script.

3) Queues

Your PL/SQL program puts a message on a queue. Your host program connects to the database and listens "forever" on that queue and then commences when it gets a message

4) Scheduler

The job scheduler can create "chains" and "events" so tasks can be serialiazed and initiated when another one finishes, or a file comes into existence etc

Lots of options

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Giriraj, November 03, 2017 - 2:59 am UTC

Thanks for the info sir. Can we have some sample code which integrate plsql and python.
Connor McDonald
November 03, 2017 - 7:22 am UTC

See my other followup - any language can do the same

A reader, November 03, 2017 - 6:43 am UTC

Thanks Connor :)

I'll have to read up on Options 3 & 4 in more detail.

Any chance you have an example walk-through of Option 3? It's something entirely new to me.

Thanks!
Connor McDonald
November 03, 2017 - 7:21 am UTC

Some info on scheduler chains here

https://oracle-base.com/articles/10g/scheduler-enhancements-10gr2


For queueing, here is a simple example:

SQL> create or replace type payload as object
  2      ( message varchar2(200) );
  3  /

Type created.

SQL>
SQL> begin
  2     dbms_aqadm.create_queue_table (
  3        queue_table        => 'myqueue_tab',
  4        queue_payload_type => 'payload'
  5        );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2     dbms_aqadm.create_queue (
  3        queue_name  => 'myqueue',
  4        queue_table => 'myqueue_tab'
  5        );
  6
  7     dbms_aqadm.start_queue (
  8        queue_name => 'myqueue'
  9        );
 10  end;
 11  /

PL/SQL procedure successfully completed.


Now that my queue is created, I have a program that I want to run ...but only when (say) Stage 1 is complete. So I *start* that program, but it will wait on a message to arrive:

SQL> set serverout on
SQL> declare
  2     l_dequeue_options    dbms_aq.dequeue_options_t;
  3     l_message_properties dbms_aq.message_properties_t;
  4     l_message_handle     raw(16);
  5     l_payload            payload;
  6
  7  begin
  8     dbms_aq.dequeue(
  9        queue_name         => 'myqueue',
 10        dequeue_options    => l_dequeue_options,
 11        message_properties => l_message_properties,
 12        payload            => l_payload,
 13        msgid              => l_message_handle
 14        );
 15
 16     dbms_output.put_line(l_payload.message);
 17
 18  end;
 19  /
(waiting.....)


Now the other parts of my application are running etc, and when they are done, they will pop a message on the queue.

SQL> declare
  2     l_enqueue_options    dbms_aq.enqueue_options_t;
  3     l_message_properties dbms_aq.message_properties_t;
  4     l_message_handle     raw(16);
  5     l_payload            payload;
  6  begin
  7     l_payload := payload('Stage 1 complete');
  8     dbms_aq.enqueue(
  9        queue_name         => 'myqueue',
 10        enqueue_options    => l_enqueue_options,
 11        message_properties => l_message_properties,
 12        payload            => l_payload,
 13        msgid              => l_message_handle
 14        );
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


I flick over the other session, and voila ....

...
  8     dbms_aq.dequeue(
  9        queue_name         => 'myqueue',
 10        dequeue_options    => l_dequeue_options,
 11        message_properties => l_message_properties,
 12        payload            => l_payload,
 13        msgid              => l_message_handle
 14        );
 15
 16     dbms_output.put_line(l_payload.message);
 17
 18  end;
 19  /
Stage 1 complete

PL/SQL procedure successfully completed.

SQL>



There are also more advanced capabilities, eg, notification, which you can see an example of here:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=advanced-queuing-plsql-notification

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database