ORA-00911 - Problème de buffer ou chaine tronquée?
Salut tout le monde!
J'ai fait un pgr pl sql qui permets de lancer des requetes en boucle et de calculer le temps mis pourles effectuer...
Mais j'ai plusieurs requetes qui ne passent pas... Je me demande si ces requetes ne sont pas tronquees lors de mon fetch ...
Voici le msg d'erreur que j'obtiens :
(msg que j'ecris dans ma table)
SQLCODE = -911 SQLERRM:ORA-00911: Caractère non valide
Voici ma requete (contenue dans ma table, champ de type long):
(elle est monstrueuse et je ne sais pas si il y a un quelconque interet a la poster mais bon...)
Code:
Select 'INDA',to_char(lpad(999-N.NUMEORDR,3,0)),I.NOM||' '||I.PREN,N.LIBL,Decode(I.DECE,'O','*',''),I.NOM||' '||I.PREN,nvl(lpad(I.JOURNAIS,2,'0'),'__') ||'/'|| nvl(lpad(I.MOISNAIS,2,'0'),'__') ||'/'|| nvl(lpad(I.ANNENAIS,4,'0'),'____'),Pack_Bure.Age_Indi(I.INDI),to_char(I.INDI),NULL From APPORT A,INDIVI I,NOMENC N,PERI_1 P Where A.ID='23623320' And A.APPO=P.APPO And P.INDI=I.INDI And P.ID_TYPEQUAL=N.ID UNION Select 'MODA',N2.NOME,T.TYPEPARA,T.LIBL,Decode(L.VALE,'O',DECODE(N3.NOME,'FBOOLE','Oui','O'),'N',DECODE(N3.NOME,'FBOOLE','Non','N'),pack_univ.liblvalepara(T.ID,L.VALE)), to_char(L.DATEDEBUEFFE,'DD/MM/YYYY'),Decode(to_char(L.DATEFIN_EFFE,'DD/MM/YYYY'),'31/12/2099',NULL,to_char(L.DATEFIN_EFFE,'DD/MM/YYYY')),NULL,NULL,NULL From APPORT A,TYPPAR T,NOMENC N1,NOMENC N2,NOMENC N3,PARINT L,INTERV S Where A.ID='23623320' And A.APPO=S.APPO And S.INTE=L.INTE And L.ID_TYPEPARA=T.ID And T.ID_NATUPARA=N2.ID And T.ID_FORMPARA=N3.ID And T.ID_CATEPARA=N1.ID And N1.NOME||''='INTE' And (nvl(L.DATEFIN_EFFE,TO_DATE('2099-12-31','YYYY-MM-DD'))=(Select Max(nvl(DATEFIN_EFFE,TO_DATE('2099-12-31','YYYY-MM-DD'))) From PARINT Where ID_TYPEPARA=L.ID_TYPEPARA And Pack_Nomenc.CompDate(DATEDEBUEFFE,SYSDATE)='O' And INTE=L.INTE) or nvl(L.DATEDEBUEFFE,TO_DATE('1900-01-01','YYYY-MM-DD'))=(Select Min(nvl(DATEDEBUEFFE,TO_DATE('1900-01-01','YYYY-MM-DD'))) From PARINT Where ID_TYPEPARA=L.ID_TYPEPARA And Pack_Nomenc.CompDate(SYSDATE,DATEDEBUEFFE)='O' And INTE=L.INTE)) UNION Select 'REQ1',to_char(B.ID,'00000000009'),NULL,Pack_Bure2.lablappointe(B.ID,0,1),T.OUTIEXEC,to_char(B.ID),to_char(T.ID),N.NOME,I.ANNU,T.TYPETACH From APPORT A,APPORT B,TYPTAC T,SUIAPP S,NOMENC N,INTERV I Where A.APPO=S.APPOPREC and S.APPOSUIV=B.APPO and B.APPO=I.APPO And B.ID_TYPETACH=T.ID And S.ID_TYPELIEN=N.ID and N.NOME||'' in ('INSC','PROL','SUBS') And A.ID='23623320' And (S.SUPPLOGI = 'N' or S.SUPPLOGI is null) union Select 'REQ1',NULL,NULL,Pack_Bure2.lablappo(B.ID),T.OUTIEXEC,to_char(B.ID),to_char(T.ID),N.NOME,NULL,T.TYPETACH From APPORT A,APPORT B,TYPTAC T,SUIAPP S,NOMENC N Where A.APPO=S.APPOPREC and S.APPOSUIV=B.APPO And B.ID_TYPETACH=T.ID And T.TYPETACH <> 'PROJET' And S.ID_TYPELIEN=N.ID and N.NOME||'' in ('DERE','DECO') And A.ID='23623320' And pack_habi.habilist(999000000,'',B.DOSS,'',B.PROC)='O' UNION Select 'REQ2',to_char(B.ID,'00000000009'),NULL,Pack_Bure2.lablappo(B.ID),T.OUTIEXEC,to_char(B.ID),to_char(T.ID),N.NOME,NULL,T.TYPETACH From APPORT A,APPORT B,TYPTAC T,SUIAPP S,NOMENC N Where A.APPO=S.APPOSUIV And B.APPO=S.APPOPREC And B.ID_TYPETACH=T.ID And S.ID_TYPELIEN=N.ID And A.ID='23623320' And N.NOME <> 'SUIN' And (S.SUPPLOGI = 'N' or S.SUPPLOGI is null) union Select 'REQ2',to_char((999999999999-B.ID),'000000000009'),NULL,Pack_Bure2.lablappo(B.ID),T.OUTIEXEC,to_char(B.ID),to_char(T.ID),N.NOME,NULL,T.TYPETACH From APPORT A,APPORT B,TYPTAC T,SUIAPP S,NOMENC N Where A.APPO=S.APPOSUIV And B.APPO=S.APPOPREC And B.ID_TYPETACH=T.ID And S.ID_TYPELIEN=N.ID And A.ID='23623320' And N.NOME = 'SUIN' UNION Select 'REQ3',NULL,NULL,to_char(D.ID),pack_bure.labldestfinc(I.INTE),to_char(B.ID),substr(pack_bure2.liblprestree1(B.ID),15),NULL,NULL,NULL From INTERV I,SERVIC A,PRESTA B,DESINT D Where A.ID_PRES=B.ID(+) and I.ID_SERV=A.ID(+) And I.INTE=D.INTE(+) and I.APPO='23623320' Order By 1,2,3;
Et enfin, voici mon code:
Code:
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
| SET SERVEROUTPUT ON SIZE 100000;
Declare
/*Variables gestion de table GFITEST1 */
vid number(12); /* id de l'enregistrement en cours (GFITEST1.ID)*/
vnumero number(12); /* Numero de la requete (GFITEST1.numero) */
/* Vreq varchar2(3000); Requete (GFITEST1.requete) */
Vreq long; /* Requete (GFITEST1.requete) */
/*variables traitement de calcul */
vndatedebut number(20,6);
vsdatedebut varchar2(20);
vndatefin number(20,6);
vsdatefin varchar2(20);
vntemps number(20,6);
vMsgErr varchar2(200);
/* Variable flag -> true si l'execute immediate se passe bien false sinan */
vbexecuteimmediat boolean;
/* Sélection des requetes SQL*/
cursor C1 is SELECT G.ID,G.NUMERO,G.REQUETE
FROM GFITEST9 G ORDER BY G.NUMERO;
BEGIN
/* Début de la boucle du cursor 1 */
OPEN C1;
LOOP
vbexecuteimmediat := true;
vid := 0;
vnumero := 0;
Vreq := null;
fetch C1 into vid,Vnumero,Vreq;
exit when C1%NOTFOUND;
/********************************************************************************/
/*Affichage du numero de la requete */
/********************************************************************************/
DBMS_OUTPUT.PUT_LINE('Requete numero:' ||Vnumero);
/********************************************************************************/
/* Suppression des caracteres indesirables */
/********************************************************************************/
select translate(vreq,'{',' ') into vreq from dual;
select translate(vreq,'}',' ') into vreq from dual;
select translate(vreq,chr(10),' ') into vreq from dual;
select translate(vreq,chr(13),' ') into vreq from dual;
/*update gfitest1 set requete= vreq where numero=Vnumero;*/
/********************************************************************************/
/* Recuperation heure de debut */
/********************************************************************************/
select to_char(systimestamp, 'SSSS.ff') into vsdatedebut from dual;
vndatedebut := to_number(vsdatedebut,'9999.999999');
/********************************************************************************/
/*Execution de la requete */
/********************************************************************************/
BEGIN
execute immediate Vreq;
EXCEPTION
WHEN OTHERS then
vbexecuteimmediat := false;
vmsgerr := 'SQLCODE = ' || SQLCODE || ' ' || 'SQLERRM:' || SQLERRM;
update gfitest9 set comm=vmsgerr where numero=Vnumero;
commit;
END;
/********************************************************************************/
/* Recuperation heure de fin */
/********************************************************************************/
select to_char(systimestamp, 'SSSS.ff') into vsdatefin from dual;
vndatefin := to_number(vsdatefin,'9999.999999');
/********************************************************************************/
/* Calcul du temps de la requete */
/********************************************************************************/
vntemps := vndatefin - vndatedebut;
/********************************************************************************/
/* Ecriture du temps ou du msg d'erreur dans la table */
/********************************************************************************/
if vbexecuteimmediat = true then
update gfitest9 set comm= vntemps where numero=Vnumero;
commit;
end if;
END LOOP;
CLOSE C1;
exception
when others then
vmsgerr := 'SQLCODE = ' || SQLCODE || ' ' || 'SQLERRM:' || SQLERRM;
DBMS_OUTPUT.PUT_LINE(vmsgerr);
DBMS_OUTPUT.PUT_LINE('Arret du programme - Erreur fatale!');
end;
/ |
Merci d'avance pour tout aide =)
