Bonjour à tous,

Voici mon problème, j'utilise actuellement un procédure stockée

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
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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
PROCEDURE S_RECHERCHE_INTRANET_NC (
	curseur  OUT			T_Cursor,
	nbrrec	OUT			T_NbrRec,
	nom						MCIC.NC_CONTACT.FAM_NM%TYPE,
	prenom					MCIC.NC_CONTACT.VOOR_NM%TYPE,
	numrel					MCIC.NC_CONTACT.KLN_NR%TYPE,
	numcpt					MCIC.NC_COMPTE.REK_NR%TYPE,
	numtva					MCIC.NC_CONTACT.BTW_NR%TYPE,
	tcpid						MCIC.NC_CONTACT.PDOM_LAND_POST_KD%TYPE,
	contactid				MCIC.NC_CONTACT.KLN_NR%TYPE,
	dans_mes_contacts		T_String
 
) AS
	sql_stmt				VARCHAR2(2000);
	sql_cond				VARCHAR2(5000);
	sql_from				VARCHAR2(3000);
	sql_stmt3				VARCHAR2(2000);
	sql_cond3				VARCHAR2(5000);
	sql_from3				VARCHAR2(3000);
	sql_stmt2				VARCHAR2(2000);
	avant					VARCHAR2(30);
	apres					VARCHAR2(30);
	Login					MCIC.TYPE_UTILISATEUR.TU_LOGIN%TYPE;
	UserID				MCIC.TYPE_UTILISATEUR.TUID%TYPE;
	AllTCon				CHAR(1);
	AllTInd				CHAR(1);
BEGIN
	Login:=GetUserName();
	avant:='áàâäéèêëíìîïóòôöúùûüýÿ'; -- sert pour exclure les accents
	apres:='aaaaeeeeiiiioooouuuuyy'; -- 	des recherches
 
	-- sql_stmt: contient la requete pour la recherche (avant la clause WHERE)
	sql_stmt:='SELECT DISTINCT NC_CONTACT.CONTID AS "Id", DECODE(NC_CONTACT.SRT_KD, ''N'', VOOR_NM, D2_NM) AS "CONT_PRENOM", DECODE(NC_CONTACT.SRT_KD, ''N'', FAM_NM, D1_NM) AS "CONT_NOM", NC_CONTACT.KLN_NR AS "NUM_CLIENT",''O'' AS "COAD_ADRESSE_DIRECTE",';
	sql_stmt:=sql_stmt || ' (SELECT DISTINCT STATUS FROM MCIC.NC_CONTACTPDV WHERE NC_CONTACTPDV.KLN_NR=NC_CONTACT.KLN_NR) AS CONT_CLIENT, (SELECT NUM_SOUS_MARCHE || '' - '' || NOM_SOUS_MARCHE FROM MCIC.NC_CONTACTMARCHE WHERE NC_CONTACT.KLN_NR=NC_CONTACTMARCHE.KLN_NR) AS TCON_CODE,';
	sql_stmt:=sql_stmt || ' CTR_NR AS TIND_CODE,(SELECT NC_PDV.CTR_NM FROM MCIC.NC_PDV WHERE NC_CONTACTPDV.CTR_NR=NC_PDV.CTR_NR) AS TIND_DESC,';
	sql_stmt:=sql_stmt || ' RTRIM(LTRIM(PDOM_STR_NM)) || '', '' || RTRIM(LTRIM(PDOM_HUIS_NR)) || '' '' || RTRIM(LTRIM(PDOM_BUS_NR)) AS "Adresse",';
	sql_stmt:=sql_stmt || ' RTRIM(LTRIM(PDOM_LAND_POST_KD)) || '' '' || RTRIM(LTRIM(PDOM_GEM_NM)) AS "Localité"';
	-- sql_stmt:=sql_stmt || 'PMAIL_STR_NM || '', '' || PMAIL_HUIS_NR || '' '' || PMAIL_BUS_NR AS "Adresse",';
	-- sql_stmt:=sql_stmt || 'PMAIL_LAND_POST_KD || '' '' || PMAIL_GEM_NM AS "Localité"';
 
	sql_from:=' FROM MCIC.NC_CONTACT, MCIC.NC_CONTACTPDV';
 
 
	-- sql_cond et sql_cond: contiennent les critères de recherche (à partir du WHERE)
	sql_cond:=' WHERE ROWNUM<=50 AND NC_CONTACT.KLN_NR=NC_CONTACTPDV.KLN_NR';
 
 
	IF numrel IS NOT NULL THEN
		sql_cond:=sql_cond || ' AND NC_CONTACT.KLN_NR=''' || UPPER(numrel) ||'''';
	END IF;
	IF contactid IS NOT NULL THEN
		sql_cond:=sql_cond || ' AND NC_CONTACT.KLN_NR=''' || UPPER(contactid) ||'''';
	END IF;
	IF numtva IS NOT NULL THEN
		sql_cond:=sql_cond || ' AND BTW_NR=''' || REPLACE(numtva, '.','') || '''';
	END IF;
	IF numcpt IS NOT NULL THEN
		sql_from:=sql_from || ', MCIC.NC_COMPTE';
		sql_cond:=sql_cond || ' AND (NC_CONTACT.KLN_NR=NC_COMPTE.KLN_NR AND NC_COMPTE.REK_NR=''' || REPLACE(numcpt, '-','') || ''')';
	END IF;
	IF tcpid IS NOT NULL THEN
		sql_cond:=sql_cond || ' AND PDOM_LAND_POST_KD=''' || TO_CHAR(tcpid) || '''';
	END IF;
	IF nom IS NOT NULL THEN
		sql_cond:=sql_cond || ' AND (TRANSLATE(LOWER(FAM_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(nom),avant,apres) || '''';
		sql_cond:=sql_cond || ' OR TRANSLATE(LOWER(D1_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(nom),avant,apres) || ''')';
	END IF;
	IF prenom IS NOT NULL THEN
		sql_cond:=sql_cond || ' AND (TRANSLATE(LOWER(VOOR_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(prenom),avant,apres) || '''';
		sql_cond:=sql_cond || ' OR TRANSLATE(LOWER(D2_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(prenom),avant,apres) || ''')';
	END IF;
 
		INSERT INTO MCIC.SQLDEBUG(
			DATUM,
			SELECTION,
			CLAUSE_FROM,
			CLAUSE_WHERE)
		VALUES(
			SYSDATE,
			sql_stmt,
			sql_from,
			sql_cond);
 
			COMMIT;
 
	sql_stmt3:='SELECT DISTINCT NC_CONTACT.CONTID AS "Id", DECODE(NC_CONTACT.SRT_KD, ''N'', VOOR_NM, D2_NM) AS "CONT_PRENOM", DECODE(NC_CONTACT.SRT_KD, ''N'', FAM_NM, D1_NM) AS "CONT_NOM", NC_CONTACT.KLN_NR AS "NUM_CLIENT",''O'' AS "COAD_ADRESSE_DIRECTE",';
	sql_stmt3:=sql_stmt3 || ' (SELECT DISTINCT STATUS FROM MCIC.NC_CONTACTPDV WHERE NC_CONTACTPDV.KLN_NR=NC_CONTACT.KLN_NR) AS CONT_CLIENT, (SELECT NUM_SOUS_MARCHE || '' - '' || NOM_SOUS_MARCHE FROM MCIC.NC_CONTACTMARCHE WHERE NC_CONTACT.KLN_NR=NC_CONTACTMARCHE.KLN_NR) AS TCON_CODE,';
	sql_stmt3:=sql_stmt3 || ' CTR_NR AS TIND_CODE, (SELECT NC_PDV.CTR_NM FROM MCIC.NC_PDV WHERE NC_CONTACTPDV.CTR_NR=NC_PDV.CTR_NR) AS TIND_DESC,';
	sql_stmt3:=sql_stmt3 || ' RTRIM(LTRIM(PMAIL_STR_NM)) || '', '' || RTRIM(LTRIM(PMAIL_HUIS_NR)) || '' '' || RTRIM(LTRIM(PMAIL_BUS_NR)) AS "Adresse",';
	sql_stmt3:=sql_stmt3 || ' RTRIM(LTRIM(PMAIL_LAND_POST_KD)) || '' '' || RTRIM(LTRIM(PMAIL_GEM_NM)) AS "Localité"';
 
	sql_from3:=' FROM MCIC.NC_CONTACT, MCIC.NC_CONTACTPDV';
 
 
	-- sql_cond et sql_cond: contiennent les critères de recherche (à partir du WHERE)
	sql_cond3:=' WHERE ROWNUM<=50 AND NC_CONTACT.KLN_NR=NC_CONTACTPDV.KLN_NR';
 
 
	IF numrel IS NOT NULL THEN
		sql_cond3:=sql_cond3 || ' AND NC_CONTACT.KLN_NR=''' || UPPER(numrel) ||'''';
	END IF;
	IF contactid IS NOT NULL THEN
		sql_cond3:=sql_cond3 || ' AND NC_CONTACT.KLN_NR=''' || UPPER(contactid) ||'''';
	END IF;
	IF numtva IS NOT NULL THEN
		sql_cond3:=sql_cond3 || ' AND BTW_NR=''' || REPLACE(numtva, '.','') || '''';
	END IF;
	IF numcpt IS NOT NULL THEN
		sql_from3:=sql_from3 || ', MCIC.NC_COMPTE';
		sql_cond3:=sql_cond3 || ' AND (NC_CONTACT.KLN_NR=NC_COMPTE.KLN_NR AND NC_COMPTE.REK_NR=''' || REPLACE(numcpt, '-','') || ''')';
	END IF;
	IF tcpid IS NOT NULL THEN
		sql_cond3:=sql_cond3 || ' AND PDOM_LAND_POST_KD=''' || TO_CHAR(tcpid) || '''';
	END IF;
	IF nom IS NOT NULL THEN
		sql_cond3:=sql_cond3 || ' AND (TRANSLATE(LOWER(FAM_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(nom),avant,apres) || '''';
		sql_cond3:=sql_cond3 || ' OR TRANSLATE(LOWER(D1_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(nom),avant,apres) || ''')';
	END IF;
	IF prenom IS NOT NULL THEN
		sql_cond3:=sql_cond3 || ' AND (TRANSLATE(LOWER(VOOR_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(prenom),avant,apres) || '''';
		sql_cond:=sql_cond || ' OR TRANSLATE(LOWER(D2_NM),''' || avant || ''',''' || apres || ''') LIKE ''' || TRANSLATE(LOWER(prenom),avant,apres) || ''')';
	END IF;
 
 
 
	INSERT INTO MCIC.SQLDEBUG(
			DATUM,
			SELECTION,
			CLAUSE_FROM,
			CLAUSE_WHERE)
		VALUES(
			SYSDATE,
			sql_stmt3,
			sql_from3,
			sql_cond3);
 
 
OPEN curseur FOR sql_stmt || sql_from ||  sql_cond || ' UNION ' || sql_stmt3 || sql_from3 ||  sql_cond3 || ' ORDER BY CONT_NOM, CONT_PRENOM';
 
 
 
 
	EXCEPTION
		WHEN VALUE_ERR THEN
			RAISE_APPLICATION_ERROR(Value_ErrCode,Value_ErrMsg,TRUE);
		WHEN OTHERS THEN
			RAISE;
END S_RECHERCHE_INTRANET_NC;
Le problème vient du TIND_CODE car il peut y en avoir plusieurs, donc à l'affichage les informations sont doublées ou triplées car il peut y avoir 2 ou 3 indices.

Est ce qu'on ne pourrait pas passer par une deuxième requête et donc un deuxième curseurs juste pour lister les indices.

Est ce une piste envisageable ???

Si oui auriez vous une petite info car j'ai essayé plusieurs solutions mais j'ai toujours des erreurs !!

D'avance merci à tous