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:
- 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.
- 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 ).
