All geek questions in one place

Can odp.net pass parameter to boolean pl / sql parameter?

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

+4
source share
2 answers

You cannot use boolean parameters in SQL. Therefore, calling a stored procedure that accepts or returns a boolean will not work in SQL. There is no problem using such a procedure from the pl / sql block.

ADDED from JCallico answer:

I used the following workaround to get around this limitation:

  • Wrap a function call with an anonymous block.
  • Returns an output variable containing 1 or 0.
  • Read the output variable and enter it in boolean.

Here is a 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 plan to support ODP.NET Boolean as a managed provider in the near future, possibly in the middle of next year.

+2
source

I used the following workaround to get around this limitation:

  • Wrap a function call with an anonymous block.
  • Returns an output variable containing 1 or 0.
  • Read the output variable and enter it in boolean.

Here is a 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 plan to support ODP.NET Boolean as a managed provider in the near future, possibly in the middle of next year.

+9
source

Source: https://habr.com/ru/post/1313616/

More articles:


All Articles