Oracle stored procedure doesn`t return any value

Question

User-1418894206 posted

Hello!

For some time I was using OLEDB provider to return Oracle stored procedure values. I was adding procedures in DataSet.xsd with execution mode NonQuery and all was working fine.

Now I decided to move to ODP.NET provider. I added stored procedure in DataSet.xsd. When I try to execute it with mode NonQuery I always get integer not returning type. When I try to execute it with mode Scalar I get object but it always is null.

Maybe someone now how to solve this problem. I will be glad to any solution.

Answer 1

User269602965 posted

show the code so users can help

Answer 2

User-1418894206 posted

Note that I have no code for adding procedures. I only have to drop them to .xsd file. Wasn`t anyone working with that kind of files in VS?

But I was trying to execute that procedures through code and they still do not return any value using both providers: ODP.NET and OLEDB.

    public static string GetClientname(string clientID)
    {
        string conStr = ConfigurationManager.ConnectionStrings["P3L_ODP"].ToString();
        OracleConnection conn = new OracleConnection(conStr);
        OracleCommand oraCmd = new OracleCommand("P3L.GET_CLIENTNAME", conn);
        oraCmd.CommandType = CommandType.StoredProcedure;
        oraCmd.Parameters.Add("IN_CLIENTID", OracleDbType.Varchar2, 32768, clientID, ParameterDirection.Input);
        oraCmd.Parameters.Add("IN_FORMAT", OracleDbType.Varchar2, 32768, "VU", ParameterDirection.Input);
        oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 32768, ParameterDirection.ReturnValue);
        conn.Open();
        oraCmd.ExecuteScalar();
        //oraCmd.ExecuteNonQuery();
        string retval = ((OracleString)oraCmd.Parameters["RETURN_VALUE"].Value).ToString();
        //string retval = string.Empty;
        //OracleDataReader rdr = oraCmd.ExecuteReader();
        //if (rdr.Read()) retval = rdr.GetString(0);
        //rdr.Close();
        oraCmd.Dispose();
        conn.Close();
        return retval;
    }

Answer 3

User-1418894206 posted

Ok, I found solution by myself for getting values from code. But if anyone know something about .xsd files post informathion please.

1. Return parameter must be added to oracle command first.
2. Return variable must be set as value for this parameter (note that this doesn`t return value)

   oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 4000, retval, ParameterDirection.ReturnValue);

3. Value is returned this way: 

           oraCmd.ExecuteNonQuery();
           retval = oraCmd.Parameters["RETURN_VALUE"].Value.ToString();

    public static string GetClientname(string clientID)
    {
        string conStr = ConfigurationManager.ConnectionStrings["P3L_ODP"].ToString();
        OracleConnection conn = new OracleConnection(conStr);
        OracleCommand oraCmd = new OracleCommand("P3L.GET_CLIENTNAME", conn);
        oraCmd.CommandType = CommandType.StoredProcedure;
        string retval = string.Empty;
        oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 4000, retval, ParameterDirection.ReturnValue);
        oraCmd.Parameters.Add("IN_CLIENTID", OracleDbType.Varchar2, clientID, ParameterDirection.Input);
        oraCmd.Parameters.Add("IN_FORMAT", OracleDbType.Varchar2, "VU", ParameterDirection.Input);
        conn.Open();
        oraCmd.ExecuteNonQuery();
        retval = oraCmd.Parameters["RETURN_VALUE"].Value.ToString();
        oraCmd.Dispose();
        conn.Close();
        return retval;
    }