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_FILL"


Here is the Code:

Command.Connection = connection;
Command.CommandText="deptree_fill".ToUpper();
Command.CommandType = CommandType.StoredProcedure;

OracleParameter par1 = new OracleParameter("objectType", OracleType.Char);
par1.Direction = ParameterDirection.Input;
par1.DbType = DbType.String;
par1.Value = new OracleString(objectType); Command.Parameters.Add(par1);

OracleParameter par2 = new OracleParameter("objectOwner", OracleType.Char);
par2.Direction = ParameterDirection.Input;
par2.DbType = DbType.String;
par2.Value = new OracleString(objectOwner); Command.Parameters.Add(par2);

OracleParameter par3 = new OracleParameter("objectName", OracleType.Char);
par3.Direction = ParameterDirection.Input;
par3.DbType = DbType.String;
par3.Value = new OracleString(objectName); Command.Parameters.Add(par3);

OracleParameter par4 = new OracleParameter("out", OracleType.Number);
par4.Direction = ParameterDirection.Output;
par4.DbType = DbType.VarNumeric;
Command.Parameters.Add(par4);

Command.ExecuteNonQuery();



Here is the error message:

ORA-06550: LINE1,COLUMN7;
PLS-00306:WRONG NUMBER OR TYPES OF ARGUMENTS IN CALL TO
"DEPTREE_FILL"
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]

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;
        }