{"id":4746,"date":"2014-03-30T15:05:28","date_gmt":"2014-03-30T15:05:28","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/03\/30\/how-to-call-oracle-function-using-mono-collection-of-common-programming-errors\/"},"modified":"2014-03-30T15:05:28","modified_gmt":"2014-03-30T15:05:28","slug":"how-to-call-oracle-function-using-mono-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2014\/03\/30\/how-to-call-oracle-function-using-mono-collection-of-common-programming-errors\/","title":{"rendered":"How to call oracle function using Mono?-Collection of common programming errors"},"content":{"rendered":"<p>Working with Mono I needed to call a function in an oracle database. The function has two input parameters and one output parameter.<\/p>\n<p>At first I tested with .Net runtime. The following pretty simple code did the job:<\/p>\n<pre><code>var dbcon = new OracleConnection(\"my connection string\");\ndbcon.Open();\n\nOracleCommand cmd = dbcon.CreateCommand();\ncmd.CommandType = CommandType.StoredProcedure;\ncmd.CommandText = \"my_pkg.my_func\";\n\nOracleParameter p = new OracleParameter(\"ReturnValue\", OracleType.Number);\np.Direction = ParameterDirection.ReturnValue;\ncmd.Parameters.Add(p);\n\np = new OracleParameter(\"p_in1\", OracleType.VarChar);\np.Size = 5;\np.Direction = ParameterDirection.Input;\np.Value = p1;\ncmd.Parameters.Add(p);\n\np = new OracleParameter(\"p_in2\", OracleType.VarChar);\np.Size = 20;\np.Direction = ParameterDirection.Input;\np.Value = p2;\ncmd.Parameters.Add(p);\n\np = new OracleParameter(\"p_out\", OracleType.VarChar);\np.Direction = ParameterDirection.Output;\np.Size = 512;\ncmd.Parameters.Add(p);    \n\ncmd.ExecuteNonQuery();\n\nreturn cmd.Parameters[\"p_out\"].Value.ToString();\n<\/code><\/pre>\n<p>However, as the target OS is Linux, I changed the runtime (<em>Utilities=&gt;Options=&gt;.Net Runtimes<\/em>) to Mono (ver. 2.10.9). The first issue I experienced then was the bug mentioned in<\/p>\n<p>As far as I understand the only way out is to set <code>OracleParameter.Value<\/code> before <code>OracleParameter.OracleType<\/code>, even for output parameters&#8230; So the code was changed to<\/p>\n<pre><code>p = new OracleParameter();\np.ParameterName = \"p_in1\";\np.Size = 5;\np.Direction = ParameterDirection.Input;    \np.Value = code.ToString ();                \np.OracleType = OracleType.VarChar;\n<\/code><\/pre>\n<p>The <code>NullReferenceException<\/code> disappeared, but another arrised:<\/p>\n<blockquote>\n<p>PLS-00306: wrong number or types of arguments in call to &#8216;my_func&#8217;<\/p>\n<p>ORA-06550: line 1, column 7: PL\/SQL: Statement ignored<\/p>\n<\/blockquote>\n<p>As it is said in Mono &#8211; Oracle: Current Status for System.Data.OracleClient :<\/p>\n<blockquote>\n<pre><code>Input\/Output and Return parameters have not been tested.\n<\/code><\/pre>\n<\/blockquote>\n<p>is <code>ParameterDirection.ReturnValue<\/code> even supported?<\/p>\n<p>Or why does it work with the .Net runtime and does not with Mono?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Working with Mono I needed to call a function in an oracle database. The function has two input parameters and one output parameter. At first I tested with .Net runtime. The following pretty simple code did the job: var dbcon = new OracleConnection(&#8220;my connection string&#8221;); dbcon.Open(); OracleCommand cmd = dbcon.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-4746","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/4746","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=4746"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/4746\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=4746"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=4746"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=4746"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}