Re: [Zope] ZstoredProcedure help!

2 Aug 2001

      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