CODEWITHSUNDEEP
oracle-databasec#-4.0
sam
sam

Reputation: 2596

how to execute multiple oracle query c#

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

Upvotes: 1

Views: 2059

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

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

Upvotes: 1

Related Questions