Python Forum
Possible to run Python scripts from oracle?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Possible to run Python scripts from oracle?
#1
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.
Reply
#2
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.
Reply
#3
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>
Reply
#4
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.
Reply
#5
well, if you are running python37, based on info from your original post

(Sep-06-2018, 11:34 AM)dglass Wrote: "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!')
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
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!
Reply
#7
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#8
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.
Reply
#9
(Sep-06-2018, 04:38 PM)buran Wrote: 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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Trying to us python.exe from our network to run scripts cubangt 3 813 Aug-17-2023, 07:53 PM
Last Post: deanhystad
  Encrypting Oracle Passwords / Python Library for That? bmccollum 1 2,528 Jun-11-2021, 07:59 PM
Last Post: Larz60+
  Running python scripts from github etc pacmyc 7 3,608 Mar-03-2021, 10:26 PM
Last Post: pacmyc
  Calling Oracle REST SQL from Python johnjacob 2 1,991 Nov-05-2020, 04:19 AM
Last Post: johnjacob
  Python to Oracle Conn Issue chvsnarayana 2 32,575 Sep-06-2020, 04:33 PM
Last Post: Larz60+
  Reading SQL scripts from excel file and run it using python saravanatn 2 2,456 Aug-23-2020, 04:49 PM
Last Post: saravanatn
  No Scripts File present after python installation ag2207 5 4,763 Jul-30-2020, 11:11 AM
Last Post: buran
  Conversion of Oracle PL/SQL(packages, functions, procedures) to python modules. DivyaKumar 2 6,411 Jul-09-2020, 04:46 PM
Last Post: srikanth7482
  Load table from Oracle to MYSQL using python himupant94 0 1,607 May-12-2020, 04:50 PM
Last Post: himupant94
  How to merge my two python scripts in one ? HK2432 0 2,113 Jan-31-2020, 10:16 PM
Last Post: HK2432

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020