Possible to run Python scripts from oracle?

[dglass]

So I'm using oracle's dbms_scheduler, which is able to execute cmd.exe to run command line scripts (.bat, .cmd), but I can't seem to do the same for .py files via python.exe. I've also tried using a .bat file that runs python (which works when executed from windows, but not from oracle), running into the same traceback error (details: https://community.oracle.com/thread/4169278).

cmd>
@echo on

"C:\Program Files\Python37\python.exe" c:\test.py

I was told by someone on the oracle forum:
"You will need to manually setup the python environment in your test.cmd file so that Oracle is able to run python properly. (don't rely on windows to do it for you).

PATH

PYTHONPATH

PYTHONHOME

etc..."

How exactly would I do this? Does running python.exe not do this in the same way that cmd.exe does? The only information I could find online involved setting windows paths to python.exe location.

[Gribouillis]

I don't believe this answer with python environment variables. Python doesn't need PYTHONPATH or PYTHONHOME to run. The main problem for you is that Oracle doesn't display the whole python traceback which would tell you more about the error.

You could perhaps cheat a little about what oracle displays by writing the python script like this for example

import sys
import traceback

def main():
    ... # your code here

if __name__ == '__main__':
    try:
        main()
    except Exception:
        s = ''.join(traceback.format_exc(-3)).replace('\n', ' ')  # or -2
        print(s, file=sys.stderr)
        sys.exit(1)

Edit: sorry, it is format_exc instead of format_tb.

[dglass]

Unfortunately, it doesn't display anything different. It seems that an error will occur on the first "command?" line. Example with just print:

Error:
Error report -
ORA-27369: job of type EXECUTABLE failed with exit code: 1   File "C:\test.py", line 2
    print 'Hello, world!'

(Error code 1 is just the generic "everything else" error code)

With the script I was using:

import sys
import traceback
def main():
   f= open("test.txt","w+")
   for i in range(10):
         f.write("This is line %d\r\n" % (i+1))
   f.close()
if __name__ == '__main__':
    try:
        main()
    except Exception:
        s = ''.join(traceback.format_exc(-3)).replace('\n', ' ')  # or -2
        print(s, file=sys.stderr)
        sys.exit(1)

Gets this:

Error:
Error report -
ORA-27369: job of type EXECUTABLE failed with exit code: 1 Traceback (most recent call last):   File "C:\test.py", line 10, in <module> 

[Gribouillis]

I don't see the print 'Hello, world' from the error traceback in your code. If you post another code's error messages, there is no solution. For the question of obtaining a better output, try to play with the negative number in format_exc(). Try -1 perhaps, or use

s = ''.join(traceback.format_exc()).replace('\n', '').replace('\r', '')[-30:]

to output the 30 last characters of the exception traceback.

[buran]

well, if you are running python37, based on info from your original post

"C:\Program Files\Python37\python.exe

then print 'Hello, world!' won't work because in python3 print is a function. It should be print('Hello, world!')

[dglass]

Ah, I forgot about the 2 and 3 differences (I'm pretty new to Python). Yes, there are no errors returned with

print('Hello, world!')

, although there's also no visible result when executing from oracle.
Using

s = ''.join(traceback.format_exc()).replace('\n', '').replace('\r', '')[-30:]

Gets

Error:
Error report -
ORA-27369: job of type EXECUTABLE failed with exit code: 1  Permission denied: 'test.txt'

Seems that it's (another) permission error (Hopefully should be able to grant oracle additional windows privileges), thanks a lot for your help!

[buran]

try to specify full path to where the output file would be saved. At the moment it will try to save it in the current working directory, which is unclear

[Gribouillis]

Also the good news is that your python script runs from Oracle, this answers the title of this thread. You have only a permission issue with the output file. Once this works, you will be able to log full error traceback and output, which is the main tool that you need to develop these plugins.

[dglass]

try to specify full path to where the output file would be saved. At the moment it will try to save it in the current working directory, which is unclear

Thanks for that, specifying full path fixed the "permission denied" issue. Would've been confusing myself to no end trying to grant oracle permissions.