SQL query or PL/SQL function to count business days between two dates

The weekend and holidays should be excluded. It is easy to exclude weekends, but the holiday are specific to each place and should be stored in an additional table. I store the holidays in a table HOLIDAYS_VD:

Select the time interval between two dates

The SQL query is adapted from here.

 select count(*) from (
--  select * from (
 select * from (
 select  dayinbetween, to_char( dayinbetween, 'DY' ) weekday from (
 select   startday+level-1 dayinbetween from (
 select startday ,endday-startday diffdays from (
 select   to_date('01.02.2000','DD.MM.YYYY') endday, to_date('29.02.2000','DD.MM.YYYY') startday from dual
 )
 ) connect by level <= diffdays+1
 )
 )where weekday not in ( 'SAT', 'SUN' )--all not weeknds in between
 ) a
 left join  holidays_vd h on a.dayinbetween=h.day
 where h.day is null
 order by dayinbetween; 

It is almost right, but it does not return 0 is the two dates are the same. I did not corrected it and abandoned it because I needed a PL/SQL function. It is below, it is based on the query above and it seems to work fine.

PL/SQL function subtracting dates and exluding weekends and holidays

create or replace PACKAGE indicators AS
TYPE DateListType IS   TABLE OF DATE;
function subtract_business_dates(    startdate DATE,    enddate   DATE) return integer;
END  ;

create or replace PACKAGE BODY  indicators AS
 function subtract_business_dates(
    startdate DATE,
    enddate   DATE) return integer
IS
  result INTEGER:=1;
 
  dateList DateListType:=DateListType();
  difference_days INTEGER;
BEGIN
  difference_days:=trunc(enddate)-trunc(startdate);
  if difference_days<0 then
  RAISE_APPLICATION_ERROR(-20000, 'The first day is after the second date');
  end if;
    if difference_days=0 then
 return 0;
  end if;
 
  dateList.extend(difference_days);
  FOR i IN dateList.first..dateList.last
  LOOP
    dateList(i):=startdate+i;
 
  END LOOP;
  
    select count(*) into result from (
 
   select * from (
    select  dayinbetween, to_char( dayinbetween, 'DY' ) weekday from (
   select  COLUMN_VALUE dayinbetween  from (table(dateList)) 
      )
    )where weekday not in ( 'SAT', 'SUN' )--all not weeknds in between
   ) a
   left join  holidays_vd h on a.dayinbetween=h.day
    where h.day is null
 
   ;
   return result;
END;
END  ;

Some tests to make sure that the function works well. Note, the table with holidays includes a test date 14.02.2000.

select indicators.subtract_business_dates(to_date('01.02.2000','DD.MM.YYYY'),to_date('29.02.2000','DD.MM.YYYY')) from dual;
select indicators.subtract_business_dates(to_date('04.02.2000','DD.MM.YYYY'),to_date('01.02.2000','DD.MM.YYYY')) from dual; --exception

select indicators.subtract_business_dates(to_date('03.02.2000','DD.MM.YYYY'),to_date('03.02.2000','DD.MM.YYYY')) from dual;--thu
select indicators.subtract_business_dates(to_date('03.02.2000','DD.MM.YYYY'),to_date('04.02.2000','DD.MM.YYYY')) from dual;--fri
select indicators.subtract_business_dates(to_date('03.02.2000','DD.MM.YYYY'),to_date('05.02.2000','DD.MM.YYYY')) from dual;--sat
select indicators.subtract_business_dates(to_date('03.02.2000','DD.MM.YYYY'),to_date('06.02.2000','DD.MM.YYYY')) from dual;--sun
select indicators.subtract_business_dates(to_date('03.02.2000','DD.MM.YYYY'),to_date('07.02.2000','DD.MM.YYYY')) from dual;--mon
select indicators.subtract_business_dates(to_date('03.02.2000','DD.MM.YYYY'),to_date('08.02.2000','DD.MM.YYYY')) from dual;--tue


select indicators.subtract_business_dates(to_date('04.02.2000','DD.MM.YYYY'),to_date('04.02.2000','DD.MM.YYYY')) from dual;--fri
select indicators.subtract_business_dates(to_date('04.02.2000','DD.MM.YYYY'),to_date('05.02.2000','DD.MM.YYYY')) from dual;--sat
select indicators.subtract_business_dates(to_date('04.02.2000','DD.MM.YYYY'),to_date('06.02.2000','DD.MM.YYYY')) from dual;--sun
select indicators.subtract_business_dates(to_date('04.02.2000','DD.MM.YYYY'),to_date('07.02.2000','DD.MM.YYYY')) from dual;--mon
select indicators.subtract_business_dates(to_date('04.02.2000','DD.MM.YYYY'),to_date('08.02.2000','DD.MM.YYYY')) from dual;--tue

select indicators.subtract_business_dates(to_date('10.02.2000','DD.MM.YYYY'),to_date('10.02.2000','DD.MM.YYYY')) from dual;--thu
select indicators.subtract_business_dates(to_date('10.02.2000','DD.MM.YYYY'),to_date('11.02.2000','DD.MM.YYYY')) from dual;--fri
select indicators.subtract_business_dates(to_date('10.02.2000','DD.MM.YYYY'),to_date('12.02.2000','DD.MM.YYYY')) from dual;--sat
select indicators.subtract_business_dates(to_date('10.02.2000','DD.MM.YYYY'),to_date('13.02.2000','DD.MM.YYYY')) from dual;--sun
select indicators.subtract_business_dates(to_date('10.02.2000','DD.MM.YYYY'),to_date('14.02.2000','DD.MM.YYYY')) from dual;--mon holiday
select indicators.subtract_business_dates(to_date('10.02.2000','DD.MM.YYYY'),to_date('15.02.2000','DD.MM.YYYY')) from dual;--tue
select indicators.subtract_business_dates(to_date('10.02.2000','DD.MM.YYYY'),to_date('16.02.2000','DD.MM.YYYY')) from dual;--wed

Copying data from Oracle to MySQL or from any to any database

There is no official tool to export data from Oracle to MySQL database. Recurrently I need to copy a huge table from the source Oracle to MySQL database. It seems to me that configuring any available tools is more complicated than just using a very simple and therefore reliable java class. The class simply executes in the target database an INSERT statement for each row of the SELECT query executed in the source database. I execute such an tiny application as a background job to copy millions of rows from one VM to another overnight.

The prerequisite is that the target table exists in the target database. But DDL is the easiest part of an export.

public class Main {

    static String SOURCE_TABLE_NAME = "ONM_MOL_DATA", TARGET_TABLE_NAME = "ONM_MOL_DATA_COPY";
    String SQL = "select * from " + SOURCE_TABLE_NAME; 
    String INSERT = "insert into " + TARGET_TABLE_NAME + " values()";
    String DELETE = "delete from " + TARGET_TABLE_NAME;

    void run() throws SQLException, IOException {

        try (Connection oracleCon = ConnectionFactory.getOracleConnection();
                Connection mySQLCon = ConnectionFactory.getMySQLConnection();
                Statement oracleStmt = oracleCon.createStatement();
                ResultSet oracleRs = oracleStmt.executeQuery(SQL)) {
            ResultSetMetaData md = oracleRs.getMetaData();

            String mySQLInsert = getInsert(md.getColumnCount());

            // clean
            Statement mySQLDeleteStmt = mySQLCon.createStatement();
            mySQLDeleteStmt.executeUpdate(DELETE);

            // copy
            mySQLCon.setAutoCommit(false);
            System.out.println(mySQLInsert);
            PreparedStatement mySQLInsertStmt = mySQLCon.prepareStatement(mySQLInsert);

            int row = 0;
            while (oracleRs.next()) {
                for (int c = 1; c <= md.getColumnCount(); c++) {
                    mySQLInsertStmt.setObject(c, oracleRs.getObject(c));
                }
                mySQLInsertStmt.executeUpdate();
                row++;
                if (row % 100000 == 0) {
                    System.out.println("row=" + row);
                }
            }
            mySQLCon.commit();
        }

    }

    String getQuestionMarks(int count) {
        String[] ar = new String[count];
        Arrays.fill(ar, "?");
        return String.join(",", ar);
    }

    String getInsert(int count) {
        return INSERT.replace("values()", "values(" + getQuestionMarks(count) + ")");

    }

    public static void main(String... args) throws SQLException, IOException {
        new Main().run();
    }
}

There is nothing special in ConnectionFactory class:

public class ConnectionFactory {

    public static Connection getMySQLConnection() throws SQLException {
         return DriverManager.getConnection(MYSQL_URL);
    }

    public static Connection getOracleConnection() throws SQLException {
        return DriverManager.getConnection(ORACLE_URL);
    }
}

How to recover passwords in SQL developer

I just want to note down and share a useful link https://github.com/maaaaz/sqldeveloperpassworddecryptor with a python script that not only contains a comprehensive user guide but also works. Before stumbling upon it, I also tried some SQL developer plugins that turned out not to work.

Change schema in Oracle

Even though I do it regularly, I always forget the commands.

To display the current user and the current schema:

SELECT sys_context('USERENV','SESSION_USER') as "USER NAME", sys_context('USERENV', 'CURRENT_SCHEMA') as "CURRENT SCHEMA" FROM dual;

To change the current schema, instead of prefixing the table names:

ALTER SESSION SET CURRENT_SCHEMA = hdm;

Add MySQL or Oracle driver to create a datasource in Wildfly 10

In any other server one simply puts any jar into the library folder to make included in the classpath. In contrast, in Wildfly there is no such a folder - it is based on a modular classloading architecture. One needs to create an individual folder for any additional jar, which is called a module. The official documentation recommends including the required jar in the web archive instead of creating modules. However, this is impossible if one needs to create as datasource, which depends on a driver in the server classpath (A datasource can alternatively be deployed, which I find inconvenient).

1. Create a new folder path$JBOSS_HOME/modules/system/layers/base/com/mysql/main

   Inside the created folder, create a file module.xml with contents:

   <?xml version="1.0" encoding="UTF-8"?>
   <module xmlns="urn:jboss:module:1.3" name="com.mysql">
        <resources>
           <resource-root path="mysql-connector-java-5.1.42-bin.jar"/>
        </resources>
         <dependencies>
      <module name="marian.mysqllogger"/> 
           <module name="javax.api"/> 
           <module name="javax.transaction.api"/>
       </dependencies>
   </module>

   Note, module marian.mysqllogger is obviously optional, it logs the executed SQL commands. It is described in my previous posts.

2. Create a new folder path$JBOSS_HOME/modules/system/layers/base/com/oracle/main

   Inside the created folder, create a file module.xml with contents:

   <?xml version="1.0" encoding="UTF-8"?>
   <module xmlns="urn:jboss:module:1.3" name="com.oracle">
        <resources>
           <resource-root path="ojdbc6.jar"/>
        </resources>
   
       <dependencies>
           <module name="javax.api"/> 
           <module name="javax.transaction.api"/>
       </dependencies>
   </module>

3. Copy a MySQL driver jar indicated in the xml file (e.g. mysql-connector-java-5.1.42-bin.jar) into folder $JBOSS_HOME/modules/system/layers/base/com/mysql/main. The oracle driver indicated in the xml file (e.g. ojdbc6.jar) should also be placed into the folder $JBOSS_HOME/modules/system/layers/base/com/oracle/main with the xml file.

4. Finally, the datasources can be created in the Administration Console after the Wildfly is restarted.

   Alternatively, you can directly edit $JBOSS_HOME/standalone/configuration/standalone.xml. Extend the part dedicated to datasources:

           <subsystem xmlns="urn:jboss:domain:datasources:4.0">
               <datasources>
                    <datasource jta="true" jndi-name="java:/OracleDS" pool-name="OracleDS" enabled="true" use-ccm="true">
                       <connection-url>jdbc:oracle:thin:@localhost:1521:orcl2</connection-url>
                       <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
                       <driver>oracle</driver>
                       <security>
                           <user-name>username</user-name>
                           <password>password</password>
                       </security>
                       <validation>
                           <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
                           <background-validation>true</background-validation>
                           <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
                           <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
                       </validation>
                   </datasource>
                   <datasource jta="true" jndi-name="java:/MySqlDS" pool-name="MySqlDS" enabled="true" use-ccm="true">
                       <connection-url>jdbc:mysql://localhost:3306/wildfly?useSSL=false&profileSQL=true&logger=com.mysql.jdbc.log.MySlf4JLogger</connection-url>
                       <driver-class>com.mysql.jdbc.Driver</driver-class>
                       <driver>mysql</driver>
                       <security>
                           <user-name>username</user-name>
                           <password>password</password>
                       </security>
                       <validation>
                           <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                           <background-validation>true</background-validation>
                           <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                       </validation>
                   </datasource>
                   <drivers>
                       <driver name="oracle" module="com.oracle">
                           <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
                       </driver>
                       <driver name="mysql" module="com.mysql">
                           <driver-class>com.mysql.jdbc.Driver</driver-class>
                       </driver>
                   </drivers>
               </datasources>
           </subsystem>

   com.mysql.jdbc.log.MySlf4JLogger is a handy logger of executed SQL statements. It is loaded from marian.mysqllogger module.

   Restart the Wildfly.

Changing font size and default language in SQL developer or Data Integrator

Changing the default tiny font size

By default the letters in SQL developer or Data integrator are hardly visible. The steps to increase the font size are quite the same on windows and linux. In %userprofile%/AppData on Windows or $HOME on Linux search for a file ide.properties. One file will be found in SQL developer folder, whereas in ODI folder two files will be found (No idea why because it does not matter).

In SQL developer's file uncomment a line with Ide.FontSize=18 and set the convienient font size (no less than 18).

Add the the same line to the two ODI files which seems identical on Linux, and different on Windows (the last two pictures).

Restart the applications.

Changing the default language to English

Java uses the default locale of the computer. To change the language, one needs to change JVM system variables. It can be done in a configuration file ide.conf located in the installation folder of SQL developer or ODI, e.g C:\oracle\Middleware\Oracle_Home. Add to lines the end of the file and then restart the application:

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US