I am trying to get a string from a stored procedure in C# .NET from Oracle PL/SQL. The query is OK in Oracle SQL Developer, but the output parameter in C# will always return "1". I have tried setting different parameter size, direction, etc. Nothing helps. It will either return "1"/null or throw an error. Also I added an INT32 param just for testing, and it will return null every time. Above is a simplified testing query and the C# code. Being struggling for two days now on this silly thing.
string sql = @"
DECLARE
eligProdFinal1 nvarchar2(128);
status integer;
testNR integer;
BEGIN
--dbms_output.put_line( eligProdFinal1 || '|' || eligProdFinal2 || '|' || eligProdFinal3 );
dbms_output.put_line('TEST');
eligProdFinal1 := '';
dbms_output.get_line( :eligProdFinal1, :status ); --status 0 is OK
dbms_output.put_line(37);
dbms_output.get_line( :testNR, :status ); --status 0 is OK
END;
";
.NET:
//added to get DBMS Ouput Line from a query in PL/SQL ORACLE
public static string GetDbmsOutputLine(string sqlExp)
{
string dbConSAPCCDEV = "...";
using (var connection = new OracleConnection() { ConnectionString = dbConSAPCCDEV })
{
using (OracleCommand command = new OracleCommand())
{
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = string.Format(sqlExp);
connection.Open();
OracleParameter statusParameter = new OracleParameter();
statusParameter.ParameterName = "status";
statusParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(statusParameter);
//tried adding the param like this, or in a single line like below
//OracleParameter lineParameter = new OracleParameter();
//lineParameter.ParameterName = "eligProdFinal1";
//lineParameter.OracleDbType = OracleDbType.Varchar2;
//lineParameter.Size = 760;
//lineParameter.Direction = ParameterDirection.ReturnValue;
//command.Parameters.Add(lineParameter);
command.Parameters.Add(new OracleParameter("eligProdFinal1", OracleDbType.NVarchar2, 128, null, ParameterDirection.Output));
OracleParameter testParameter = new OracleParameter();
testParameter.ParameterName = "testNR";
testParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(testParameter);
command.ExecuteNonQuery();
if (command.Parameters["eligProdFinal1"].Value is DBNull)
return null;
string output = command.Parameters["eligProdFinal1"].Value.ToString();
string testNr = command.Parameters["testNR"].Value.ToString();
connection.Close();
return output;
}
}
}