Stored procedures and Forms

From: Xavier Costey <xavier.costey_at_elpunt.com>
Date: Thu, 13 May 1999 13:52:28 +0200
Message-ID: <7hee0t$nm9$1@diana.bcn.ttd.net>

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 May 13 1999 - 06:52:28 CDT