3 Replies Latest reply: Jul 7, 2011 1:03 AM by 873859 RSS

    Array insert with stored procedure

    588770
      Hello,

      is it possible to use array bind insert in odp.net that calls a stored procedure, or does that lose the point of array binding?

      I need to do two inserts into a parent table and child table at once (getting the primary key generated by a sequence in the first table.). Don't know if this can be done without storing seq.next_val somehow?

      edit: i'll extend the question and wonder if the array bind sql text can be an anonymous plsql block

      Edited by: KarlTrumstedt on 2010-jun-16 02:49
        • 1. Re: Array insert with stored procedure
          319958
          You can do both. You can array insert a stored procedure and an anonymous block.

          Here's how (these are based on the ArrayBind sample that comes with the ODP.net/ODT installation.

          Setup:
          create table zdept (deptno number, deptname varchar2(50), loc varchar2(50));
          
          CREATE OR REPLACE PROCEDURE ZZZ (p_deptno in number, p_deptname in varchar2, p_loc in varchar2) AS
          begin
              insert into zdept values(p_deptno , p_deptname || ' ' || p_deptname, p_loc );
          end zzz;
          
          
           
           /**
           drop table zdept ;
           drop procedure ZZZ ;
           **/
          array bind to stored procedure call:
             static void Main(string[] args)
              {
                // Connect
                string connectStr = "User Id=;Password=;Data Source=";
          
                // Setup the Tables for sample
                Setup(connectStr);
          
                // Initialize array of data
                int[]    myArrayDeptNo   = new int[3]{1, 2, 3};
                String[] myArrayDeptName = {"Dev", "QA", "Facility"};
                String[] myArrayDeptLoc  = {"New York", "Maryland", "Texas"};
                
                OracleConnection connection = new OracleConnection(connectStr);
                OracleCommand    command    = new OracleCommand (
                  "zzz", connection);
                command.CommandType = CommandType.StoredProcedure;
                    
                // Set the Array Size to 3. This applied to all the parameter in 
                // associated with this command
                command.ArrayBindCount = 3;
                command.BindByName = true;
                // deptno parameter
                OracleParameter deptNoParam = new OracleParameter("p_deptno",OracleDbType.Int32);
                deptNoParam.Direction       = ParameterDirection.Input;
                deptNoParam.Value           = myArrayDeptNo;
                command.Parameters.Add(deptNoParam);
          
                // deptname parameter
                OracleParameter deptNameParam = new OracleParameter("p_deptname", OracleDbType.Varchar2);
                deptNameParam.Direction       = ParameterDirection.Input;
                deptNameParam.Value           = myArrayDeptName;
                command.Parameters.Add(deptNameParam);
          
                // loc parameter
                OracleParameter deptLocParam = new OracleParameter("p_loc", OracleDbType.Varchar2);
                deptLocParam.Direction       = ParameterDirection.Input;
                deptLocParam.Value           = myArrayDeptLoc;
                command.Parameters.Add(deptLocParam);
          
                try 
                {
                  connection.Open();
                  command.ExecuteNonQuery ();
                  Console.WriteLine("{0} Rows Inserted", command.ArrayBindCount);
                }
                catch (Exception e)
                {
                  Console.WriteLine("Execution Failed:" + e.Message);
                }
                finally
                {
                  // connection, command used server side resource, dispose them
                  // asap to conserve resource
                  connection.Close();
                  command.Dispose();
                  connection.Dispose();
                }
              }
          "anonymous plsql block"
          yup
              static void Main(string[] args)
              {
                // Connect
                string connectStr = "User Id=;Password=;Data Source=";
          
                // Setup the Tables for sample
                Setup(connectStr);
          
                // Initialize array of data
                int[]    myArrayDeptNo   = new int[3]{1, 2, 3};
                String[] myArrayDeptName = {"Dev", "QA", "Facility"};
                String[] myArrayDeptLoc  = {"New York", "Maryland", "Texas"};
                
                OracleConnection connection = new OracleConnection(connectStr);
                OracleCommand    command    = new OracleCommand (
                  "declare dnumber number; dname varchar2(50) ; begin dnumber := :deptno;dname := :deptname;insert into zdept values (:deptno, :deptname, :loc); update zdept set deptname=dname || :loc where deptno = :deptno; end;", connection);
                    
                // Set the Array Size to 3. This applied to all the parameter in 
                // associated with this command
                command.ArrayBindCount = 3;
                command.BindByName = true;
                // deptno parameter
                OracleParameter deptNoParam = new OracleParameter("deptno",OracleDbType.Int32);
                deptNoParam.Direction       = ParameterDirection.Input;
                deptNoParam.Value           = myArrayDeptNo;
                command.Parameters.Add(deptNoParam);
          
                // deptname parameter
                OracleParameter deptNameParam = new OracleParameter("deptname", OracleDbType.Varchar2);
                deptNameParam.Direction       = ParameterDirection.Input;
                deptNameParam.Value           = myArrayDeptName;
                command.Parameters.Add(deptNameParam);
          
                // loc parameter
                OracleParameter deptLocParam = new OracleParameter("loc", OracleDbType.Varchar2);
                deptLocParam.Direction       = ParameterDirection.Input;
                deptLocParam.Value           = myArrayDeptLoc;
                command.Parameters.Add(deptLocParam);
          
                try 
                {
                  connection.Open();
                  command.ExecuteNonQuery();
                  Console.WriteLine("{0} Rows Inserted", command.ArrayBindCount);
                }
                catch (Exception e)
                {
                  Console.WriteLine("Execution Failed:" + e.Message);
                }
                finally
                {
                  // connection, command used server side resource, dispose them
                  // asap to conserve resource
                  connection.Close();
                  command.Dispose();
                  connection.Dispose();
                }
              }
          • 2. Re: Array insert with stored procedure
            588770
            Yes, I figured as much. The real question is how much it will affect performance though. Since the array insert usually makes it possible to execute SQL directly in the SQL engine without even going through the PL/SQL engine.
            • 3. Re: Array insert with stored procedure
              873859
              Hello,

              I think I have same problem. I want to insert data using stored procedure. I have 2 dimensional array in java class how can i pass this 2d array to stored procedure? And how do i design stored procedure that inserts data into table.
              Please help me.