[Zope-DB] Returning values from Oracle function/procedure withZSQLMethod

[Jim Abramson]

for functions, you can do something like
  
select my_function_result(<dtml-sqlvar myparam>) from dual

(assuming your function returns a scalar of some kind.)

for procedures, you can do the DCOracle2 Stored Procedure (might be
called ZOracleDA Stored Procedure in your install...)


OTOH I've found the procedure objects a bit flaky.  In our experience we
ended up needing to do increasingly complex things with plsql, and
ultimately, we had no choice to move all our db access out into
ExternalMethods or Products and use DCOracle2 directly.  This does
require constructing your own connection pool/management, but once
you've built that you can leverage DCOracle2 directly in python and this
provides much more flexibility.

hth
Jim


> -----Original Message-----
> From: zope-db-bounces at zope.org 
> [mailto:zope-db-bounces at zope.org] On Behalf Of Maciej Wisniowski
> Sent: Friday, April 08, 2005 2:28 PM
> To: Zope-DB at zope.org
> Subject: [Zope-DB] Returning values from Oracle 
> function/procedure withZSQLMethod
> 
> Hi
> 
> I'm using DCOracle2 to connect to Oracle databases, but I 
> think that moving to SQLRelay may be a good idea. I haven't 
> tried it yet but I wonder how to get any values returned from 
> oracle function and/or procedures using only ZSQLMethods?
> 
> I've made few tries with DCOracle to call functions from 
> ZSQLMethod and to return a value to calling python script, 
> but I wasn't successful with this. It was pretty simple in 
> Sybase or PostgreSQL but how to do this with oracle?
> 
> Suppose that I have database function: get_next_code() in 
> database that returns integer value. How my ZSQLMethod body 
> should look like to return value to Python script that called 
> this ZSQLMethod? I think it'll be same in DCOracle2 and in 
> ZSQLRelayDA. Am I wrong?
> 
> Another option is to get in/out parameters from procedures...
> is this possible with ZSQLRelayDA?
> 
> --
> Maciej
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>