Calling Python from Oracle

Question

Is it possible to call Python within an Oracle procedure? I've read plenty of literature about the reverse case (calling Oracle SQL from Python), but not the other way around.

What I would like to do is to have Oracle produce a database table, then I would like to call Python and pass this database table to it in a DataFrame so that I could use Python to do something to it and produce results. I might need to call Python several times during the Oracle procedure. Does anyone know if this is possible and how could it be done?

Solution 1

You can write stored procedures in Java and you can use Java to run Python code, so you can possibly combine the two to achieve what you want.

Solution 2

On the edge there is a possibility on how to overcome the PL/SQL limitations. You can design a specific interface between Database and Python program. I suppose You'd use one of the Python's library to get some data from the Net. And then exchange it's data with Oracle using the C Library.

call python using c library -> data file -> external table -> data

NOTICE: Take it as a proof of concept or rather starting point for deeper exploration. Also I'd strongly discourage You from using it on production. Breaking the PL/SQL jail to call system program could be considered at least as unsafe.

So this is the possible way on how to proceed:

--== Prerequisities ==--

pip install quandl

--== quandl.py ==--

#!/usr/bin/python
import quandl
# World Bank Education Statistics
# Population, tertiary, total - Czech Republic
data = quandl.get("WEDU/CZE_SP_TER_TOTL_IN")
data.to_csv("/u01/data/data.txt")

--== exec.c ==--

//
// gcc -Wall -fPIC -c exec.c
// gcc -shared -o exec.so exec.o
// mkdir -p /u01/lib
// cp exec.so /u01/lib
//

#include <stdlib.h>

int execute() {
  system("/u01/bin/get_data.py");
  return 0; // We want to make the compiler happy
}

--== LISTENER CONFIGURATION ==--

SID_LIST_LISTENER =
...
  (SID_DESC =
...
    (ENVS="EXTPROC_DLLS=ANY")
    (PROGRAM = extproc)
...

--== DDL PART ==--

create or replace library c_exec is '/u01/lib/exec.so';

create or replace procedure exec as external
  name "execute"
  library c_exec
  language c;
/

create directory pydata as '/u01/data';

create table data (
  "date" varchar2(14),
  "value" varchar2(32)
) organization external (  
  type oracle_loader
  default directory pydata
  access parameters ( 
    records delimited by newline
    nobadfile nodiscardfile nologfile
    fields terminated by ','
   ) location (pydata:'data.txt')
  );

---=== USAGE ===---

--== DOWNLOAD DATA FOR PROCESSING ==--

Using the external PL/SQL C library You would call the python program that stores the result to the expected location for the external table.

execute exec;  

--== QUERY THE DATA ==--

select 
  to_date("date",'yyyy-mm-dd') "date", 
  to_number("value") "value" 
from data 
  where "date" != 'Date';

--== RESULT ==--

date           value
--------- ----------
31-DEC-70     886414
31-DEC-71     885549
31-DEC-72     877533
31-DEC-73     862859

Solution 3

Well, there are a lot of different answers, with some very good options, but let me try to propose another one.

Let's imagine this scenario:

• I have a set of python programs that interact with data in different ways, you mentioned data frames.
• I have a big Oracle procedure that during runtime, needs to run the python scripts, so basically I need to use Python inside Oracle PL/SQL, which is not possible unless you use external libraries or Java code ( examples already provided )

What you can do always is calling SHELL SCRIPTS from PL/SQL using the API of DBMS_SCHEDULER. Those shell scripts can called whatever you want to, in this case Python programs.

My scenario is as follows:

• One Python program running the function to get the result set of a sys_refcursor variable.
• One Oracle Procedure calling those Python programs by a generic shell script

Let's make it work

SQL> create table t_python ( c1 number generated by default on null as identity ( start with 1 increment by 1 ) ,
                        c2 varchar2(10) ,
                        c3 date
                       ) ;             

Table created.

SQL> declare
begin
   for r in 1..10
   loop
      insert into t_python values ( null , dbms_random.string('A','5') , sysdate - round(dbms_random.value(1,100),0) );
          commit ;
   end loop;
end;
/  

PL/SQL procedure successfully completed.

SQL> select * from t_python
  2  ;

        C1 C2         C3
---------- ---------- ---------
         1 Anrio      14-JUL-20
         2 ouaTA      04-MAY-20
         3 Swteu      06-JUL-20
         4 kdsiZ      24-MAY-20
         5 PXxbS      14-MAY-20
         6 xQFYY      18-JUN-20
         7 oahQR      09-MAY-20
         8 ZjfXw      24-MAY-20
         9 AmMOa      26-JUL-20
        10 IQKpK      25-JUL-20

10 rows selected.

SQL>

So, lets imagine I have a function in the database that returns a SYS_REFCURSOR object, so a collection or dataset.

SQL> CREATE OR REPLACE FUNCTION get_result_table_f RETURN SYS_REFCURSOR
AS
   r_python SYS_REFCURSOR;
BEGIN
   OPEN r_python FOR 
   SELECT 
      c1,
      c2,
      c3
   FROM 
      t_python 
    ORDER BY 
         c1,   
         c2,
         c3;

   RETURN r_python;
END;
/

Function created

If I call this function with my python program, it works perfect.

import cx_Oracle
import pandas as pd

conn = cx_Oracle.connect('user/pwd@hostname:port/servicename')
cur = conn.cursor()

refCursor = cur.callfunc('get_result_table_f', cx_Oracle.CURSOR, [])
for row in refCursor:
    print(row)

Result

$ /usr/bin/python3.6 /home/myuser/testcursor.py
(1, 'Anrio', datetime.datetime(2020, 7, 14, 12, 38, 52))
(2, 'ouaTA', datetime.datetime(2020, 5, 4, 12, 38, 52))
(3, 'Swteu', datetime.datetime(2020, 7, 6, 12, 38, 52))
(4, 'kdsiZ', datetime.datetime(2020, 5, 24, 12, 38, 52))
(5, 'PXxbS', datetime.datetime(2020, 5, 14, 12, 38, 52))
(6, 'xQFYY', datetime.datetime(2020, 6, 18, 12, 38, 52))
(7, 'oahQR', datetime.datetime(2020, 5, 9, 12, 38, 52))
(8, 'ZjfXw', datetime.datetime(2020, 5, 24, 12, 38, 52))
(9, 'AmMOa', datetime.datetime(2020, 7, 26, 12, 38, 52))
(10, 'IQKpK', datetime.datetime(2020, 7, 25, 12, 38, 52))

So, how can I call this python program within my oracle procedure ?

Well, my option is using the API of DBMS_SCHEDULER, which only requires a shell script to invoke the python program. In order to setup DBMS_SCHEDULER, you onlz need to :

• Create a credential that the scheduler will use to run your shell. It must be an OS user ( In my example below is ftpcpl ).
• Use the scheduler job type EXTERNAL SCRIPT
• Use a Shell script to call the python program ( the python script must in the same server as the database. Is there an option for doing in another server, but it is more complicated because you need to install the Oracle scheduler agent )

This is how it should look like

create or replace procedure run_python_program 
as
v_job_count  pls_integer;
v_owner      varchar2(30);
v_job        varchar2(120) := 'MY_PYTHON_SCRIPT';
begin
    select count(*) into v_job_count from dba_scheduler_jobs where job_name = v_job ;
    if v_job_count > 0
    then
        DBMS_SCHEDULER.drop_job (job_name=> v_job , force => true);
    end if;

    DBMS_SCHEDULER.create_job
    (
        job_name             =>  v_job,
        job_type             => 'EXTERNAL_SCRIPT',
        job_action           => '/home/myuser/my_shell_script.sh `date +%Y%m%d`',
        credential_name      => 'ftpcpl',
        enabled              =>  FALSE
    );
    DBMS_SCHEDULER.run_job (job_name=> v_job, use_current_session => true);
exception when others then raise;
end;
/

You shell script as easy as it seems

#/bin/bash 
odate=$1
logfile=/home/myuser/logfile_$odate.txt 
/usr/bin/python3.6 /home/myuser/testpython.py >> $logfile

Run the procedure

SQL> begin
     run_python_program; 
     end;
     /

 PL/SQL procedure successfully completed. 


 SQL> host cat /home/test/logfile_20200809.txt
    (1, 'Anrio', datetime.datetime(2020, 7, 14, 12, 38, 52))
    (2, 'ouaTA', datetime.datetime(2020, 5, 4, 12, 38, 52))
    (3, 'Swteu', datetime.datetime(2020, 7, 6, 12, 38, 52))
    (4, 'kdsiZ', datetime.datetime(2020, 5, 24, 12, 38, 52))
    (5, 'PXxbS', datetime.datetime(2020, 5, 14, 12, 38, 52))
    (6, 'xQFYY', datetime.datetime(2020, 6, 18, 12, 38, 52))
    (7, 'oahQR', datetime.datetime(2020, 5, 9, 12, 38, 52))
    (8, 'ZjfXw', datetime.datetime(2020, 5, 24, 12, 38, 52))
    (9, 'AmMOa', datetime.datetime(2020, 7, 26, 12, 38, 52))
    (10, 'IQKpK', datetime.datetime(2020, 7, 25, 12, 38, 52))

SUMMARY

Keep in mind that I did a very easy and simple test just to show you just how to call python ( embedded into shell script ) from PL/SQL. Actually, you can make the procedure to run several external scripts ( python programs ) and you can interact with the data in several ways.

For example, you could do this:

1. A procedure in Oracle creates data and stores this data in a table , collection or sys_refcursor object. I can call the python program within the PL/SQL using the DBMS_SCHEDULER EXTERNAL_SCRIPT job type and interact with the data.
2. The python generates a output data from the original dataset. Into the python program I can load the table or I can leave a csv as external table which I can read from the procedure back again.

And so on so forth.

I actually have a lot of programs in shell script which are being executed in steps using Oracle Scheduler Chains. One of those steps is actually a python program. I found the API of the DBMS_SCHEDULER quite useful when you need to run technologies out of PL/SQL, as long as they can be invoked using shell script ( or cmd in Windows ).

Solution 4

You can use the Preprocessor feature with external tables, which allows you to invoke a Python script to populate an external table with data. An example can be found in the Using External Table section of this OTN article: https://community.oracle.com/docs/DOC-994731.

Solution 5

Depending on context you want to use Python you may consider OML4Py:

Oracle Machine Learning

Key benefits:

In-Database Processing: “Move the algorithms, not the data!”—Process data where it resides to eliminate data movement and further leverage your Oracle environment as a high performance compute engine with parallel, distributed algorithms.

Rapidly Deploy Machine Learning Applications—Because in-database machine learning models are native SQL functions, model deployment is immediate via SQL and R scripts.

---

Further reading:

Oracle Machine Learning: Scaling R and Python for the Enterprise

Slides(answering this particular question):

• page 7: Data access, analysis, and exploration
• page 24: Create user-defined functions from SQL (or use from R/Python)
• page 25: Invoke user-defined functions from SQL

Oracle Machine Learning Platform Move the algorithms, not the data!

View more solutions

Comments

[Alexander]

split your logic into smaller chunks and pass the results between the blocks of the python code, and so on

[abhi]

Other than PL/SQL, Oracle procedures can invoke methods written in java classes.

[user3311225]

Thanks everyone for the advice. I'm not too familar with Java and due to the time constraint, I probably will just do everything in Python. i.e use Python to run the sql part then carry on with the Python processing.

[Roberto Hernandez]

@user3311225 , using the DBMS_SCHEDULER package, you don't need Java or C or any other external language, You would only need a shell script to call your python programs.