[Zope] oracle8 and cursors

[Francisco José Esteban Risueño]

I can't imagine a problem that can be solved by cursors and not by a query.

We have been used PL/SQL (the extension of SQL by Oracle) inside zsql methods
like this:

DECLARE
  C_AUX NUMBER;

BEGIN

INSERT INTO IT_GRUPO_INVESTIGACION (
  C_GRUPO, D_GRUPO, S_GRUPO, F_INI_VIG, F_FIN_VIG, C_MACROAREA, L_TIPO_GRUPO,
C_PAI, C_CLASIFICACION_PAI
) VALUES (
 <dtml-sqlvar C_GRUPO type=nb>,
 <dtml-sqlvar D_GRUPO type=nb>,
 <dtml-sqlvar S_GRUPO type=nb>,
 <dtml-sqlvar F_INI_VIG type=nb>,
 <dtml-sqlvar F_FIN_VIG type=nb optional>,
 <dtml-sqlvar C_MACROAREA type=nb>,
 <dtml-sqlvar L_TIPO_GRUPO type=nb>,
 <dtml-sqlvar C_PAI type=nb optional>,
 <dtml-sqlvar C_CLASIFICACION_PAI type=nb optional>
);


<dtml-if items>
<dtml-in items>

<dtml-if "C_AREA_UNESCO <> ''">

INSERT INTO IT_GRUPO_AREA_UNESCO (
C_AREA_UNESCO, C_GRUPO, F_INI_VIG, F_FIN_VIG
) VALUES (
 <dtml-sqlvar C_AREA_UNESCO type=nb>,
 <dtml-sqlvar C_GRUPO type=nb>,
 <dtml-sqlvar F_INI_VIG type=nb>,
 <dtml-sqlvar F_FIN_VIG type=nb optional>
);

</dtml-if>
</dtml-in>
</dtml-if>

<dtml-if items2>
<dtml-in items2>

<dtml-if "D_GRUPO_TECNICA <> ''">

SELECT NVL(MAX(C_GRUPO_TECNICA)+1,1)
  INTO C_AUX
  FROM IT_GRUPO_TECNICA;

INSERT INTO IT_GRUPO_TECNICA (
C_GRUPO_TECNICA, D_GRUPO_TECNICA, C_GRUPO, L_PROPIA
) VALUES (
 C_AUX,
 <dtml-sqlvar D_GRUPO_TECNICA type=nb>,
 <dtml-sqlvar C_GRUPO type=nb>,
 <dtml-sqlvar L_PROPIA type=nb optional>
);

</dtml-if>

</dtml-in>
</dtml-if>


EXCEPTION
 WHEN OTHERS THEN
   rollback;
   raise;
END;

and it works fine. However, in such ZSQL method there isn't a resultset to
obtain (it contains  INSERT and UPDATES). I think you can get a resultset by
inserting the results of your cursor proccesing into a temp table and finishing
your PL/SQL with a query.

On the other hand, a cursor is a select statement you define in order to
process each row individually; that is, a ZSQL method. You can reproduce the
cursor proccessing by defining several ZSQL methods with single SELECT and
combining then in a DTML method that does the actual proccessing (whith
<dtml-in>, <dtml-let...>)

Andy Pahne escribió:

> hi, there,
>
> i've been using oracle8 with zope succesfully for a while. now my database
> programmer came up with a solution for a certain problem, which "we cannot
> solve with an easy select statement". he told me, that we are gonna have to
> use db-cursors instead.
>
> i must admit that i have heard the therm cursor before, but i am not sure
> how to use them with zsql-methods.
>
> my question is fairly simple: is there any difference in handling cursors
> with zsql-methods in comparison to handling 'simple' select-statements? can
> i use <dtml-in "..."</dtml-in> to iterate over the result sets?
>
> I read DCOracle.txt which comes along with DCOracle-Product, but I do not
> understand it very well.
>
> Any recommended readings?
>
> Andy Pahne
> ap@net22
>
> net22 GmbH
> Bad Meinberger Strasse 1
> 32760 Detmold
> Tel.: 05231 - 580 640
>
> _______________________________________________
> 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 )