[Zope-dev] ZSQL Methods from Python?

[Jim Fulton]

Monty Taylor wrote:
> 
> Jim Fulton wrote:
> 
(snip)
> > Note that one of the things I like about Oracle's stored procedures
> > is that they allow me to avoid screwing with cursors in the common case
> > that I'm getting one row of data.  I can just get the data I need through
> > a straight function call.  The DCOracleStorage uses stored procedures
> > almost exclusively.
> >
> >
> 
> I've been looking through that code (we've started using DCOracleStorage on
> the backend.) For some reason, though, I can't get the stored procedure stuff
> to work like you do. Check out the following:
> 
> Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
> With the Partitioning option
> JServer Release 8.1.6.0.0 - Production
> 
> SQL> desc pgm140_api;
> PROCEDURE SELECT_TARGETS
>  Argument Name                  Type                    In/Out Default?
>  ------------------------------ ----------------------- ------ --------
>  PP_NAME                        VARCHAR2                IN
>  PP_RESULT                      REF CURSOR              IN/OUT
>                                 RECORD                  IN/OUT
>      AMSM_ID                    NUMBER(12)              IN/OUT
>      NAME                       VARCHAR2(80)            IN/OUT
> PROCEDURE SELECT_TECHNOLOGIES
>  Argument Name                  Type                    In/Out Default?
>  ------------------------------ ----------------------- ------ --------
>  PP_CATEGORY                    VARCHAR2                IN
>  PP_RESULT                      REF CURSOR              IN/OUT
>                                 RECORD                  IN/OUT
>      ET_ID                      NUMBER(12)              IN/OUT
>      NAME                       VARCHAR2(80)            IN/OUT
> 
> *********
> Then from python, with the same connection string, I do:
> Python 1.5.2 (#1, Feb 14 2000, 18:27:27)  [GCC 2.95.1 19990816 (release)] on
> sunos5
> Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
> >>> import DCOracle.oci_
> >>> conn=DCOracle.Connect('****')
> >>> pgm140_api=getattr(conn.procedures,'pgm140_api')
> >>> sql_select_targets=getattr(pgm140_api, 'select_targets')
> Traceback (innermost last):
>   File "<stdin>", line 1, in ?
>   File "/apps/zope/lib/python/Products/ZOracleDA/DCOracle/ociProc.py", line
> 324, in __getattr__
> oci.error: no usable procedure named pgm140_api.select_targets
> 
> I've tried using all caps on one or both as well to no avail. Any thoughts?

I suspect that the problem is the RECORD argument type. I don't
remember off-hand what this is, but I'm pretty sure, the DCOracle
procedure code doesn's handle this type. We (you;) would need to
either work around this or fix it.  Unless the fix is easy though,
we might want to wait for an OCI8-based Oracle interface that
someone here is rumored to be working on. :) (Actually, I *know*
that someone's working on an OCI 8 interface, but I'm not sure
what the status or priority is.)

Jim

--
Jim Fulton           mailto:jim@digicool.com
Technical Director   (888) 344-4332              Python Powered!
Digital Creations    http://www.digicool.com     http://www.python.org

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.