[Zope] ZstoredProcedure help!

J. Cone jcone@g8labs.com
Thu, 02 Aug 2001 11:10:39 +0100


Hello Richardo,

I'm sorry; that's beyond my expertise.

It may be possible in a ZSqlMethod; if not, you may end up having to learn
python and write external methods.

I had to write external methods in order to write long raws into the database.

Regards,
J. Cone.

>From: ricardo@cnbe.mar.org.uk (Ricardo Seghizzi)
>To: "J. Cone" <jcone@g8labs.com>
>Subject: Re: [Zope] ZstoredProcedure  help!
>Date: Thu, 2 Aug 2001 10:53:29 +0100
>X-MSMail-Priority: Normal
>X-Mailer: Microsoft Outlook Express 5.00.2919.6700
>X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
>
>Hi J.Cone
>
>thanks very much for your help. I learned about the PRAGMA declaration and
>it is
>usefull. The example that you sent me worked very well.
>
>Sorry if I didn't said thanks before but I was very busy and
>only yesterday I could test your example.
>
>Things are geting more complicated kown!
>
>I created  test_2  PL/SQL procedure and I
>would like to call it from a ZSQL method or,  if  possible a
>Z Stored Procedure. My problem is How can I bind
>the OUT parameter (or any procedure parameters)  from test_2 with arguments
>from ZSQL methods.
>
>If I use a Z Stored Procedure How can I pass/receive arguments values to my
>stored procedures in the database?
>
>Do you know any documentation about this subject?
>
>Sorry to disturb you with this trivial questions but I don't know where
>to find documentation about it.
>
>Regards
>
>Ricardo
>
>
>PACKAGE test_pkg IS
>  FUNCTION conta_reg RETURN NUMBER;
>  PRAGMA restrict_references(conta_reg,wnds,wnps,rnps);
>
>  PROCEDURE  test_2(tot OUT NUMBER);
>  PRAGMA restrict_references(test_2,wnds,wnps,rnps);
>END;
>
>
>PACKAGE BODY TEST_PKG IS
>  FUNCTION conta_reg RETURN NUMBER
>  IS
>    total NUMBER;
>  BEGIN
>   SELECT COUNT(*) INTO total FROM ASSUNTOS;
>   RETURN total;
>  END;
>
>  PROCEDURE  test_2(tot OUT NUMBER) IS
>  total NUMBER;
>  BEGIN
>   SELECT COUNT(*) INTO total FROM ASSUNTOS;
>   tot:= total;
>  END;
>
>
>----- Original Message -----
>From: "J. Cone" <jcone@g8labs.com>
>To: "Ricardo Seghizzi" <ricardo@cnbe.mar.org.uk>
>Cc: <zope@zope.org>
>Sent: Thursday, July 26, 2001 6:15 PM
>Subject: Re: [Zope] ZstoredProcedure help!
>
>
>> Hello Richardo,
>> Hello Zopers,
>>
>> Advanced Oracle Zopers,
>>   - there is a promise in the help system that ZSqlMethod can contain
>> multiple statements
>>   - I would be interested to know how to get both DDL statements below
>into
>> one ZSqlMethod
>>   - I would be interested to know why broken DDL causes Zope 2.2.3 to:
>>        - write "ORA-00000: normal, successful completion" on its
>> controlling terminal
>>        - apparently restart
>>
>> Richardo:
>>
>> The pragma is a declaration that the package body won't do anything to
>> break the select statement at the end.  There's one other option, rnds,
>for
>> "Read No Database State".  I trust you have Oracle documentation and can
>> tailor it to your requirements.
>>
>> Regards,
>> J. Cone.
>>
>>
>> Create the following, eg in scott/tiger:
>> ----------------------
>> create or replace
>> PACKAGE test_pkg IS
>>   PROCEDURE conta_reg;
>>   function conta_reg_fn return number;
>>
>>   pragma restrict_references (conta_reg_fn, wnds, wnps, rnps);
>> END;
>> /
>> create or replace
>> PACKAGE BODY TEST_PKG IS
>>   PROCEDURE conta_reg
>>   IS
>>     total NUMBER;
>>   BEGIN
>>    SELECT COUNT(*) INTO total FROM scott.dept;
>>   END;
>>
>>   function conta_reg_fn return number
>>   IS
>>     total NUMBER;
>>   BEGIN
>>    SELECT COUNT(*) INTO total FROM scott.dept;
>>    return total;
>>   END;
>> END;
>> /
>> ----------------
>>
>> Do the following in a ZSqlMethod:
>> --------------------
>> select test_pkg.conta_reg_fn from dual
>> --------------------
>>
>> Get the following result:
>>
>> Z SQL Method at /longRaw/get_fn_result  Help!
>> --------------------------------------------------------------------------
>--
>> ----
>> CONTA REG FN
>> 4.0
>> --------------------------------------------------------------------------
>--
>> ----
>> SQL used:
>> select test_pkg.conta_reg_fn from dual
>>
>
>
>
>