Bottom Page

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.
Quote
#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.
buran likes this post
Quote
#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>
Quote
#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.
Quote
#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!')
Quote
#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!
Quote
#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
Gribouillis likes this post
Quote
#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.
Quote
#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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  No Scripts File present after python installation ag2207 5 466 Jul-30-2020, 11:11 AM
Last Post: buran
  Conversion of Oracle PL/SQL(packages, functions, procedures) to python modules. DivyaKumar 2 1,615 Jul-09-2020, 04:46 PM
Last Post: srikanth7482
  Load table from Oracle to MYSQL using python himupant94 0 222 May-12-2020, 04:50 PM
Last Post: himupant94
  How to merge my two python scripts in one ? HK2432 0 301 Jan-31-2020, 10:16 PM
Last Post: HK2432
  How can I Open and close .py file from python scripts SayHiii 9 729 Dec-17-2019, 06:10 AM
Last Post: Malt
  autostart python scripts in background (Windows10) john36 4 1,887 Oct-01-2019, 01:36 PM
Last Post: john36
  how to set echo ON in Python call to Oracle SQL nmrt 4 712 Sep-21-2019, 05:40 AM
Last Post: nmrt
  Long command with characters not working in Python on Oracle Linux 7 iaas_infra 10 1,161 Jul-19-2019, 04:53 PM
Last Post: ichabod801
  Run macros of excel sheet from python scripts shubhamjainj 3 4,378 May-01-2019, 08:40 AM
Last Post: buran
  Oracle Merge using cx_oracle raulp2301 0 1,499 Mar-15-2019, 12:56 AM
Last Post: raulp2301

Forum Jump:


Users browsing this thread: 1 Guest(s)