How to execute multiple oracle C # queries

Question

I am trying to fulfill a few oracle selection requests, how to explain in response to this post here , but I get an exception as shown in the image

the same thing is explained on oracle site here

Does btw still process strings found from one of these queries?

string cmdstr = @"begin open :1 for SELECT a.column1, a.olumn2 b.column3 FROM table1 A,table2 B WHERE A.column1=B.column1 AND A.column2 = NVL(:P_para, 0) AND B.column3='1'; open :2 for select column1, column2, column3, From dual; end;"; using (OracleConnection conn = new OracleConnection(connstr)) using (OracleCommand cmd = new OracleCommand(cmdstr, conn)) { try { cmd.Parameters.Add(new OracleParameter(":P_para", OracleDbType.Int64)).Value = Convert.ToInt64(Textbox.Text); cmd.Parameters.Add("p_cr1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output); cmd.Parameters.Add("p_cr2", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output); cmd.CommandText = cmdstr; conn.Open(); OracleTransaction trans = conn.BeginTransaction(); OracleDataReader oraReder; oraReder = cmd.ExecuteReader(); while (oraReder.Read()) { textbox1.Text = oraReder.GetString(0).ToString(); textbox2.Text = oraReder.GetValue(1).ToString(); textbox3.Text = oraReder.GetValue(2).ToString(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Erorr Message", MessageBoxButtons.OK, MessageBoxIcon.Error); } } 

Answer 1

Although you use names for your parameters, your driver handles them differently. You can tell because it is (almost) a match :1 with the name p_cr1 - '1' is not a valid name. He does not complain, as he coincides with the position, but this means that he is trying to use P_para for :1 , and as the wrong type, this explains the error you see.

There may be a way to change the behavior of the driver, but now you can just change the order that you bind to it - therefore, the bindings occur in the same order (position) that the variables appear in the request. So:

 cmd.Parameters.Add("p_cr1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output); cmd.Parameters.Add(new OracleParameter(":P_para", OracleDbType.Int64)).Value = Convert.ToInt64(Textbox.Text); cmd.Parameters.Add("p_cr2", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);