[Zope] Oracle Stored Procedures

[Andreas Jung]

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
----- Original Message -----
From: "ender" <kthangavelu@earthlink.net>
To: "Andreas Jung" <andreas@digicool.com>; "Alejandro Pancani"
<apancani@topgroup.com.ar>; <zope@zope.org>
Sent: Friday, April 20, 2001 12:52 PM
Subject: Re: [Zope] Oracle Stored Procedures


> 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 )
>