Re: Stored procedures with Forms 5

Open Cursors=512 should be a good value for an average database.

The problem in your source code is that you do not close the cursor.

There are 2 ways of using cursors

• implizit and explizit cursors

the explizit way works with
  open cursor
  fetch cursor
  close cursor

the implizit way works with

   for rc_var in cursor loop
   end loop;
   here you do not have to close the cursor

roland

Xavier Costey wrote:

> Hi my name is Xavier Costey and I´ve a problem:
>
> I program with Oracle Forms Ver 5.
> I assign a stored procedure to a Data Block. This runs well,
> but when the program is executed the number times of the variable
> OPEN_CURSORS
> then forms raises with the error number ORA-01000.
>
> Questions:
>
> - The forms close automaticly the cursor? If Forms don't close the
> cursor how can i close it?
>
> - The package of the stored procedure is correct ?
>
> - I´ve got teh variable OPEN_CURSORS = 512, is this value correct?
>
> Thanks
>
> ORA-01000 : maximum open cursors exceeded
> A host language program attempted to open too many cursors. The
> initialization parameter OPEN_CURSORS determines the
> maximum number of cursors per user.
>
> Example of stored procedure :
>
> /****************************************************/
> create or replace package contracte_generic is
> type tipus_registre_tercer is record(
> ID tercers.id%type,
> TIP_TERCER tip_tercers.descripcio%type,
> NOM tercers.nom%type,
> COGNOMS tercers.cognoms%type,
> COGNOM2 tercers.cognom2%type,
> NIF tercers.nif%type,
> NOM_COMERCIAL tercers.nom_comercial%type,
> tip_terecer_codi tip_tercers.codi%type,
> client_id clients.id%type
> );
>
> type cursor_tipus_tercer is ref cursor return tipus_registre_tercer;
>
> Procedure select_tercer(codi_tercer number, retval in out
> cursor_tipus_tercer);
>
> end contracte_generic;
> /
> show errors;
>
> /**************************************************/
> create or replace package body contracte_generic as
>
> Procedure select_tercer(codi_tercer number, retval in out
> cursor_tipus_tercer) is
> text_error varchar2(200);
> begin
>
> open retval for select tercers.id, tip_tercers.descripcio
> tip_tercer,tercers.nom,
> tercers.cognoms, tercers.cognom2,tercers.nif,
> tercers.nom_comercial,
> tercers.tip_tercer_codi,clients.id
> from tercers, tip_tercers,clients
> where tercers.id = codi_tercer and
> tercers.tip_tercer_codi = tip_tercers.codi and
> tercers.id = clients.tercer_id(+);
> exception
> when others then
> text_error := sqlerrm;
> insert into
> errors_departament(form,missatge)values('Package',text_error);
> commit;
>
> end;
>
> /*************************************************/
> end contracte_generic;
> /
> show errors;
Received on Thu Jun 24 1999 - 19:45:24 CEST