使用ODP.NET

先下载Download: 32-bit ODP.NET   ODTwithODAC112030.zip

ODAC 11.2.0.3.0 with Oracle Developer Tools for Visual Studio
安装本机为了提取7个DLL

新建项目

连接字符串代码：

        private void Button_Open_Click(object sender, EventArgs e)
        {

            string tns = "(DESCRIPTION =";
    tns+="(ADDRESS_LIST =";
      tns+="(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.98.87)(PORT = 1521)))";
    
    tns+="(CONNECT_DATA =(SERVICE_NAME = TestDev) )  )";
      
   
            string CON = "Data Source="+tns+";User Id=APPS;Password=123456;"; 
 ;

         try
         {
             using (OracleConnection conn = new OracleConnection(CON))
             { 

                 conn.Open();
                 MessageBox.Show("Open");

             }
         }
         catch(Exception ex)
         {
             MessageBox.Show(ex.Message);
         }

        }

在其他机器上打包安装测试成功，做了个实验最大的那个DLL不能删除

Oracle存储过程传统数组

 
 /**
             create or replace package Pkg_Test is
  type  myList is table of varchar2(32767) INDEX BY BINARY_INTEGER;
   
  TYPE DAY_ARRAY_TYPE IS VARRAY(3) OF VARCHAR2(2);

  procedure list(ilist in number_list);
end;
            create or replace package body Pkg_Test is

  procedure list(ilist in myList) as
  
  begin
  
    for i in 1 .. ilist.count loop
    
      insert into apps.test (id) values (ilist(i));
    end loop;
    commit;
  exception
    when others then
      rollback;
  end;
end;
             * 
             */

            string tns = "(DESCRIPTION =";
    tns+="(ADDRESS_LIST =";
      tns+="(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.98.87)(PORT = 1521)))";
    
    tns+="(CONNECT_DATA =(SERVICE_NAME = TestDev) )  )";
      
   
            string CON = "Data Source="+tns+";User Id=APPS;Password=123456;"; 
 ;

         try
         {
             using (OracleConnection conn = new OracleConnection(CON))
             {

                 if (conn.State == ConnectionState.Closed)
                     conn.Open();
                 using (OracleCommand cmd = new OracleCommand("apps.Pkg_Test.list", conn))
                 {
                     cmd.CommandType = CommandType.StoredProcedure;
                     OracleParameter[] pars=new OracleParameter[1];
                     pars[0] = new OracleParameter("ilist", OracleDbType.Varchar2);
                     pars[0].CollectionType = OracleCollectionType.PLSQLAssociativeArray; 
                     //int[] iArray = new int[pars[0].Size];
                     //for (int i = 0; i < iArray.Length; i++)
                     //{
                     //    iArray[i] = 25;
                     //}
                     //pars[0].ArrayBindSize = iArray;
                     //pars[0].Value = new string[] { "100", "200", "300" };

                     List<string> list_obj = new List<string>();

                     for (int i = 1; i <=10; i++) //32767
                         list_obj.Add(i.ToString());
                     pars[0].Value = list_obj.ToArray();
                     cmd.Parameters.AddRange(pars);

                     cmd.ExecuteNonQuery();

                 }

             }
         }
         catch(Exception ex)
         {
             MessageBox.Show(ex.Message);
         }