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
An Oracle stored procedure (returning a value) acting as a function can be used without any problem inside a select statement. But there is no call interface to support stored procedures without return value. When your stored procedure only uses IN parameters you could rewrite it as a function.
Andreas Jung Digitial Creations ----- Original Message ----- From: "ender" <kthangavelu@earthlink.net> To: "Alejandro Pancani" <apancani@topgroup.com.ar>; <zope@zope.org> Sent: Friday, April 20, 2001 7:54 AM Subject: Re: [Zope] Oracle Stored Procedures
On Friday 20 April 2001 11:07, Alejandro Pancani wrote:
I wonder if anybody know how to call a stored procedure from Zope
I have an Oracle 8i Database conected to zope with Z Oracle DA Product
procedure not function, right?
i don't think this is zope specific, but generally with a procedure called from the client side you need to wrap it in an anonymous pl/sql block or a function (called from a select).
begin you_proc(args) end;
kapil
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )