plsql - Return variable from cx_Oracle PL/SQL call in Python -
i want execute oracle pl/sql statement via cx_oracle in python. code looks this:
db = cx_oracle.connect(user, pass, dsn_tns) cursor = db.cursor() ... sel = """ declare c number := 0.2; mn number := 1.5; res number; begin res := c+mn/6.; end; """ try: cursor.execute(sel) print "pl/sql successful executed ..." except cx_oracle.databaseerror e: err, = e.args print "\n".join([str(err.code),err.message,err.context]) the code running without problems, there chance result python?
you need function return result. anonymous block not.
you need create function in database, instance:
create or replace function calculation return number c number := 0.2; mn number := 1.5; res number; begin return c + mn / 6.; end; / then change python code call function, using, callfunc()
db = cx_oracle.connect(user, pass, dsn_tns) cursor = db.cursor() try: result = cursor.callfunc('calculation', float) print result except cx_oracle.databaseerror e: err, = e.args print "\n".join([str(err.code),err.message,err.context]) it's not possible create function on fly function simple enough can in select statement , use fetchall() described in linked documentation return result python. fetchall() returns list of tuples if you're after single row , column can select 0th index of both.
>>> import cx_oracle >>> db = cx_oracle.connect('****','****','****') >>> cursor = db.cursor() >>> sql = """select 0.2 + 1.5 / 6. dual""" >>> try: ... cursor.execute(sql) ... result = cursor.fetchall()[0][0] ... except cx_oracle.databaseerror, e: ... pass ... <__builtin__.oraclecursor on <cx_oracle.connection ****@****>> >>> result 0.45000000000000001 >>> you can pass variables execute() call using bind variables , therefore instantiate them in python if necessary:
>>> c = 0.2 >>> mn = 1.5 >>> sql = """select :c + :mn / 6. dual""" >>> bind_vars = { 'c' : c, 'mn' : mn } >>> cursor.execute(sql, bind_vars) <__builtin__.oraclecursor on <cx_oracle.connection history@monitor>> >>> result = cursor.fetchall()[0][0] >>> result 0.45000000000000001 >>> though might simpler in python... assume actual situation more complicated?
Comments
Post a Comment