Calling Python from Oracle

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.

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/pythonimport quandl# World Bank Education Statistics# Population, tertiary, total - Czech Republicdata = 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     88641431-DEC-71     88554931-DEC-72     87753331-DEC-73     862859

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> declarebegin   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-2010 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_REFCURSORAS   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_Oracleimport pandas as pdconn = 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 asv_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=$1logfile=/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 ).