Call procedure of Oracle apps by C# not working

122 Views Asked by At

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#.

0

There are 0 best solutions below