[10g] - ADO.net - C# 3.5 Paramètre de sortie pour remplir un datareader
Salut,
J'ai un problème avec une procédure qui est censée récupérer (ou créer) un enregistrement pour le retourner dans un datareader.
Je sais que je pourrais ne pas utiliser un datareader mais j'aimerais comprendre pourquoi ça ne fonctionne pas.
Voici l'extrait du package (je precise que le package compile et la procedure s'execute dans TOAD).
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
|
PROCEDURE GetCreate_BHR_BREACH_MAIN(
pISIN IN VARCHAR2,
pPTF_ID IN VARCHAR2 := null,
pBHR_BREACH_MAIN_Row OUT SYS_REFCURSOR
)
IS
vID_BREACH_MAIN NUMBER := null;
vRETURN_CURSOR SYS_REFCURSOR;
BEGIN
IF(pPTF_ID is null) THEN
BEGIN
SELECT
BHR_BREACH_MAIN.ID_BREACH_MAIN
INTO
vID_BREACH_MAIN
FROM
BHR_BREACH_MAIN
WHERE
BHR_BREACH_MAIN.ISIN = pISIN AND BHR_BREACH_MAIN.PTF_ID is null;
exception
when NO_DATA_FOUND then
vID_BREACH_MAIN := null;
when OTHERS then
RAISE;
END;
ELSE
BEGIN
SELECT
BHR_BREACH_MAIN.ID_BREACH_MAIN
INTO
vID_BREACH_MAIN
FROM
BHR_BREACH_MAIN
WHERE
BHR_BREACH_MAIN.ISIN = pISIN AND BHR_BREACH_MAIN.PTF_ID = pPTF_ID;
exception
when NO_DATA_FOUND then
vID_BREACH_MAIN := null;
when OTHERS then
RAISE;
END;
END IF;
IF (vID_BREACH_MAIN is null) THEN
--Il faut créer l'enregistrement avant
INSERT INTO BHR_BREACH_MAIN (
ISIN,
PTF_ID,
IS_ARCHIVED
) VALUES (
pISIN,
pPTF_ID,
'N'
) returning BHR_BREACH_MAIN.ID_BREACH_MAIN into vID_BREACH_MAIN;
END IF;
OPEN vRETURN_CURSOR FOR
SELECT
B.*
FROM
BHR_BREACH_MAIN B
WHERE
B.ID_BREACH_MAIN = vID_BREACH_MAIN;
pBHR_BREACH_MAIN_Row := vRETURN_CURSOR;
END; |
voici le bout de code C#
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
|
oCmd.CommandText = "FIR1.PAK_BHR.ClosePositionPeriod";
foreach (var P in PeriodsToClose)
{
oCmd.Parameters.Clear();
oCmd.Parameters.AddWithValue("pISIN", P.ISIN);
oCmd.Parameters.AddWithValue("pPTF", P.Portfolio);
oCmd.Parameters.AddWithValue("pThresholdType", Tools.EnumTypeTo<int>(P.ThresholdType));
oCmd.ExecuteNonQuery();
}
oCmd.CommandText = "FIR1.PAK_BHR.GetCreate_BHR_BREACH_MAIN";
foreach (var Threshold in Thresholds) {
oCmd.Parameters.Clear();
oPmt = new OracleParameter();
oPmt.DbType = DbType.String;
oPmt.IsNullable = false;
oPmt.OracleType = OracleType.NVarChar;
oPmt.ParameterName = "pISIN";
oPmt.Value = Threshold.ISIN;
oPmt.Direction = ParameterDirection.Input;
oCmd.Parameters.Add(oPmt);
oPmt = new OracleParameter();
oPmt.DbType = DbType.String;
oPmt.IsNullable = true;
oPmt.OracleType = OracleType.NVarChar;
oPmt.ParameterName = "pPTF_ID";
if (Threshold.Portfolio == null)
{
oPmt.Value = DBNull.Value;
}
else
{
oPmt.Value = Threshold.Portfolio;
}
oPmt.Direction = ParameterDirection.Input;
oCmd.Parameters.Add(oPmt);
oCmd.Parameters.Add("pBHR_BREACH_MAIN_Row", OracleType.Cursor).Direction = ParameterDirection.Output;
oDr = oCmd.ExecuteReader();
if (oDr.Read())
{
pID_BREACH_MAIN = int.Parse(oDr["ID_BREACH_MAIN"].ToString());
} |
J'avoue que je cale...
D'avance merci pour votre aide,
Laurent
