Oracle 12c, Stored Procedure OUT Parameter string message Breaks

Question

I created a dummy package contain two input and one output. while saving i should get "SUCCESS" but it returns "SU".
Oracle SP

PROCEDURE savedetail (  p_msg  OUT VARCHAR2,                           
                         inputs...
                         )    begin  p_msg := 'SUCCESS';   END;

C# code

public string SaveLoad(DataGL.ScheduledMaintenance objmaster)
    {
        try
        {
            this.AppConnection.OpenConnection();
            this.AppConnection.BeginTransaction();
            cmd = this.AppConnection.Connection.CreateCommand();
            cmd.Transaction = this.AppConnection.Transaction;
            cmd.CommandText = "PKG_VHSCHDULEMAINTENANCE.USP_SAVE";
            cmd.CommandType = CommandType.StoredProcedure;

            Parameter = cmd.CreateParameter();
            Parameter.ParameterName = "p_HostBranchId";
            Parameter.OracleType = OracleType.Number;
            Parameter.Value = objmaster.intBranchId;
            cmd.Parameters.Add(Parameter); 

            Parameter = cmd.CreateParameter();
            Parameter.ParameterName = "p_UserId";
            Parameter.OracleType = OracleType.Number;
            Parameter.Value = objmaster.intUserId;
            cmd.Parameters.Add(Parameter); 


            Parameter = cmd.CreateParameter();
            Parameter.ParameterName = "p_OutMsg";
            Parameter.OracleType = OracleType.VarChar;
            Parameter.Size = 100;
            Parameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(Parameter);


            cmd.ExecuteNonQuery();

            string strResult = "";
            strResult = cmd.Parameters["p_OutMsg"].Value.ToString();

            return strResult;
        }
        catch (Exception exc)
        {

        }
        finally
        {
            this.AppConnection.CommitTransaction();
        }

Issue occurs when application is hosted in server. but it works fine while i running in source. Environment details: oracele 12c , framework 3.5, iis 10.

You've tried debugging it, I assume? One red flag I see here is an empty catch block; a recipe for un-clear results. — rory.ap, Apr 11 '18 at 13:25
Why are you committing a transaction in a finally block? You don't know if an error occurred there — Camilo Terevinto, Apr 11 '18 at 13:26
@rory.ap Thanks for the reply. Yes i tried with de bugged with application source but i didnt get any issues while i m using hosted application, i getting that error. I used catch block for catching error log. Temporarily i removed that. Catch(Exception ex) — Prakash N, Apr 11 '18 at 14:44
@camilo thanks for the reply. Actually thats the common method use in all pages. In that method, i m closing the connection. — Prakash N, Apr 11 '18 at 14:46

score 0 · Answer 1 · answered Apr 11 '18 at 13:34

I had a similar problem once when running a 64 bit IIS application against a 32 bit version of Odac. Make sure these match up.

And I'm with Camilio w regards to that transaction in the finally block. And make sure you close your connection or you are bound to get a ORA-1000 exception.

score 0 · Answer 2 · answered Apr 11 '18 at 17:00

why don't you try something closer to this ...

public string SaveLoad(DataGL.ScheduledMaintenance objmaster)
{
    try
    {
    OracleConnection connection = this.AppConnection;
    connection.Open();
    OracleTransaction transaction = connection.BeginTransaction();
    OracleCommand cmd = new OracleCommand("PKG_VHSCHDULEMAINTENANCE.USP_SAVE", connection, transaction);
    cmd.CommandType = CommandType.StoredProcedure;

    OracleParameter parameter = new OracleParameter("p_HostBranchId",OracleType.Number);
    parameter.Value = objmaster.intBranchId;
    cmd.Parameters.Add(parameter); 

    OracleParameter parameter = new OracleParameter("p_UserId",OracleType.Number);
    parameter.Direction = ParameterDirection.Output;
    parameter.Value = objmaster.intUserId;
    cmd.Parameters.Add(parameter); 

    OracleParameter parameter = new OracleParameter("p_OutMsg",OracleType.VarChar,100);
    parameter.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(parameter);

    cmd.ExecuteNonQuery();

    string strResult = "";
    strResult = cmd.Parameters["p_OutMsg"].Value.ToString();

    connection.CommitTransaction();
        return strResult;
    }
    catch (Exception exc)
    {

    }
    finally
    {
        connection.close();
    }

Thanks for the reply. But its not working. problem still exist. — Prakash N, Apr 12 '18 at 05:35
If you are using Oracle client 12c then it is likely you should try using a version 11 client instead. there is a known bug mentioned in this thread. (https://stackoverflow.com/questions/42856909/stored-procedure-output-varchar2-value-truncated-using-12c-client?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) — webmite, Apr 13 '18 at 11:11

score 0 · Answer 3 · answered Apr 13 '18 at 11:36

If you are using a Oracle 12c client try downgrading the client to version 11g if you have it on hand. No change on the server required. There is a known bug in Oracle 12c client distro mentioned in this thread here

Or you can try ugrading to a newer version of Oracle 12c client as was the final answer in the thread listed above. The oracle document that details the new ODBC drivers and at the end lists the known failures of the 12c client has a link listed in the answer but I am including it here

Oracle 12c, Stored Procedure OUT Parameter string message Breaks

3 Answers3