[Zope] Oracle Stored Procedures

[ender]

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 )