lostdorje lostdorje - 2 years ago 249
Python Question

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

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)',
# 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
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.

Answer Source

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

result = DBSession.execute('begin getlogin(:username, :password); end;',

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download