How to Pass an Array form Java and Iterate/Insert it in a Proc/Function that Takes Oracle Type as Input

At times, you need to send big list of data to backend. The backend procedure written by you or your database developed allows it to take an Oracle Type, which is a convenient approach for some scenarios.

So, this article is a basic example to perform the above using combination of Oracle PL/SQL and Java.

Using below steps we can pass an array form java and iterate it in a proc/function (I am assuming, once we are able to iterate the array, we should be able to insert as well )

1. Create a Oracle Type using table of  VARCHAR, this can used as a placeholder for array in proc/function.

    

   CREATE OR REPLACE TYPE MY_SCHEMA.txn_type AS TABLE OF varchar (500);

    

2. Create a proc/function to iterate and insert data. 

   
   

   CREATE OR REPLACE PROCEDURE CCIEADM.INSERT_CONFIG (
    P_ARRAY IN txn_type,
    STATUS OUT VARCHAR2
   )
   AS
   
   V_COUNT NUMBER;
   
   BEGIN
   
    V_COUNT :=  0;
    
    FOR i IN 1 .. P_ARRAY.COUNT
    LOOP
    DBMS_OUTPUT.put_line ( 'At position:' ||v_count);
    DBMS_OUTPUT.put_line ('Value:' || P_ARRAY (i) );
   
    V_COUNT := V_COUNT+1;
    
    END LOOP;
   END;
   /

   
   

   
   

    

3. Oracle code to call this function, can help testing your procedure

   
   

   DECLARE 
   
     P_ARRAY KK_TXN_TYPE;
     STATUS VARCHAR2(32767);
   
   BEGIN 
     P_ARRAY := P_ARRAY := TXN_TYPE('sale', 'authenticate', 'create_token') ; 

     -- Modify the code to initialize this parameter
     STATUS := 1;
   
     MY_SCHEMA.INSERT_CONFIG ( P_ARRAY, STATUS );
     COMMIT; 
   END; 

4.  A java code snippet which takes array as input and call the above procedure.

    

   public String insertTransactions(String[] transactionsTypes) {
   
    Connection myConnection = null;
    CallableStatement cstmt = null;
   
    String result = null;
   
    ResultSet oResultSet = null;
   
    try {
     myConnection = ConnectionFactory.getConnection("connPool"); 
           // ConnectionFactory is a util class to get connection
     ArrayDescriptor arrayDiscriptor = null;
   
     arrayDiscriptor = ArrayDescriptor.createDescriptor(
       "MY_SCHEMA.TXN_TYPE", myConnection);  

                   // first argument is the oracle sqltype
   
            cstmt = myConnection.prepareCall("call MY_SCHEMA.INSERT_CONFIG(?)");
     Array array = new ARRAY(arrayDiscriptor, myConnection,
        transactionsTypes); 
        
     //connection passed here should be compatible to the OracleConnection
   
     if (array == null) {
       System.out.println("array does not exist");
     } else {
       System.out.println("yo!! Array created!!");
     }
       cstmt.setArray(1, array);
       cstmt.registerOutParameter(3, OracleTypes.VARCHAR);
       cstmt.execute();
   
       result = cstmt.getString(2);
       System.out.println("proc result in merchant db is " + result);
   
     } catch (SQLException e) {
      e.printStackTrace();
     }
   
     finally {
         try {
      if (oResultSet != null) {
       oResultSet.close();
      }
      if (cstmt != null) {
       cstmt.close();
      }
      if (myConnection != null) {
       myConnection.close();
      }
      } catch (SQLException e) {
       e.printStackTrace();
      }
     }
   
     return result;
   
    }
   
    public static void main(String[] s) {
   
         String transactionTypes[] = { "sale", "create_token", "authentication" };
   
         String status = new TransactionDAO().insertTransactions(reqFieldsValue);
   
         System.out.println("Status" + status);
   
    }

   
   

A simple logback.xml with daily log rollover feature

A simple logback.xml with daily log rollover feature, keeping 8 days of log history date wise.

More on logback in upcoming posts...

 <configuration>  
      <property name="logdir" value="C:\\MyAppLogs" />  
      <appender name="LOGFILE"  
           class="ch.qos.logback.core.rolling.RollingFileAppender">  
            <file>${logdir}/AppLogs/appInfo.log</file>   
           <encoder>  
                <pattern>[ %date ] [%level] [%logger{10}] : %msg%n  
                </pattern>  
           </encoder>  
           <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">  
                     <FileNamePattern>${logdir}/AppLogs/appinfo.log.%d{yyyy-MM-dd}.log</FileNamePattern>  
                     <maxHistory>8</maxHistory>  
           </rollingPolicy>  
      </appender>  
      <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">  
           <encoder>  
                <pattern>%date %level %logger{10} %msg%n</pattern>  
           </encoder>  
      </appender>       
      <root level="INFO">  
           <appender-ref ref="LOGFILE" />  
           <appender-ref ref="STDOUT" />  
      </root>  
 </configuration>  

How to convert date into oracle sysdate using java or how to send date to Oracle procedure

This may be something of your use, hence sharing.

This is how we can send a value to a DATE field in procedure, which will match DB sysdate. 

Please note that it is assumed here, the DB takes the current date in “sysdate” as GMT/UTC timestamp. 

Whereas the JVM may reside on IST(in case of local machine), PST or probably some other timestamp. (This was the actual case in one of my recent projects)

Hence, to maintain a consistency between Oracle’s sysdate and the date which is passed to an Oracle Proc/Function, the below code can come very handy.

stmt.setTimestamp(12, new java.sql.Timestamp(System.currentTimeMillis()), Calendar.getInstance(TimeZone.getTimeZone("GMT")));

Here, 12 is the position of the IN parameter, which is defined as a DATE field in the Backend pl/sql procedure.

These can be used in “last_updated_time” or "creation_date" kind of fields.

PS: I know, I could have used sysdate directly in the procedure too! But, this is a Java way out.