Calling Oracle Stored Procedure from c#.net 2.0

[Muhammad Intikhab Qaiser]

Hi
I have following code and problem in calling the oracle stored procedure "DEPTREE_FI LL"


Here is the Code:

Command.Connect ion = connection;
Command.Command Text="deptree_f ill".ToUpper() ;
Command.Command Type = CommandType.Sto redProcedure;

OracleParameter par1 = new OracleParameter ("objectType ", OracleType.Char );
par1.Direction = ParameterDirect ion.Input;
par1.DbType = DbType.String;
par1.Value = new OracleString(ob jectType); Command.Paramet ers.Add(par1);

OracleParameter par2 = new OracleParameter ("objectOwne r", OracleType.Char );
par2.Direction = ParameterDirect ion.Input;
par2.DbType = DbType.String;
par2.Value = new OracleString(ob jectOwner); Command.Paramet ers.Add(par2);

OracleParameter par3 = new OracleParameter ("objectName ", OracleType.Char );
par3.Direction = ParameterDirect ion.Input;
par3.DbType = DbType.String;
par3.Value = new OracleString(ob jectName); Command.Paramet ers.Add(par3);

OracleParameter par4 = new OracleParameter ("out", OracleType.Numb er);
par4.Direction = ParameterDirect ion.Output;
par4.DbType = DbType.VarNumer ic;
Command.Paramet ers.Add(par4);

Command.Execute NonQuery();



Here is the error message:

ORA-06550: LINE1,COLUMN7;
PLS-00306:WRONG NUMBER OR TYPES OF ARGUMENTS IN CALL TO
"DEPTREE_FI LL"
ORA-06550:LINE1, COLUMN7;
PL/SQL:STATEMENT IGNORED

[debasisdas]

Hi
muhammad
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.


Please follow posting guidelines and use code tags to make your post more readable.


It seems yout C# code is ok
but what bout the procedure in oracle.
Is it compiled with out error.

Can you please post the code of the PROCEDURE for my reference.

[AdamHung]

You can use template code the following such as:

 
public int SG_SEG_GROUP_INSERT(SG_SEG_GROUP Entity)
        {
            int intResult = 1;
            try
            {

                OracleParameter OraParm;
                OracleConnection DbConn = GetOracleConnection(strOrcConnection);
                OracleCommand OraCmd = new OracleCommand(PRO_SG_SEG_GROUP_INSERT, DbConn);
                OraCmd.CommandType = CommandType.StoredProcedure;

                OraParm = new OracleParameter(PARAM_GRP_TP_ID_SRC, OracleType.VarChar, 64);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.GRP_TP_ID_SRC;
                OraCmd.Parameters.Add(OraParm);

                OraParm = new OracleParameter(PARAM_GRP_CODE, OracleType.VarChar, 20);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.GRP_CODE;
                OraCmd.Parameters.Add(OraParm);

                OraParm = new OracleParameter(PARAM_GRP_NM, OracleType.VarChar, 64);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.GRP_NM;
                OraCmd.Parameters.Add(OraParm);

                OraParm = new OracleParameter(PARAM_DSC, OracleType.VarChar, 255);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.DSC;
                OraCmd.Parameters.Add(OraParm);

                DbConn.Open();
                OraCmd.ExecuteNonQuery();
                DbConn.Dispose();
            }
            catch (Exception ex)
            {
                intResult = -1;
            }
            return intResult;
        }

[AdamHung]

using orcale procedure

public int SG_SEG_GROUP_INSERT(SG_SEG_GROUP Entity)
        {
            int intResult = 1;
            try
            {

                OracleParameter OraParm;
                OracleConnection DbConn = GetOracleConnection(strOrcConnection);
                OracleCommand OraCmd = new OracleCommand(PRO_SG_SEG_GROUP_INSERT, DbConn);
                OraCmd.CommandType = CommandType.StoredProcedure;

                OraParm = new OracleParameter(PARAM_GRP_TP_ID_SRC, OracleType.VarChar, 64);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.GRP_TP_ID_SRC;
                OraCmd.Parameters.Add(OraParm);

                OraParm = new OracleParameter(PARAM_GRP_CODE, OracleType.VarChar, 20);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.GRP_CODE;
                OraCmd.Parameters.Add(OraParm);

                OraParm = new OracleParameter(PARAM_GRP_NM, OracleType.VarChar, 64);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.GRP_NM;
                OraCmd.Parameters.Add(OraParm);

                OraParm = new OracleParameter(PARAM_DSC, OracleType.VarChar, 255);
                OraParm.Direction = ParameterDirection.Input;
                OraParm.Value = Entity.DSC;
                OraCmd.Parameters.Add(OraParm);

                DbConn.Open();
                OraCmd.ExecuteNonQuery();
                DbConn.Dispose();
            }
            catch (Exception ex)
            {
                intResult = -1;
            }
            return intResult;
        }