I need a solution to run python (.py) scripts from oracle (pl/sql). Is there any solution?
For example: I have a python script to send gmail and create Excel spreadsheet from Oracle database. But I have to call this with Oracle, and I also have to use parameters from Oracle.
DBMS_SCHEDULER might be of use.
First create a shell script that is a wrapper for your Python.
Then create the job.
begin
dbms_scheduler.create_program
(
program_name => 'PYEXCEL',
program_type => 'EXECUTABLE',
program_action => '/the_path/the_py_script_wrapper.ks',
enabled => TRUE,
comments => 'Call Python stuff'
);
end;
/
Note, jobs can be configured with parameters in case your script needs these.
Then run:
BEGIN
DBMS_SCHEDULER.RUN_JOB(
JOB_NAME => 'PYEXCEL',
USE_CURRENT_SESSION => FALSE);
END;
/
This is the 'purest' PLSQL only way I think.
.ksh. A typo. It would be a wrapper for the so something like python /the_path/the_python_script.py .TenG's method is the easiest path to what you are looking for but another method can be found using OS_COMMAND
http://plsqlexecoscomm.sourceforge.net/plsqldoc/os_command.html
This (very short) answer to a similar question provides one more possible solution using Jython inerpreter. May be you can find other answers in that thread helpful. It is possible to call a Java method inside your PL/SQL that will load and run your .py script using a Jython Interpreter. It lacks an example though.
This article provides an example of how to run python code from java using the so called ProcessBuilder and any arbitrary python interpreter installed in the system. Here another example of ProcessBuilder used to run python from Java which in turn can be run from PL/SQL.
Hope this helps.
Generally the database is isolated from the OS for security reasons. There are a couple of workarounds (*):
host command and runs a shell script. Find out moreI think the second option is better for your purposes. In either case you will need to persuade your DBA / security team to allow the granting of the required privileges.
Alternatively Oracle has an inbuilt package UTL_MAIL to send email from PL/SQL and there are third-party PL/SQL libraries which allow us to generate Excel spreadsheets from inside the database. These may be more suitable to your situation (depending on how much you need to re-use your python code).
The other alternative is drive the whole thing from python programs, and just connect to the database to get the data you need.
(*) For completeness, there is a third way to execute OS shell scripts from the database. We can attach pre-processor scripts to external tables which get run whenever we select from the external table. Find out more. But I don't think external tables are relevant in this scenario. And of course external tables also need the granting of OS privileges to the database, so it doesn't avoid that conversation with your DBA / security team.