c#中使用call调用oracle存储过程并获取out参数值

1.创建测试存储过程：

SQL> create or replace procedureproc_test(p1 INvarchar2,p2 OUT varchar2) is

 2  begin

 3    SELECT p1 INTO p2 FROM dual;

 4  end proc_test;

 5  /

 

过程被创建

 

2.主要c# 代码以及注意点：

using ORAC = System.Data.OracleClient;

private void button1_Click(object sender, System.EventArgs e)

         {

              try

              {

                   stringstr_Sql = @"call proc_test(:p1,:p2)";     

 /*不能是：call proc_test(?,?)或者call proc_test(@p1,@p2)，这样报错：ORA-01036:非法的变量名/编号*/

                   ORAC.OracleCommand cmd = new ORAC.OracleCommand(str_Sql,this.oracleConnection1);

                   /*cmd.CommandType= CommandType.StoredProcedure;   --注意这种方式调用存储过程，不能指定CommandType为StoredProcedure */

 

                   ORAC.OracleParameter pram1 = newORAC.OracleParameter("p1",ORAC.OracleType.VarChar,10);

                   pram1.Value ="test";

 

                   cmd.Parameters.Add(pram1);

 

                   ORAC.OracleParameter pram2 = newORAC.OracleParameter("p2",ORAC.OracleType.VarChar,10);

                   pram2.Direction=ParameterDirection.Output;

 

                   cmd.Parameters.Add(pram2);

 

                   if(this.oracleConnection1.State ==System.Data.ConnectionState.Closed)

                   {

                       this.oracleConnection1.Open();

                   }

 

                   cmd.ExecuteNonQuery();

 

                   this.textBox1.Text= cmd.Parameters[1].Value.ToString();

              }

              catch(Exceptionex)

              {

                   MessageBox.Show(ex.Message);

              }

              finally

              {

                   this.oracleConnection1.Close();

              }

             

         }