To pass a set of values, you need to use Oracle’s table or array types. At first, you create a table type (e.g. for NUMBER): CREATE TYPE number_table AS TABLE OF NUMBER; When you create the parameter for the query, declare it as an associative PL/SQL array: OracleParameter param1 = new OracleParameter(); param1.OracleDbType = OracleDbType.Int32; … Read more
This is not a bug but explicitly mentioned in Oracle ODP.Net documentation. In a OracleCommand class the parameters are bound by position as default. If you want to bind by name then set the property cmd.BindByName = true; explicitly. Reference to Oracle documentation. http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666
From Oracle Database 12c Release 2 you could use TO_NUMBER with DEFAULT … ON CONVERSION ERROR: SELECT TO_NUMBER(‘*’ DEFAULT 0 ON CONVERSION ERROR) AS “Value” FROM DUAL; Or CAST: SELECT CAST(‘*’ AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS “Value” FROM DUAL; db<>fiddle demo