[Zope] ZstoredProcedure help!

[J. Cone]

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


At 17:55 26/07/01 +0100, Ricardo Seghizzi wrote:
>Hi J. Cone
>
>thanks for your help! I manage to do it was well after your tip.
>
>I hava a last ( I hoppe!) question:
>
>if  scott.TEST_PKG.conta_reg is a function that returns, for instance a
>number,
>how can I put a variable to receive the value retuned by the function in the
>code bellow?
>
>>   begin
>>     scott.TEST_PKG.conta_reg;
>>   end;
>
>I tried
>result = scott.TEST_PKG.conta_reg;
>or
>result : = scott.TEST_PKG.conta_reg;
>
>and it didn't work. Can I use PL/SQL code in ZSqlMethod?
>
>Thanks
>
>Ricardo
>
>
>----- Original Message -----
>From: "J. Cone" <jcone@g8labs.com>
>To: "Ricardo Seghizzi" <ricardo@cnbe.mar.org.uk>; <Zope@zope.org>
>Sent: Thursday, July 26, 2001 2:58 PM
>Subject: Re: [Zope] ZstoredProcedure help!
>
>
>> Hello,
>>
>> I had to add "create or replace" and substitute "scott.dept" for your
>table.
>>
>> The creates worked after that.
>>
>> This oracle-specific ZSqlMethod:
>>
>>   begin
>>     scott.TEST_PKG.conta_reg;
>>   end;
>>
>> produces this result:
>>
>> Z SQL Method at  /james/exp1/call_proc  Help!
>>
>> --------------------------------------------------------------------------
>--
>> ----
>> This statement returned no results.
>> --------------------------------------------------------------------------
>--
>> ----
>> SQL used:
>>
>> begin
>>   scott.TEST_PKG.conta_reg;
>> end;
>>
>> Regards,
>> J. Cone.
>>
>> At 14:51 26/07/01 +0100, Ricardo Seghizzi wrote:
>> >Sorry!
>> >
>> >I was offline from the mail list so I dind't realised that the
>> >e-mail was sent.
>> >
>> >But, it would be better if you could help me with stored procedures
>> >problems.
>> >
>> >Regards
>> >
>> >Ricardo
>> >
>> >
>> >_______________________________________________
>> >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 )
>> >
>> >
>>
>>
>> _______________________________________________
>> 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 )
>
>
>