how to execute multiple oracle query c#

Question

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);
                }
            }

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`? — Alex Poole, Dec 23 '16 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 — sam, Dec 23 '16 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... — Alex Poole, Dec 23 '16 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 — sam, Dec 23 '16 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? — Alex Poole, Dec 23 '16 at 21:28

score 1 · Accepted Answer · answered Dec 23 '16 at 21:26

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);

answered Dec 23 '16 at 21:26

Alex Poole

183,384
11
179
318

what do you mean by change the driver's behaviour ? – sam Dec 23 '16 at 22:06
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 ?? – sam Dec 23 '16 at 23:28
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... – Alex Poole Dec 24 '16 at 00:06
I *think* you need to get your reader [as shown here](http://stackoverflow.com/a/16986144/266304). 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! – Alex Poole Dec 24 '16 at 00:11
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 – sam Dec 24 '16 at 00:22
Yes - but it isn't a query, so there are no returned columns - there is no result set from the execution itself. You have two result sets as ref cursor parameters, but you have to access them differently. – Alex Poole Dec 24 '16 at 00:24
but when I use OracleDataReader oraReder; oraReder = cmd.ExecuteNonQuery(); i got error **cannot implicitly convert int to oracle.data.access.....** I cant process retrieve record when using 'executeNonQuery' – sam Dec 24 '16 at 00:24
Look at the other answer I linked to. The reader is set from the parameter, **not** from the result of `executeNonQuery`, which is just an int flag. You need two readers, one for each ref cursor, and both are retrieved after the execute. – Alex Poole Dec 24 '16 at 00:27
as I read the executeNonQuery used with dml command and executeQuery with select query here is a link for that http://stackoverflow.com/questions/1062569/batch-multiple-select-statements-when-calling-oracle-from-ado-net ,,,, and also that http://stackoverflow.com/questions/31917301/execute-multiple-queries-in-single-oracle-command-in-c-sharp ... that way i did it by that way >>>> i dont know my friend I need to know the best and correct way to do if do not mind to help me of course – sam Dec 24 '16 at 00:30

how to execute multiple oracle query c#

1 Answers1

Linked