如何执行多个Oracle查询C＃ [英] how to execute multiple oracle query c#

问题描述

我正在尝试执行多个oracle select查询，如该帖子答案此处所述。我在图像上显示异常

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

与在Oracle网站此处

the same way explained at oracle site here

btw仍然可以处理未找到的行从这些查询之一？

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

推荐答案

尽管您使用名称作为参数，但驱动程序会在位置上对其进行处理。您可以说一下，因为它（几乎）匹配名称为 p_cr1 的：1 -'1'不是有效名称。它没有抱怨，因为它在位置上匹配-但这意味着它正在尝试将 P_para 用于：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);

这篇关于如何执行多个Oracle查询C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！