On Friday 20 April 2001 16:49, Andreas Jung wrote:
Nope, DCOracle provides support calling stored procedures through OCI like "curs.procedures.package.proc(...)".You can these functions inside an external method or a PythonScript. Please refer to the documentation - it's explained there.
Andreas
sigh... first i'm using DCOracle2 for the record, i've always had problems with compiling DCOracle, DCOracle2 is MUCH easier to get up and running on different machines (solaris and linux) not to mention the source is alot cleaner. I realize now that the difference between these two adaptors is the reason for the most of the differences in answers, which are much more about the differences between the adaptors. 1. dcoracle2 does not support calling stored procs through oci, its a proxy implementation doing a cursor execute with an anonymous pl/sql block after param parsing upon being called. neither, does dcoracle1 (read the implementation) do a direct calling of pl/sql procs/funcs through oci, it also wraps the call in an anonymous pl/sql block, as do the oracle examples in rdbms/demo. 2. procedure is a connection method not a cursor method. (dcoracle1 exports it as the 'procedures' namespace on the cursor) 3. i DO indeed use the procedure method to call a function in the sample code, also i'm not sure if you saw how to crash python with a 4 lines of code using DCOracle2 at the end of my email, since its not apparent that you even finished reading it. 4. procedure() is not exposed by the Zoracle adaptor and cannot be called from a TTW python script without mucking around. 5. binding arrays require being mapped to a list to be useable inside of zope because they don't define security info (and seem to be read only). 6. more important than reading the documentation is reading the source. thanks. kapil
On Friday 20 April 2001 12:02, Andreas Jung wrote:
Of course it is Zope specific.
what i meant was, afaik oci does not support direct call of procedures
except
via anonymous pl/sql blocks, which is not Zope specific.
zope does have issues with calling db procs more along the lines that currently the system expects a description to associate the return values
in
the namespace, which is fine for select usage, but with an anonymous
pl/sql
block or direct calling (via procedure) than this info is not present in
the
description where the zope machinery expects to find it. zsql methods are
not
geared towards handling stored procs, but instead sql constructs (select, insert, etc).
here are a couple of funcs that should help get someone started with
oracle
procs/functions in zope. just add them as external methods
Extensions/OracleUtil.py from string import split """ wrap executes with except DatabaseError for possiblity of no data found. from dco2 import DatabaseError """ def call_proc(self, da, query): " when you don't care what the return value is" da._v_database_connection.cursor.execute(query) return
def call_binding_proc(self,da,query, array_args): " when you only want the out vars " res = [] a = res.append ba =apply(da._v_database_connection.db.BindingArray, array_args) da._v_database_connection.cursor.execute(query, ba) map(a,ba) return res
def call_func(self, da, q_func_name, args): parts = split(q_func_name, '.') print parts proc = None v = da._v_database_connection.db.procedure
for p in parts: print p v = getattr(v, p) return apply(v, args)
## <END
i tested them like so (site_node.url is a function)
<dtml-var "a(o2, 'DECLARE foo varchar2(4000); BEGIN foo := site_node.url(1115); END;')"> <br> <dtml-in "b(o2, 'BEGIN :1 := site_node.url(1115); END;', (10,64, 'SQLT_STR'))"> <dtml-var sequence-item> </dtml-in> <dtml-var "c(o2, 'site_node.url', (1115,))">
if you might get a no data found error, you should wrap the last two functions in try except DatabaseError and return appriopately.
procedures can probably be integrated into zope a bit more naturally,
roughly
along the lines of sqlmethods but it requires digging through
conn.describe()
info, and building up a desc to pass to ZRDB.
incidentally an easy way to crash python using DCOracle2 import DCOracle2 conn = DCOracle2.connect('info/info') curs = conn.cursor() curs.fetchmany() segementation fault
cheers
kapil