Can I call a function that does dml?

[john]

I get the following traceback:

>>> conn.execute(sqlalchemy.func.myfunc('1', '2'))
Traceback (most recent call last):
File "<interactive input>", line 1, in <module>
File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\engine\base.py", line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\engine\base.py", line 858, in _execute_function
return self._execute_clauseelement(func.select(), multiparams,
params)
File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\engine\base.py", line 874, in _execute_clauseelement
return self.__execute_context(context)
File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\engine\base.py", line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\engine\base.py", line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\engine\base.py", line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DatabaseError: (DatabaseError) ORA-14551: cannot perform a DML
operation inside a query

'SELECT myfunc(:myfunc_2, :myfunc_3) AS myfunc_1 FROM
DUAL' {'myfunc_2': '1', 'myfunc_2': '2'}

The select is the problem. Leaving aside the issue of whether its a
good idea to modify data inside a function, is there a way to do this
using sqlalchemy? And if so, what is the preferred syntax/style?

cheers

[Michael Bayer]

here's what google has to say:

http://www.dba-oracle.com/t_ora_14551_cannot_perform_a_dml_operation_inside_a_query.htm

so you need to execute from a string or text() construct on this one with
the syntax oracle is looking for.



>
> cheers
> >
>

[john]

Thanks, but when I try that I get:

>>> s = sqlalchemy.sql.text("DECLARE RetVal VARCHAR2(200); p1 VARCHAR2(200); p2 NUMBER; BEGIN p1 := '1'; P2 := 2; RetVal := myfunc ( p1, p2 ); END; ")
>>> print conn.execute(s).fetchall()

Traceback (most recent call last):
File "<interactive input>", line 1, in <module>
File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg

\sqlalchemy\engine\base.py", line 1642, in fetchall
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)

File "C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg
\sqlalchemy\engine\base.py", line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)

InterfaceError: (InterfaceError) not open None None

My obtuseness makes it hard to see the connection between executing
the statement and retrieving the results.

[Michael Bayer]

thats a strange error there and not sure if that's cx_oracle being weird,
or some doubling of errors is occurring when the text() statement fails.
I don't know how to declare variables and then execute SQL against them in
a single DBAPI execute() call.

we do have an "out param" test which doesn't go through any of that
trouble, it looks like:

result = testing.db.execute(text("begin foo(:x_in, :x_out, :y_out,
:z_out); end;",
bindparams=[bindparam('x_in', Numeric), outparam('x_out',
Integer), outparam('y_out', Numeric), outparam('z_out',
String)]), x_in=5)
assert result.out_parameters == {'x_out':10, 'y_out':75,
'z_out':None}, result.out_parameters
assert isinstance(result.out_parameters['x_out'], int)

thats one thing to try, otherwise your next stop would be the cx_oracle
mailing list.


>
> >
>

[john]

Thanks, that works (taking your out param test from above):

>>> r = conn.execute(sa.text('begin :rv := pkga.f_x(:p1, :p2); end;', bindparams=[sa.bindparam('p1', sa.String), sa.bindparam('p2', sa.INT), sa.outparam('rv', sa.String)]), p1='1', p2=2)
>>> r.out_parameters
{'rv': 'yup'}

but I got to say it seems verbose.
Looking at the cx_Oracle test suite and the examples at

http://markharrison.net/cx-oracle-demos/function.html (almost the same
as test suite)

it shows 2 forms of execution, of which the 2nd form is what you
suggested.
I guess there is no way to do the, less verbose, first form?

i.e.
x = curs.callfunc('cxdemo.f2', cx_Oracle.NUMBER, [1,2])
print x

Which works in cx_Oracle, just don't think I can call sa.callfunc
without it trying to do a select and I don't think I have access to
the cursor obj in sa, but my head is foggy.

[Michael Bayer]

you can get at a cursor if you say conn.connection.cursor(). but
then you'd just be dealing with the plain cx_oracle cursor. We
don't at the moment have a "friendly" interface to `callfunc` though
you can just build yourself a short function that accomplishes the
ugliness behind the scenes.