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 )

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

That's right. As a workaround you can put the calls for the stored procedures into an external method or a PythonScript....never tried it but it should work. Andreas ----- Original Message ----- From: "Dario Lopez-Kästen" <dario@ita.chalmers.se> To: "Andreas Jung" <andreas@digicool.com> Cc: <zope@zope.org> Sent: Friday, April 20, 2001 3:34 PM Subject: Re: [Zope] Oracle Stored Procedures

From: "Andreas Jung" <andreas@digicool.com> Subject: Re: [Zope] Oracle Stored Procedures

...

Of course it is Zope specific. 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

So are you saying that stored procedures only can be used in the context of an SQL statemnt from Zope (in effect only from ZSQL methods?).

If so, is this the case with version 1 or 2 of the DCOracle product? or both?

/dario - expecting the worst... :-/

- -------------------------------------------------------------------- Dario Lopez-Kästen Systems Developer Chalmers Univ. of Technology dario@ita.chalmers.se ICQ: 14142275 (seldom) IT Systems & Services

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