Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
192 views
in Technique[技术] by (71.8m points)

c# - Can odp.net pass a parameter to a boolean pl/sql parameter?

Is it possible to correctly pass an OracleParameter to a boolean parameter in a pl/sql stored procedure?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I used the following workaround to bypass this limitation:

  1. Wrap the function call using an anonymous block.
  2. Return an output variable containing 1 or 0.
  3. Read the output variable and cast it to boolean.

Here is some sample code:

using (var connection = new OracleConnection("<connection string>"))
{
    var command = new OracleCommand();
    command.Connection = connection;
    command.CommandText = 
        "declare v_bool boolean;" + 
        "begin " +
        "v_bool := auth_com.is_valid_username (:username); "+
        "if (v_bool = TRUE) then select 1 into :v_result from dual; end if; " +
        "if (v_bool = FALSE) then select 0 into :v_result from dual; end if; " +
        "end;";

    command.Parameters.Add(new OracleParameter { ParameterName = "username", OracleDbType = OracleDbType.NVarchar2, Size=512, Direction = ParameterDirection.Input });
    command.Parameters.Add(new OracleParameter { ParameterName = "v_result", OracleDbType = OracleDbType.Decimal, Direction = ParameterDirection.Output });     

    try
    {
        connection.Open();
        command.ExecuteNonQuery();
    }
    finally
    {
        connection.Close();
    }

    bool success = Convert.ToBoolean(((OracleDecimal)command.Parameters["v_result"].Value).ToInt32());
}

EDIT:

Alex Keh from Oracle, october 2013:

We're planning on supporting ODP.NET Boolean in the managed provider in the near term, possibly in the middle of next year.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...