Call procedure from C#:
DataSet ds = new DataSet();
using (OracleConnection con = new OracleConnection(WebConfigurationManager.ConnectionStrings["DataContext"].ConnectionString))
{
using (OracleCommand cmd = new OracleCommand("CS_SERVICEREQUEST_PUB.GET_SR_NOTES_DETAILS", con))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
OracleParameter p_api_version = cmd.Parameters.Add("p_api_version", OracleDbType.Int32);
p_api_version.Direction = ParameterDirection.Input;
p_api_version.Value = 4;
OracleParameter p_incident_id = cmd.Parameters.Add("p_incident_id", OracleDbType.Int32);
p_incident_id.Direction = ParameterDirection.Input;
p_incident_id.Value = 860636;
OracleParameter p_incident_number = cmd.Parameters.Add("p_incident_number", OracleDbType.Varchar2, 50);
p_incident_number.Direction = ParameterDirection.Input;
p_incident_number.Value = "480777";
OracleParameter x_notes = cmd.Parameters.Add("x_notes", null); // having issue with this
x_notes.Direction = ParameterDirection.Output;
OracleParameter x_return_status = cmd.Parameters.Add("x_return_status", OracleDbType.Varchar2, 50);
x_return_status.Direction = ParameterDirection.Output;
OracleParameter x_msg_count = cmd.Parameters.Add("x_msg_count", OracleDbType.Varchar2, 50);
x_msg_count.Direction = ParameterDirection.Output;
OracleParameter x_msg_data = cmd.Parameters.Add("x_msg_data", OracleDbType.Varchar2, 50);
x_msg_data.Direction = ParameterDirection.Output;
using (OracleTransaction tran = con.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
using (OracleDataAdapter da = new OracleDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(ds);
}
tran.Commit();
}
catch (OracleException ex)
{
tran.Rollback();
}
finally
{
con.Close();
}
}
}
}
Procedure in Oracle apps:
PROCEDURE GET_SR_NOTES_DETAILS
( p_api_version IN NUMBER,
p_incident_id IN NUMBER ,
p_incident_number IN VARCHAR2,
x_notes OUT NOCOPY sr_notes_det_table,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_incident_id number := null;
l_sr_found number := 1;
l_api_name VARCHAR2(100) := 'CS_SERVICEREQUEST_PUB.GET_SR_NOTES_DETAILS';
l_count NUMBER := 0;
--l_tasks tasks_table;
--l_notes notes_table;
--l_sr_rec sr_records_cur%ROWTYPE;
BEGIN
--initialize the msg count to 0
x_msg_count := 0;
x_msg_data := null;
IF (p_incident_id IS NULL) AND (p_incident_number IS NOT NULL) THEN
BEGIN
select incident_id into l_incident_id
from cs_incidents_all_b
where incident_number = p_incident_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_sr_found := 0;
END;
ELSE
l_incident_id := p_incident_id;
END IF;
IF l_sr_found = 1 THEN
BEGIN
select a.NOTES,
a.NOTES_DETAIL,
a.NOTE_TYPE,
a.NOTE_TYPE_MEANING,
a.NOTE_STATUS,
a.NOTE_STATUS_MEANING,
a.CREATED_BY,
a.creation_date,
a.last_update_date,
DECODE(b.employee_id,NULL,(select d.party_name from hz_parties d where d.party_id=b.person_party_id),
DECODE(b.person_party_id,NULL,NULL,(select c.FULL_NAME from hr_employees c where c.employee_id=b.employee_id))) CREATED_BY_NAME,
DECODE(b.employee_id,NULL,'CUSTOMER','AGENT') CREATED_BY_USER_TYPE,
b.PERSON_PARTY_ID CONTACT_PARTY_ID
bulk collect into x_notes
from jtf_notes_vl a,fnd_user b
where a.source_object_id=l_incident_id AND a.source_object_code='SR' AND a.created_by = b.user_id
order by a.creation_date desc;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_return_status := 'S';
END;
END IF;
x_return_status := 'S';
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
x_return_status := FND_API.G_RET_STS_ERROR;
FND_MSG_PUB.Count_And_Get
( p_count => x_msg_count,
p_data => x_msg_data
);
WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
FND_MSG_PUB.Count_And_Get
( p_count => x_msg_count,
p_data => x_msg_data
);
WHEN OTHERS THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
END IF;
FND_MSG_PUB.Count_And_Get
( p_count => x_msg_count,
p_data => x_msg_data
);
END GET_SR_NOTES_DETAILS;
--End changes for get sr related info
END CS_ServiceRequest_PUB;
Unable to call this procedure from c# because I did not find any oracledBtype which is used for
x_notes OUT NOCOPY sr_notes_det_table
Here code for sr_notes_det_table
TYPE SR_NOTES_DET_REC IS RECORD (
NOTES JTF_NOTES_VL.NOTES%TYPE ,
NOTES_DETAIL JTF_NOTES_VL.NOTES_DETAIL%TYPE ,
NOTE_TYPE JTF_NOTES_VL.NOTE_TYPE%TYPE ,
NOTE_TYPE_MEANING JTF_NOTES_VL.NOTE_TYPE_MEANING%TYPE ,
NOTE_STATUS JTF_NOTES_VL.NOTE_STATUS%TYPE ,
NOTE_STATUS_MEANING JTF_NOTES_VL.NOTE_STATUS_MEANING%TYPE ,
CREATED_BY JTF_NOTES_VL.CREATED_BY%TYPE,
CREATION_DATE JTF_NOTES_VL.CREATION_DATE%TYPE,
last_update_date JTF_NOTES_VL.last_update_date%TYPE,
CREATED_BY_NAME VARCHAR2(240),
CREATED_BY_USER_TYPE VARCHAR2(30),
CONTACT_PARTY_ID NUMBER);
TYPE sr_notes_det_table IS TABLE OF SR_NOTES_DET_REC INDEX BY BINARY_INTEGER;
in my procedure call I have only problem with this out parameter
OracleParameter x_notes = cmd.Parameters.Add("x_notes", null);
x_notes.Direction = ParameterDirection.Output;
How can I captured this output parameter value in c#. please help or guide me how can I implement this correctly into the C#.