Passing a list of dates to an oracle db function via java

Question

I need to pass alist of dates to a DB function and compare if the selected date falls in the list of these holidays being passed. My DB function currently is as below. Need to pass alist of dynamic holidays as an input parameter to this function and check if START_DATE falls in that holiday list also.

create or replace 
FUNCTION getWorkingDays (DATE_ONE DATE, DATE_TWO DATE) RETURN NUMBER
IS
DAY_COUNT NUMBER := 0;
START_DATE DATE;
END_DATE DATE;
BEGIN -- loop through and update
IF(DATE_ONE is not null and DATE_TWO is not null)
THEN
IF DATE_ONE < DATE_TWO THEN
  START_DATE := DATE_ONE;
  END_DATE := DATE_TWO;
ELSE
  START_DATE := DATE_TWO;
  END_DATE := DATE_ONE;
END IF;

WHILE START_DATE < END_DATE
LOOP
  IF TO_CHAR(START_DATE,'DY') NOT IN ('SAT','SUN') THEN
    DAY_COUNT := DAY_COUNT + 1;
END IF;
  START_DATE := START_DATE + 1;
END LOOP;
END IF;
RETURN DAY_COUNT;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END getWorkingDays;

How do i pass a list of dates to a DB function as its input parameter & how do i compare if start date falls in this date inside the function? — Ann, Apr 26 '17 at 06:57
Have a look at [this question](http://stackoverflow.com/questions/19888520/pass-array-from-java-to-oracle-java-sql-sqlexception-fail-to-convert-to-intern) for the first part of your question. Second part of the question is algorithm development which is outside the scope of SO questions. — Nitish, Apr 26 '17 at 07:41

STaefi · Answer 1 · 2017-04-26T09:22:43.713

Passing an array to database functions has many ways. A simple one is as follow:

First you should create a TABLE type in your DB schema:

CREATE TYPE DATE_ARRAY AS TABLE OF DATE;

After that your should write a FUNCTION with this new type of input:

-- a dummy function just for presenting the usage of input array
CREATE FUNCTION Date_Array_Test_Function(p_data IN DATE_ARRAY) 
RETURN INTEGER  
IS
    TYPE Cur IS REF CURSOR;
    MyCur cur;

    single_date DATE;
BEGIN
    /* Inside this function you can do anything you wish 
        with the input parameter: p_data */

    OPEN MyCur FOR SELECT * FROM table(p_data);

    LOOP
      FETCH MyCur INTO single_date;
      EXIT WHEN MyCur%NOTFOUND;

      dbms_output.put_line(to_char(single_date));
    END LOOP;

    RETURN 0;

END Date_Array_Test_Function;

Now in the java code you can use the following code to call such a function with an array input type:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

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

public class Main 
{           
    public static void main(String[] args) throws SQLException 
    {
        Connection c = DriverManager.getConnection(url, user, pass);

        String query = "begin ? := date_array_test_function( ? ); end;";

        // note the uppercase "DATE_ARRAY"
        ArrayDescriptor arrDescriptor = ArrayDescriptor.createDescriptor("DATE_ARRAY", c);

        // Test dates
        Date[] inputs = new Date[] {new Date(System.currentTimeMillis()),
                                    new Date(System.currentTimeMillis()),
                                    new Date(System.currentTimeMillis())};

        ARRAY array = new ARRAY(arrDescriptor, c, inputs);

        CallableStatement cs = c.prepareCall(query);
        cs.registerOutParameter(1, Types.INTEGER); // the return value
        cs.setObject(2, array); // the input of the function
        cs.executeUpdate();

        System.out.println(cs.getInt(1));
    }
}

Hope this would be helpful.

Good Luck

score 0 · Answer 2 · edited May 23 '17 at 12:34

CREATE TYPE DateList IS TABLE OF DATE;
/

CREATE FUNCTION getWorkingDays (
  in_start_date IN  DATE,
  in_end_date   IN  DATE,
  in_date_list  IN  DateList
) RETURN NUMBER
IS
  p_start_date   DATE;
  p_end_date     DATE;
  p_working_days NUMBER;
BEGIN
  IF in_start_date IS NULL OR in_end_date IS NULL THEN
    RETURN NUll;
  END IF;

  p_start_date := TRUNC( LEAST( in_start_date, in_end_date ) );
  p_end_date   := TRUNC( GREATEST( in_start_date, in_end_date ) );

  -- 5/7 * ( Number of days between monday of the week containing the start date
  --         and monday of the week containing the end date )
  -- + LEAST( day of week for end date, 5 )
  -- - LEAST( day of week for start date, 5 )
  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ) + 1, 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ) + 1, 5 );

  IF in_date_list IS NOT NULL THEN
    FOR i IN 1 .. in_date_list.COUNT LOOP
      IF in_date_list(i) >= p_start_date AND in_date_list(i) < p_end_date THEN
        p_working_days := p_working_days - 1;
      END IF;
    END LOOP;
  END IF;

  RETURN p_working_days;
END;
/

@STaefi Has an example of how to pass an array to a procedure. There are other examples like: "How to pass List from java to Oracle Procedure?".

Passing a list of dates to an oracle db function via java

2 Answers2