从SQLAlchemy执行PL/SQL函数(或过程) [英] Execute a PL/SQL function (or procedure) from SQLAlchemy

问题描述

我具有旧版PL/SQL功能:

I have a legacy PL/SQL function:

getlogin(p_username in varchar2, p_password in varchar2) return boolean;

如何从SQLAlchemy执行此操作并获取返回值?

How can I execute this from SQLAlchemy and get the return value?

像这样的幼稚方法似乎行不通:

A naive approach like this doesn't seem to work:

result = DBSession.execute('getlogin(:username, :password)',
                           {'username':request.POST['username'],'password':request.POST['password']});
# extract return value from result

从日志中，我看到此错误:

From the logs I see this error:

文件"../env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/engine/base.py"，行871，在_execute_context中 语境) 在do_execute中，文件"../env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/engine/default.py"，第322行 cursor.execute(语句，参数) cx_Oracle.DatabaseError:ORA-00900:无效的SQL语句

File "../env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/engine/base.py", line 871, in _execute_context context) File "../env/lib/python3.3/site-packages/SQLAlchemy-0.8.0-py3.3.egg/sqlalchemy/engine/default.py", line 322, in do_execute cursor.execute(statement, parameters) cx_Oracle.DatabaseError: ORA-00900: invalid SQL statement

我可以看到它看起来像是在调用SQL解释器，而不是PL/SQL解释器，但是我不确定下一步如何.

I can see it looks like the SQL interpreter is being invoked and not the PL/SQL interpreter, but I'm not sure on the next steps.

推荐答案

您可能必须为执行调用提供一个PL/SQL块:

You'll probably have to provide a PL/SQL block to your execute call:

result = DBSession.execute('begin getlogin(:username, :password); end;',
                             {'username':request.POST['username'],'password':request.POST['password']});

此问题在SQLAlchemy + Oracle SP上可能也有帮助.

This SO question on SQLAlchemy + Oracle SP might also help.

这篇关于从SQLAlchemy执行PL/SQL函数(或过程)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！