Discussion :

[NewJS]

Bonjour à tous

J'ai crée dans un 1er temps une fonction en PL/SQL où j'utilise un curseur dans mon package que j'ai nommé pkg.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
function p1 ( p1_id in number ) return varchar2
is
retour varchar2(2000);
begin
retour := null;
BEGIN
  FOR c1 IN (SELECT apt.name
             FROM AP_PAYMENT_SCHEDULES_ALL aps,
                  ap_invoices_all ai,
                  ap_suppliers ap,
                  ap_supplier_sites_all apsa,
                  ap_terms apt
              WHERE apsa.vendor_site_id = p1_id
              AND aps.invoice_id = ai.invoice_id
              AND apt.term_id = ai.terms_id
              AND ap.vendor_id = ai.vendor_id
              AND apsa.vendor_site_id = ai.vendor_site_id
              AND apsa.vendor_id = ap.vendor_id
              AND apt.enabled_flag='Y')
  LOOP
    retour := retour || c1.name ;
  END LOOP;
END;
 
return retour;
 
end;

Ensuite, j'insère la valeur retournée par cette fonction dans une table.

Code :

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

1
2
3
4
5
6
7
8
9
insert into table_test ( 
org_id,
champ_test
)
select 
org_id,
pkg.p1(p1_id)
from 
     ap_supplier_sites_all apsa;

Le souci, c'est qu'au moment où j'insère, j'ai l'erreur suivante ORA-06502: PL/SQL: numeric or value error: character string buffer too small



D'où ça peut venir ?
J'ai pensé dans un premier temps que ça pouvait venir de retour varchar2(2000); dans mon fonction. (au départ c'était 80, j'ai mis 2000 et même essayé 4000) mais toujours le même souci.

Aussi, ma table table_test possède 2 champs : org_id et champ_test.
champ_test possède à la base un type VARCHAR2(80) que j'ai mis pour essayer à VARCHAR2(300) mais toujours le même souci également.

Quelqu'un a déjà rencontré un souci de ce type ?

[al1_24]

Avant de tester l'INSERT, as-tu essayé d'exécuter le SELECT pour vérifier la valeur retournée par la fonction ?
Et la requête exécutée dans la fonction ?

[NewJS]

Code :

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

1
2
3
4
5
select 
org_id,
pkg.p1(p1_id)
from 
     ap_supplier_sites_all apsa;

Rien que ça me ramène l'erreur sous SQL Developer

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: character string buffer too small
ORA-06512: à "PKG", ligne 646
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:

[vanagreg]

Bonjour,

Il vaut mieux éviter de faire des fonctions quand ce n'est pas utile, d'autant plus qu'ici la table qui sert à l'insertion est également utilisée dans la fonction, ce qui n'est pas terrible en termes de performance. Quand une fonction SQL est appelée au sein d'une requête, elle peut-être exécutée en interne plusieurs fois par Oracle, ce qui peut donc amener à l'erreur que tu rencontres puisque la fonction fait de la concaténation.
Tu peux plutôt écrire ta requête ainsi:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO table_test(org_id, champ_test)
SELECT apsa.org_id,
       LISTAGG(apt.name) WITHIN GROUP(ORDER BY null) 
FROM AP_PAYMENT_SCHEDULES_ALL aps,
  ap_invoices_all ai,
  ap_suppliers ap,
  ap_supplier_sites_all apsa,
  ap_terms apt
WHERE apsa.vendor_site_id = p1_id
AND aps.invoice_id = ai.invoice_id
AND apt.term_id = ai.terms_id
AND ap.vendor_id = ai.vendor_id
AND apsa.vendor_site_id = ai.vendor_site_id
AND apsa.vendor_id = ap.vendor_id
AND apt.enabled_flag='Y'
GROUP BY apsa.org_id;

[NewJS]

Merci

Mais concrètement, c'est quoi le souci ? C'est ce que ramène ma fonction ? Elle ramène trop de valeurs ?

Car le package se compile parfaitement au départ, donc ce n'est pas un souci syntaxique. C'est plutôt ce que ça ramène.

[vanagreg]

Non ce n'est pas un souci de compilation. L'erreur vient du fait que l'appel de la fonction provoque un dépassement de la limite 4000 en sql. Tu peux essayer du "scalar subquery caching" en appelant ta fonction sous la forme de sous-requête scalaire:

Code :

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

1
2
3
4
5
select 
org_id,
(select pkg.p1(p1_id) from dual)
from 
     ap_supplier_sites_all apsa;

Mais le mieux c'est de faire comme j'ai indiqué.

[McM]

Ca vient de ton LOOP

Code :

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

1
2
3
  LOOP
    retour := retour || c1.name ;
  END LOOP;

Tu ne testes pas la taille du champ, donc arrivé avec retour vers 2000 caractères, dès que tu vas concaténer et dépasser 2000.. erreur

Soit tu tronques (mais le résultat n'est pas complet), soit tu sors en erreur.

Exemple en tronquant le résultat

Code :

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

1
2
3
4
5
6
7
8
9
DECLARE
	v VARCHAR2(2000);
BEGIN
	FOR i IN 1..5000
  LOOP
  	v := SUBSTR(v || 'x', 1, 2000);
  END LOOP;
  DBMS_OUTPUT.put_line(LENGTH(v));
END;