[Zope] Oracle Stored Procedures

[ender]

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