Cannot get return value from Stored Procedure

[mjh66]

I have an Oracle databases that has a stored procedure which accepts a string. This return is just a simple message of varchar2 type.

Can someone please tell me what I am doing wrong in the C# code below? - it fails on the ExecuteNonQuery()

Thanks in advance.

if (connection != null)
{
try
{
// Create the Oracle command (as Stored Procedure)
OracleCommand myCmd2 = new OracleCommand("INSERT_PID_XML",connection);
myCmd2.CommandType = CommandType.StoredProcedure;

// Pass parameters to the Stored Procedure
OracleParameter myParam1 = myCmd2.Parameters.Add("P_DATA",OracleDbType.Clob);
myParam1.Direction = ParameterDirection.Input;
myParam1.Value = xmldata;

OracleParameter myParam2 = myCmd2.Parameters.Add("P_ERROR_MSG",OracleDbType.Varchar2);
myParam2.Direction = ParameterDirection.Output;

// Execute command and return error code
myCmd2.ExecuteNonQuery();

string error = (string) myParam2.Value;
if (error != "&quot
{
return error;
}
else
{
return "0";
}

}
catch (Exception e)
{
return e.ToString();
}
finally
{
connection.Close();
}
}
else
{
return "3";
}

 

[SHelton]

Unless I haven't read your code correctly, you don't seem to have opened the connection.

The connection must already be open before executing a command, unless you are using a DataAdapter.

 

[mjh66]

Yeh - the connection is open.
The 'connection' at the top is the return connection object from another routine. If it's not null, i.e. a connection has been made then this code runs.

 

[mjh66]

I've cracked it. Code below ....

OracleParameter p_data = new OracleParameter("P_DATA",OracleDbType.Clob);
p_data.Direction = ParameterDirection.Input;
p_data.Value = xmldata;
myCmd.Parameters.Add(p_data);

OracleParameter p_error_msg = new OracleParameter("P_ERROR_MSG",OracleDbType.Varchar2);
p_error_msg.Direction = ParameterDirection.Output;
//p_error_msg.Value = "x";
p_error_msg.Size=1000;
myCmd.Parameters.Add(p_error_msg);

// Execute command and return error code
myCmd.ExecuteNonQuery();

OracleString x = (OracleString) p_error_msg.Value;
string error = x.ToString();