Execute a PL/SQL function (or procedure) from SQLAlchemy

oracle, plsql, python, sqlalchemy

Solution

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']});

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

Problem

I have a legacy PL/SQL function: ``` getlogin(p_username in varchar2, p_password in varchar2) return boolean; ``` 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: 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 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.

Original source