oracle cursor outparameters

[matt g]

Hi, 

I'm working with a client that does most of their work in stored procedures on an oracle database. One of the things I'm trying to help them with is transitioning to use sqlalchemy for calling said procedures.

For simple input and output types i was able to follow threads in this group and do something like this:

db.execute(text('begin HELLO_WORLD.say_hi(:x_in, :x_out); end;',bindparams=[bindparam('x_in',String),outparam('x_out',String)]),x_in='matt').

However, for most of their procedures, they actually have cursors in the outparams. The only way i have found to do it is to drop down into using a raw_connection and the cx_oracle types like so:

engine = create_engine(dsn)

con = engine.raw_connection()

cur = conn.cursor()

people=cur.var(cx_Oracle.CURSOR)

groups=cur.var(cx_Oracle.CURSOR)

params = [12345, people, groups]

#12345 is a input param

r = cur.callproc('list_people', params)

Is there a way to keep this more abstract and within sqlalchemy instead of dropping down into cx_Oracle?

thanks,
Matt 

[Michael Bayer]

sure....the "out parameter" idea is pretty much an oracle-only thing these days, so while we should be able to make this happen through the API, it obviously isn't backend-agnostic.

the usual "out parameter" routine is like you have above, where we get the results back using the "out_parameters" member of the result:

result = conn.execute(text('...', bindparams=[outparam('x', SomeType)]))

result.out_parameters['x']

this internally does what you're doing with cursor.var().  When you use String, it knows to use cx_Oracle.STRING because the cx_oracle dialect associates that DBAPI type with the String type.    At result time it applies the String type to the result of Variable.getvalue().

So, *assuming* the way cx_Oracle.CURSOR behaves here is that you get some kind of special object via getvalue(), no changes to SQLAlchemy would be needed.   If you need access to other methods of Variable (see http://cx-oracle.sourceforge.net/html/variable.html) then we might have to come up with something more involved, like intercepting the parameters using after_cursor_execute() or something.

To do this we just create a type:

from sqlalchemy.types import TypeEngine

class OracleCursorType(TypeEngine):

    def get_dbapi_type(self, dbapi):

        return dbapi.CURSOR

If you use OracleCursorType in your outparam() function, you should get the "cursor" value back in result.out_parameters.

If you want to give me a SQL expression that will actually return a "cursor" type, I can test this locally to iron out anything I'm missing, in case this doesn't work as is.

thanks,
Matt 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/u38qIICSt5AJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[matt g]

For the parts I'm working on right now, this is perfect. Thank you.

Matt