Re: Stored procedures and Forms

Xavier,

I think you need to close your cursor after the insert in the exception block, and also at the end of the procedure. Generally the end of my stored procedures look something like this:

...
 close the_cursor ;
EXCEPTION
 WHEN OTHERS THEN
  close the_cursor ; --ALWAYS CLOSE THE CURSOR   RAISE;
end procedure_name;

Even if I'm going to raise an error, I still close the cursor. If you wrap your code in a begin-exception-end where the exception block closes your cursors on any exception, you're guaranteed to have closed the cursor when you end the procedure.

Regards, David.

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 May 13 1999 - 19:35:38 CDT