0

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;

            }
        }

    }
Dionis
  • 73
  • 7
  • 1
    Where is the code for the stored procedure? You have provided an anonymous pl/sql block that calls `DBMS_OUTPUT` functions - the output from this will not (by default) be sent to the client. – MT0 Jan 18 '17 at 10:43
  • The code of stored procedure is pretty long, many procedures are called and then the output is modified and returned in dbms_output. So, I tried to get it from there, reading in some suggestions that you could get it from dbms_output.getline( :parameter, :status). Should i return it from a stored procedure, is there no way to send it to client with DBMS_OUTPUT? – Dionis Jan 18 '17 at 10:47
  • 1
    "Should i return it from a stored procedure?" **yes** "is there no way to send it to client with DBMS_OUTPUT?" **Not easily** - see the [ask tom Java solution](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45027262935845) – MT0 Jan 18 '17 at 10:51
  • Okay man, I am trying to return it from a stored procedure. Thanks a lot :) – Dionis Jan 18 '17 at 10:53
  • Still the same problem even with the procedure, it returns "1"... – Dionis Jan 18 '17 at 13:17

0 Answers0