Click here to Skip to main content
15,886,199 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
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:
C#
static public void CallingStoredProcedure(string PolygonGeom, int PImklId)
        {


            //my custom object 

            try
            {

                //conection to the database

                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;

                    //first input
                    cmd.Parameters.Add("p_polygon_geom", OracleDbType.Clob).Value = PolygonGeom;

                    cmd.Parameters.Add("p_imkl_id", OracleDbType.Int32).Value = PImklId;

                    //Oracle Parameters

                    //first output
                    OracleParameter objParam = new OracleParameter();

                    objParam.OracleDbType = OracleDbType.Object;

                    objParam.UdtTypeName = "t_t_out_house_details";
                    objParam.Value = houseDetails;
                    //T_T_OUT_HOUSE_DETAILS
                    
                    objParam.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(objParam);

                    //second output
                    OracleParameter op = null;
                     op = new OracleParameter("p_return_cd", OracleDbType.Int32);
                    op.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(op);
                   // cmd.BindByName = false;
                    //Taking instance from my object 
                    
                    //execute my command
                    cmd.ExecuteNonQuery();
                    houseDetails = (t_out_house_details)objParam.Value;
                }


                ////Console.WriteLine(cmd.Parameters["p_return_cd"].Value);
            }
                catch(OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                //throw new Exception("Error " + 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
C#
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 :
C#
OracleParameter objParam = new OracleParameter();

                    objParam.OracleDbType = OracleDbType.Object;

                    objParam.UdtTypeName = "t_t_out_house_details";
                    objParam.Value = houseDetails;
                    //T_T_OUT_HOUSE_DETAILS
                    
                    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.
Posted
Updated 6-Feb-18 3:14am
v2
Comments
Joan Magnet 26-Mar-15 10:04am    
Have you searched in codeproject?

http://www.codeproject.com/Articles/141728/Interaction-between-C-Application-and-Oracle-throu
Hesham el Masry 27-Mar-15 2:18am    
yes yes but its not working
Member 13752818 11-May-18 6:13am    
have you found a solution for this issue?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900