Discussion :

[tototoulouse1974]

Bonjour à toutes et à tous,

J'ai un programme PL/SQL dans lequel au niveau d'une boucle l'instruction suivante est appelée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MA_SEQUENCE.NEXTVAL INTO nouvelleCle FROM DUAL;

Au bout d'une centaine de passage dans la boucle, l'erreur suivante apparaît :

ora-01000: maximum open cursors exceeded

J'ai pu répéter cette erreur en créant ma propre séquence et en jouant l'instruction avec NEXTVAL sur cette séquence plusieurs fois :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE SEQUENCE SEQ_TEST_POUR_VOIR  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 2006680770 NOCACHE  NOORDER  NOCYCLE ;

SELECT SEQ_TEST_POUR_VOIR.NEXTVAL FROM DUAL; => répétée une centaine de fois

Et là aussi, j'obtiens cette erreur :

ora-01000: maximum open cursors exceeded

Je voudrais :
- comprendre ce qui se passe (ouverture à chaque fois d'un curseur "implicite" ? ou autre chose ?)
- comment remédier dans le code à un tel problème

Je ne voudrais pas augmenter le nombre de curseurs ouvert autorisé
mais changer le code de cette procédure PL/SQL afin que cette erreur ne survienne plus.

D'avance Merci à vous tous et bonne journée,
Thomas

[vanagreg]

Bonjour,

Quelle la version de ta BDD?
A partir de la 11g tu peux directement assigner la nextval de ta séquence à une variable:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

nouvelleCle := MA_SEQUENCE.NEXTVAL;

Quand tu lances ta boucle, tu peux voir les curseurs ouverts dans v$open_cursor, quels sont-ils?

[tototoulouse1974]

Bonjour,

Quelle la version de ta BDD?
A partir de la 11g tu peux directement assigner la nextval de ta séquence à une variable:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

nouvelleCle := MA_SEQUENCE.NEXTVAL;

Quand tu lances ta boucle, tu peux voir les curseurs ouverts dans v$open_cursor, quels sont-ils?

Merci beaucoup vanareg.
En fait, j'ai été imprécis dans la formulation de mon post.
La boucle se trouve en fait dans une application Java (que je n'ai pas codé, que je découvre) qui, dans une boucle, appelle une procédure stockée PL/SQL où l'instruction nextval est appelée.
Et ce n'est qu'ensuite à la fin de la boucle Java qu'un commit est fait dans Java : connection.commit()

J'ai 2 pistes qui d'ailleurs ne s'excluent pas l'une l'autre :

- la 1ère : modifier le Java
En effet, au niveau du code Java qui appelle la procédure stockée, il crée un statement (CallableStatement) mais ne le ferme jamais => 1er souci

- la 2ème :
ne plus utiliser la séquence dans la procédure stockée

Je te tiens au courant vanaveg.

Encore merci, bonne journée et à très bientôt,
Thomas

[vanagreg]

Ok, as-tu pu voir quels étaient les curseurs ouverts quand le problème se produit?
Un nextval ne devrait pas générer ce genre d'erreurs, je pense que tu en sauras plus en consultant v$open_cursor.

[tototoulouse1974]

Ok, as-tu pu voir quels étaient les curseurs ouverts quand le problème se produit?
Un nextval ne devrait pas générer ce genre d'erreurs, je pense que tu en sauras plus en consultant v$open_cursor.

Salut vanagreg.
Merci de te pencher sur mon problème.

1) Je n'arrive pas à visualiser cette table. J'ai essayé hier mais il ne la trouve pas.

2) Ce que j'ai fait pour résoudre le problème, c'est d'aller dans le code Java.

Le code Java (avec SpringBoot) que je n'ai pas écrit fait en gros ceci (je résume car sinon ce serait trop long et peu compréhensible) :

Prendre du pool de connexion 1 des connexions disponibles
Pour chaque object (1 objet correspond à une ligne)
Créer un statement (CallableStatement) à partir d'un "String" correspondant à l'appel de notre procédure stockée
Récupérer le statement
Fin de la boucle Pour

Ce que j'ai rajouté à la fin de la boucle, c'est la fermeture du statement (de type CallableStatement).
Et ceci a réglé le problème.
Car sinon, 1 fois sur 2, cela finit par planter avec l'erreur "nombre max de curseurs ouverts atteint".
En fermant systématiquement chaque statement (1 statement préparant 1 appel à la procédure stockée) à la fin de chaque itération de boucle, le problème a totalement disparu : j'ai fait 20 fois le test et le problème n'apparaît plus.


3) Après, sur SQLDeveloper,
en répétant l'instruction suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MOSAIC_INFO.SEQ_TEST_POUR_VOIR.NEXTVAL FROM DUAL;

plus d'une centaine de fois,

au bout d'un certain nombre d'exécutions de cette requête,
mon SQL Developer rame à fond
et finit par afficher l'erreur :
"Nombre maximums de curseurs ouverts atteint"


4) Ce que l'on me dit de faire, et que je vais faire,
c'est de modifier mon PLSQL de telle sorte que je ne fasse plus appel explicitement à ma séquence.
Car le champ servant de clé primaire est associé à cette séquence, donc en fait, je n'ai pas à l'inclure dans mon INSERT.

Dans la procédure stockée PL/SQM
Je vais donc remplacer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT SEQ_PRODUIT.NEXTVAL INTO compoundKey FROM DUAL;
INSERT INTO PRODUIT(PRODUIT_ID, PRODUIT_NOM)
VALUES(compoundKey, nomProduit);

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
INSERT INTO PRODUIT(PRODUIT_NOM)
VALUES(nomProduit);

5) Merci de t'être penché sur mon cas vanagreg.
Je te tiens au courant.

Bonne après-midi à vous tous,
Thomas