hello i have a problem when i call my stored procedure from c# , In my oracle stored procedure that i'm calling i have 2 inputs and two outputs ,
my inputs the first input is CLOB means it will passs string in c# code and the second input is number.
my outputs first out is a defined
object in my stored procedure
t_t_out_house_details and this object returns 10 values, the second output is number.
my stored procedure:
PROCEDURE prc_get_impacted_houses(p_polygon_geom IN CLOB,
p_imkl_id IN NUMBER,
p_out_house_details OUT t_t_out_house_details,
p_return_cd OUT NUMBER);
im calling this stored procedure and passing these two inputs using c# like that:
static public void CallingStoredProcedure(string PolygonGeom, int PImklId)
{
try
{
using (OracleConnection conn = new OracleConnection("Data Source=; User ID=; Password="))
{
t_out_house_details houseDetails = new t_out_house_details();
OracleCommand cmd = new OracleCommand();
conn.Open();
cmd = new OracleCommand("prc_get_impacted_houses", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_polygon_geom", OracleDbType.Clob).Value = PolygonGeom;
cmd.Parameters.Add("p_imkl_id", OracleDbType.Int32).Value = PImklId;
OracleParameter objParam = new OracleParameter();
objParam.OracleDbType = OracleDbType.Object;
objParam.UdtTypeName = "t_t_out_house_details";
objParam.Value = houseDetails;
objParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(objParam);
OracleParameter op = null;
op = new OracleParameter("p_return_cd", OracleDbType.Int32);
op.Direction = ParameterDirection.Output;
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
houseDetails = (t_out_house_details)objParam.Value;
}
}
catch(OracleException ex)
{
Console.WriteLine(ex.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
also i have a c# object where i want to store my returning values from this stored procedure object to it.
my c# object
public class t_out_house_details
{
public string NET_CD { get; set; }
public int SUBNET_NR { get; set; }
public int CABLE_NUMBER { get; set; }
public int MK { get; set; }
public int KG { get; set; }
public int KS { get; set; }
public string HOUSENR_FIRST { get; set; }
public string HOUSENR_FIRST_SUFFIX { get; set; }
public string GEOMETRY { get; set; }
public string PROJ_LINE_GEOM { get; set; }
}
my problem that my code cant read the object type im defining using UTD user defind types.
my ode stuck here :
OracleParameter objParam = new OracleParameter();
objParam.OracleDbType = OracleDbType.Object;
objParam.UdtTypeName = "t_t_out_house_details";
objParam.Value = houseDetails;
objParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(objParam);
and give me this error: that he cant find my object :
OCI-22303: type ""."my stored procedure object type" not found
i want to know wht is the problem
im pasing a string and int and i tested this stored procedure on pl sql its working fine no errors.