There are couple of options to run Python script in Oracle database. Why? reuse Python scripts to create data feeding Oracle.
Oracle database support Java stored procedure and I can call Java method from PL/SQL. Well, Java can also run Python script, and in couple of ways. Put these two things together, I can reuse Python code for sure. I would go through this obvious option in this post.
At first, I got a script use Python faker to generate one line random data. Why not use Java faker directly? it is overkill when you generate tiny data only from it.
#!/usr/bin/python3
import sys
from faker import Faker
fake=Faker('en_CA')
a1=(''.join(fake.email())).replace('\n',', ')
a2=(''.join(fake.address())).replace('\n',', ')
print('|'.join([a1,a2]))
Second, Java can use ProcessBuilder or RunTime to run Python script. ProcessBuilder is newer and more flexibility is there. For more details about difference between these 2 class. Check out https://stackoverflow.com/questions/6856028/difference-between-processbuilder-and-runtime-exec.
import java.io.*;
public class myDemo{
public static String data() throws IOException
{
ProcessBuilder pb = new ProcessBuilder("/tmp/mysript.py");
Process p = pb.start();
String out = "";
BufferedReader in = new BufferedReader(new InputStreamReader(p.getInputStream()));
String thisLine = "";
while ((thisLine = in.readLine()) != null) {
out = out + thisLine;
}
return out;
}
}
It is time to load this java program into Oracle database. The loadjava tool creates schema objects from files and loads them into a schema. Schema objects can be created from Java source, class, and data files. loadjava can also create schema objects from SQLJ files.
It needs following SQL database privileges to load classes:
CREATE PROCEDURE and CREATE TABLE privileges to load into your schema.
CREATE ANY PROCEDURE and CREATE ANY TABLE privileges to load into another schema.
SQL> grant create session,create table,create procedure to dbuser;
Grant succeeded.
[oracle@localhost class]$ loadjava -u dbuser/password@myDB myDemo.class
Before I can do further things inside database, I have to grant execute privileges on Python scripts.
SQL> exec dbms_java.grant_permission(upper('dbuser'),'SYS:java.io.FilePermission','/tmp/myDemo.py', 'execute' );
PL/SQL procedure successfully completed.
It is time to create Java procedure in Oracle database now and run it finally.
SQL> conn dbuser/password@myDB
Connected.
SQL> create or replace function myDemo return varchar2 as language java name 'myDemo.data() return java.lang.String';
/
Function created.
SQL> var out varchar2(200)
SQL> call myDemo() into :out;
Call completed.
SQL> print out;
OUT
--------------------------------------------------------------------------------
markmiller@lee-riley.info|1-289-233-3919
It works as expected, Some efforts though. Check out calling java from PL/SQL (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdev/calling-Java-from-PL-SQL.html#GUID-499ABE6B-4391-43C8-A527-74A6C7B0A0FF)
