1

i am trying to execute multiple oracle select query as explain at that post answer here but I am getting exception as show at image

the same way explained at oracle site here

btw is ther anyway to handle the no rows 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);
                }
            }

enter image description here

5
  • What happens if you change open :1 to open :p_cr1? From the example you linked to the parameters look positional, not named; in which case, do you need to add p_cr1 before P_para? Commented Dec 23, 2016 at 18:58
  • @AlexPoole I tried to replace the :1 and :@ with name of parameters p_cr1 and p_cr2 i got same error ... and if i understand you well the P_para needed at first select statement Commented Dec 23, 2016 at 20:42
  • Yes, it's needed, but it's the second bind variable; I'm just suggesting swapping the first two Add calls - so those binds are in the same order they appear in the query. It may not help, but sometimes they are positional... Commented Dec 23, 2016 at 20:47
  • @AlexPoole OMG problem solved !!!! I was not except that the order will make all that !!! ... problem suggest its positional so the order is p_cr1 then :P_para and lastly :p_cr2 Thank you a lot man please post your comment as answer and ill market too... one last quest is t her anyway to handle the no rows found from one of these queries ? I needed to prompt the user a message that their is no entry about his query Commented Dec 23, 2016 at 20:53
  • You can keep a count as you iterate over the result set, inside the while loop. if the counter is still zero after the while loop then there was no data found? Commented Dec 23, 2016 at 21:28

1 Answer 1

1

Although you're using names for your parameters, your driver is treating them positionally. You can kind of tell because it's (almost) matching :1 with the name p_cr1 - '1' isn't a valid name. It doesn't complain since it matches positionally - but that means it's trying to use the P_para for :1, and as the type of that is wrong, that explains the error you see.

There may well be a way to change the driver's behaviour, but for now you can just swap the order you bind them - so the binds occur in the same order (position) the variables appear in the query. 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);
Sign up to request clarification or add additional context in comments.

9 Comments

what do you mean by change the driver's behaviour ?
another question what would be the index of the first column of second query ? textbox1.Text = oraReder.GetString(index).ToString(); and to be more specific about how to handle the no rows found the first query return rows but the second did not return rows so how to detect the no rows found for each query ??
Some drivers (that manage the Oracle connection) let you choose between positional and named parameters. Check the documentation for the driver and version you're using. The index might also be driver specific, but I'd expect the first column to be 1 I think. I don't think you are doing it quite right though. You've executed a PL/SQL block, not run a query. As in the example you linked to, you need to executeNonQuery, then iterate over the bound refCursor. I'm not sure of the details in this language...
I think you need to get your reader as shown here. So executeNonQuery(), then get the value from the first ref cursor parameter, and get a reader from that? If that doesn't help (or make sense) you might need to ask a new question - though there may be other existing answers that can explain it properly. Good luck!
I tried to start with 0 for index and tried to start with 1 but same error message invalid column index specified ummm at oracle site link the are also execute plsql block here is it cmd.CommandText = "begin open :1 for select * from employees where manager_id=101; end;"; and you are right about the executeNonQuery
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.