7 Replies Latest reply: May 5, 2010 2:54 PM by 725913 RSS

    Passing %ROWTYPE array to a stored procedure using JDBC

    714045

      Hello,

      i have the following code:

      create or replace package PCK_COLLECT_MESSAGES_CSR
      as
      TYPE TY_TA_KPMESSAGE_ROWTYPE IS TABLE OF M_MESSAGECOLLECTION%ROWTYPE;
      procedure insert_messages(messages IN TY_TA_KPMESSAGE_ROWTYPE);
      end PCK_COLLECT_MESSAGES_CSR;

      I would like to pass an array of M_MESSAGECOLLECTION%ROWTYPE to the stored procedure using JDBC, because i want to use the FORALL statement to do a fast insert inside the procedure.

      Is this possible using JDBC? Oracle version is 10g.

      Thank you in advance,
      regards,

      Markus.

        • 1. Re: Passing %ROWTYPE array to a stored procedure using JDBC
          428263
          You generally want to use JPublisher to convert the %ROWTYPE into a SQL Object type. Then you can pass a Collection (varray or nested table) of that Object type to a stored procedure using JDBC. Hope that helps.
          • 2. Re: Passing %ROWTYPE array to a stored procedure using JDBC
            friscoki
            %rowtype declarations are native to pl/sql and are not accessible from outside its confines, something to think about when designing apis.

            As a workaround you can create an anonymous pl/sql block that can access %rowtype definitions.
              String sql = "declare r dual%rowtype; begin r.dummy := :x; end;";
                
              CallableStatement cs = conn.prepareCall(sql);
                
              cs.setString(1, "x");
            
              cs.execute();
            • 3. Re: Passing %ROWTYPE array to a stored procedure using JDBC
              714045
              Ok, thank you for your answers.
              • 4. Re: Passing %ROWTYPE array to a stored procedure using JDBC
                422629
                user8668629 wrote:
                Hello,

                i have the following code:

                create or replace package PCK_COLLECT_MESSAGES_CSR
                as
                TYPE TY_TA_KPMESSAGE_ROWTYPE IS TABLE OF M_MESSAGECOLLECTION%ROWTYPE;
                procedure insert_messages(messages IN TY_TA_KPMESSAGE_ROWTYPE);
                end PCK_COLLECT_MESSAGES_CSR;

                I would like to pass an array of M_MESSAGECOLLECTION%ROWTYPE to the stored procedure using JDBC, because i want to use the FORALL statement to do a fast insert inside the procedure.

                Is this possible using JDBC? Oracle version is 10g.
                For demonstration purposes lets use this table:
                  CREATE TABLE "ARRAY_COMMANDS" 
                   (     "COMMAND_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
                     "OS_NAME" VARCHAR2(512 BYTE) NOT NULL ENABLE, 
                     "JAVA_CLASS_FILE_NAME" VARCHAR2(512 BYTE), 
                     "BUILTIN_Y_OR_N" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
                     "REQUIRED_NUMBER" NUMBER, 
                     "OBLIGATARY_DATE" DATE, 
                     "EXE_FILE_NAME" VARCHAR2(512 BYTE), 
                     "COMMAND_DESCRIPTION" VARCHAR2(512 BYTE)
                   );
                We want to pass an array of ARRAY_COMMANDS%ROWTYPE to this procedure:
                create or replace package oracle_arrays as
                --
                TYPE TBL_ARRAY_COMMANDS_ROWTYPE_OA  IS TABLE OF array_COMMANDS%ROWTYPE;
                --
                PROCEDURE arraytest3a(p_param1 in out TBL_ARRAY_COMMANDS_ROWTYPE_OA
                                     ,p_param2 in out TBL_ARRAY_COMMANDS_ROWTYPE_OA) 
                --
                END;
                This is possible but isn't straightforward. As the poster above says if you are passing a %ROWTYPE parameter you can break it up into its component parts and pass those instead, but this doesn't work for a TABLE of %ROWTYPE. In order to do this you need to create an Oracle TYPE object that matches the columns and then another object that is an array of these TYPES. So you'll need to do this once in your DB:
                  executeImmediate("CREATE OR REPLACE TYPE OSOFT7990ND99_T AS OBJECT" 
                                 + " (COL_0 VARCHAR2(128)"
                                 + " ,COL_1 VARCHAR2(512)"
                                 + " ,COL_2 VARCHAR2(512)"
                                 + " ,COL_3 VARCHAR2(1)" 
                                 + " ,COL_4 NUMBER" 
                                 + " ,COL_5 DATE"
                                 + " ,COL_6 VARCHAR2(512)"
                                 + " ,COL_7 VARCHAR2(512))",SqlUtils.TYPE_HAS_DEPENDENTS);
                   
                  executeImmediate("CREATE OR REPLACE TYPE OSOFT7990ND99_A  AS TABLE OF OSOFT7990ND99_T;",SqlUtils.TYPE_HAS_DEPENDENTS); 
                   
                Note that the name of the object or its columns isn't relevent - as long as there are the right number of cols and they are of the right size or greater all will be well..
                At the JDBC level you'll need to create an Object that can be mapped to our Oracle type. This is what you will pass in to your PL/SQL block. How to create this is well documented in the JDBC manual.

                Once you've done this you need to write a PL/SQL block that takes your Oracle TYPE array as a parameter and maps it into an array of %ROWTYPE, then does the call, and then does any required mapping back:
                  public String getProcCallStatement()                                             
                    {                                                              
                    if (procCall == null)
                      {                                                              
                      procCall = new StringBuffer("DECLARE \n"); // 1
                      procCall.append("/* Created  By OrindaBuild 6.0.2621 */ \n"); // 2
                      procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3
                      procCall.append("p_param1_A OSOFT7990ND99_A := ?; \n"); // 4
                      procCall.append("p_param1 ORACLE_ARRAYS.TBL_ARRAY_COMMANDS_ROWTYPE_OA := ORACLE_ARRAYS.TBL_ARRAY_COMMANDS_ROWTYPE_OA(); \n"); // 5
                      procCall.append("p_param2_A OSOFT7990ND99_A := ?; \n"); // 6
                      procCall.append("p_param2 ORACLE_ARRAYS.TBL_ARRAY_COMMANDS_ROWTYPE_OA := ORACLE_ARRAYS.TBL_ARRAY_COMMANDS_ROWTYPE_OA(); \n"); // 7
                      procCall.append("BEGIN  \n"); // 8
                      procCall.append("IF p_param1_A.COUNT > 0 THEN \n"); // 9
                      procCall.append("  p_param1.EXTEND(p_param1_A.COUNT); \n"); // 10
                      procCall.append("  FOR i IN p_param1_A.FIRST..p_param1_A.LAST LOOP \n"); // 11
                      procCall.append("      p_param1(i).COMMAND_NAME         := p_param1_A(i).COL_0; \n"); // 12
                      procCall.append("      p_param1(i).OS_NAME              := p_param1_A(i).COL_1; \n"); // 13
                      procCall.append("      p_param1(i).JAVA_CLASS_FILE_NAME := p_param1_A(i).COL_2; \n"); // 14
                      procCall.append("      p_param1(i).BUILTIN_Y_OR_N       := p_param1_A(i).COL_3; \n"); // 15
                      procCall.append("      p_param1(i).REQUIRED_NUMBER      := p_param1_A(i).COL_4; \n"); // 16
                      procCall.append("      p_param1(i).OBLIGATARY_DATE      := p_param1_A(i).COL_5; \n"); // 17
                      procCall.append("      p_param1(i).EXE_FILE_NAME        := p_param1_A(i).COL_6; \n"); // 18
                      procCall.append("      p_param1(i).COMMAND_DESCRIPTION  := p_param1_A(i).COL_7; \n"); // 19
                      procCall.append("  END LOOP; \n"); // 20
                      procCall.append("END IF; \n"); // 21
                      procCall.append("IF p_param2_A.COUNT > 0 THEN \n"); // 22
                      procCall.append("  p_param2.EXTEND(p_param2_A.COUNT); \n"); // 23
                      procCall.append("  FOR i IN p_param2_A.FIRST..p_param2_A.LAST LOOP \n"); // 24
                      procCall.append("      p_param2(i).COMMAND_NAME         := p_param2_A(i).COL_0; \n"); // 25
                      procCall.append("      p_param2(i).OS_NAME              := p_param2_A(i).COL_1; \n"); // 26
                      procCall.append("      p_param2(i).JAVA_CLASS_FILE_NAME := p_param2_A(i).COL_2; \n"); // 27
                      procCall.append("      p_param2(i).BUILTIN_Y_OR_N       := p_param2_A(i).COL_3; \n"); // 28
                      procCall.append("      p_param2(i).REQUIRED_NUMBER      := p_param2_A(i).COL_4; \n"); // 29
                      procCall.append("      p_param2(i).OBLIGATARY_DATE      := p_param2_A(i).COL_5; \n"); // 30
                      procCall.append("      p_param2(i).EXE_FILE_NAME        := p_param2_A(i).COL_6; \n"); // 31
                      procCall.append("      p_param2(i).COMMAND_DESCRIPTION  := p_param2_A(i).COL_7; \n"); // 32
                      procCall.append("  END LOOP; \n"); // 33
                      procCall.append("END IF; \n"); // 34
                      procCall.append(" \n"); // 35
                      procCall.append("ORACLE_ARRAYS.ARRAYTEST3A(p_param1,p_param2); \n"); // 36
                      procCall.append("  \n"); // 37
                      procCall.append("p_param1_A.DELETE; \n"); // 38
                      procCall.append("IF p_param1.COUNT > 0 THEN \n"); // 39
                      procCall.append("  p_param1_A.EXTEND(p_param1.COUNT); \n"); // 40
                      procCall.append("  FOR i IN p_param1.FIRST..p_param1.LAST LOOP \n"); // 41
                      procCall.append("  p_param1_A(i) := OSOFT7990ND99_T \n"); // 42
                      procCall.append("   (p_param1(i).COMMAND_NAME \n"); // 43
                      procCall.append("   ,p_param1(i).OS_NAME \n"); // 44
                      procCall.append("   ,p_param1(i).JAVA_CLASS_FILE_NAME \n"); // 45
                      procCall.append("   ,p_param1(i).BUILTIN_Y_OR_N \n"); // 46
                      procCall.append("   ,p_param1(i).REQUIRED_NUMBER \n"); // 47
                      procCall.append("   ,p_param1(i).OBLIGATARY_DATE \n"); // 48
                      procCall.append("   ,p_param1(i).EXE_FILE_NAME \n"); // 49
                      procCall.append("   ,p_param1(i).COMMAND_DESCRIPTION); \n"); // 50
                      procCall.append("  END LOOP; \n"); // 51
                      procCall.append("END IF; \n"); // 52
                      procCall.append("  \n"); // 53
                      procCall.append("p_param2_A.DELETE; \n"); // 54
                      procCall.append("IF p_param2.COUNT > 0 THEN \n"); // 55
                      procCall.append("  p_param2_A.EXTEND(p_param2.COUNT); \n"); // 56
                      procCall.append("  FOR i IN p_param2.FIRST..p_param2.LAST LOOP \n"); // 57
                      procCall.append("  p_param2_A(i) := OSOFT7990ND99_T \n"); // 58
                      procCall.append("   (p_param2(i).COMMAND_NAME \n"); // 59
                      procCall.append("   ,p_param2(i).OS_NAME \n"); // 60
                      procCall.append("   ,p_param2(i).JAVA_CLASS_FILE_NAME \n"); // 61
                      procCall.append("   ,p_param2(i).BUILTIN_Y_OR_N \n"); // 62
                      procCall.append("   ,p_param2(i).REQUIRED_NUMBER \n"); // 63
                      procCall.append("   ,p_param2(i).OBLIGATARY_DATE \n"); // 64
                      procCall.append("   ,p_param2(i).EXE_FILE_NAME \n"); // 65
                      procCall.append("   ,p_param2(i).COMMAND_DESCRIPTION); \n"); // 66
                      procCall.append("  END LOOP; \n"); // 67
                      procCall.append("END IF; \n"); // 68
                      procCall.append("? := p_param1_A; \n"); // 69
                      procCall.append("? := p_param2_A; \n"); // 70
                      procCall.append("END; "); // 71 2545 characters
                      
                      }     
                    return(procCall.toString());
                    }     
                As you can tell this is a lot of work to do by hand. If my goal was to insert records into a table quickly I might try straightforward SQL first....

                All the Java to do this is available here:

                http://www.orindasoft.com/public/friends/oracle/rowtypedemo.zip


                David Rolfe
                Orinda Software
                Dublin, Ireland
                www.orindasoft.com

                Disclaimer: Orinda Software makes OrindaBuild, a product which automates the process of Java generation for complex PL/SQL
                • 5. Re: Passing %ROWTYPE array to a stored procedure using JDBC
                  725913
                  Working on the exact same thing here but I can't see how to use arrays of ROWTYPE. I'm using arrays of individual column values with the forall insert statement in a procedure. It's a bit more cumbersome to have all the column data in separate fields, but even at that, the performance was increased an order of magnitude above using statements and statement caching. I'll kill some brain cells trying to do it with objects types and compare the performance but will fallback to separate arrays of values.

                  You need a global type definition for the array, when declared in package does not work:
                  CREATE OR REPLACE TYPE HR.NUM_TBL_TY IS TABLE OF NUMBER(10);

                  Then, a procedure to bulk load the array(s):
                  PROCEDURE HR.INSERT_BULK_RAWDATA( A_ARR_NUM HR.NUM_TBL_TY )
                  AS
                  BEGIN
                  FORALL idx IN A_ARR_NUM.FIRST .. A_ARR_NUM.LAST
                  INSERT INTO HR.SOMETABLE( NUM )
                  VALUES ( A_ARR_NUM(idx));
                  END INSERT_BULK_RAWDATA;

                  Finally, the workhorse class:

                  import java.sql.Connection;
                  import java.sql.CallableStatement;
                  import java.sql.SQLException;
                  import java.util.ArrayList;

                  import oracle.sql.ARRAY;
                  import oracle.sql.ArrayDescriptor;

                  public class NumData {

                       private CallableStatement cs;
                       private Connection dbConn;

                       // ArrayLists needed to gather rows of field values
                       private ArrayList<Integer> nbrList = new ArrayList<Integer>();

                       // Oracle specific descriptors required for each array
                       private ArrayDescriptor oraNbrDscr;

                       // Needed to convert ArrayLists to Arrays to pass to Oracle
                       private final Integer[] emptyIntegerArray = new Integer[0];

                       // Upper limit on batch size
                       private static final int BATCHLIMIT = 1000;

                       // Counters
                       private int batchCount = 0;
                       private int nbr = 0;

                       // The SQL procedure call
                       private static final String sqlCall = "{ call HR.INSERT_BULK_RAWDATA( A_ARR_LINENUM => ? ) }";

                       /**
                       * This class designed for bulk loading Oracle data
                       *
                       * @param dbConn
                       * A useable connection (must persist throughout life of the file
                       * load)
                       * @throws SQLException
                       * On any database error
                       *
                       * Build a test class to:
                       * Create instance: numData = new NumData( <opened Oracle connection> );
                       * Call in a loop 10,000 or so times: numData.insertData();
                       * Close to send last batch: numData.close();
                       *
                       */
                       public NumData( Connection dbConn) throws SQLException {
                            this.dbConn = dbConn;

                            // Prepare the Oracle procedure call
                            cs = dbConn.prepareCall(sqlCall);

                            // Build the Oracle ArrayDescriptor objects for each field array
                            // per Oracle JDBC requirements
                            // (For some reason, package level types are not supported
                            // , need to use schema level types)
                            oraNbrDscr = ArrayDescriptor.createDescriptor(
                                      "HR.NUM_TBL_TY", dbConn);
                       }

                       /**
                       * Called from line reader to accumulate arrays of numbers to bulk
                       * insert
                       * If array size hits limit, send to database
                       *
                       * @param nbr
                       * The file line as read
                       * @throws SQLException
                       * On any database error
                       */
                       public void insertData() throws SQLException {

                            nbr++;

                            // Limit batch size by sending to Oracle and resetting counts and arrays
                            if (batchCount >= BATCHLIMIT)
                                 sendBatch();

                            batchCount++;

                            // Build array lists
                            nbrList.add(nbr);
                       }

                       /**
                       * Sends available data in array batches to Oracle
                       * 10 times more efficient than row by row processing!
                       * Reduces 3.25 minutes down to 10 seconds for 280,000 line file
                       * @throws SQLException
                       * On any database error
                       */
                       private void sendBatch() throws SQLException {
                            // Holders for raw array data
                            Integer[] lineArray;

                            // Extract raw array data fro ArrayLists
                            lineArray = nbrList.toArray(emptyIntegerArray);

                            // Wrap/convert Java arrays to Oracle specific SQL arrays
                            ARRAY oraNbrArray = new ARRAY(oraNbrDscr, dbConn, lineArray);
                            
                            // Set the procedure argumemts
                            cs.setString(1, oraNbrArray);
                            // Do the call
                            cs.executeUpdate();

                            // Reset counter and arrays
                            batchCount = 0;
                            nbrList.clear();
                            recTypeList.clear();
                            rawDataList.clear();
                       }

                       /**
                       * MUST be called by line reader class after last line is read to send any
                       * last partial batch
                       *
                       * @throws SQLException
                       * On any database error
                       */
                       public void close() throws SQLException {
                            if (batchCount > 0)
                                 sendBatch();
                            cs.close();
                       }

                       
                  }
                  • 6. Re: Passing %ROWTYPE array to a stored procedure using JDBC
                    user4900730
                    Hi!,
                    Did you ever do a test with the SQL object types?

                    I am not a java person but would prefer to pass the batches to a stored proc rather than let the java client do batch inserts. The problem is our tables ahve large numbers of columns upto a 100 attributes for some. If I use the array for each attribute thats 100 separate arrays. I guess my options are to create a SQL OBJECT and use a varray of that object type OR a nested table of that type. Am I right to say that if I use the VARRAY option I am hard coding the batch size to the VARRAY declaration size i.e. no flexibility on trying different batch sizes. I have also read somewhere that I could use an oracle temp table to pass the data to a stored proc.

                    Can anybody offer some advice
                    • 7. Re: Passing %ROWTYPE array to a stored procedure using JDBC
                      725913
                      The best I could do was a list of arrays of collection types. Latest was a table with a dozen fields, so had to build a dozen arrays and passed it to a package procedure call. It worked satisfactory, meaning it was still an order of magnitude or more quicker than individual statements or batches of individual statements.