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 a table to another MySQL database

Unfortunately, in MySQL there are no decent export tools similar to Data Pump in Oracle. Luckily one can copy either the entire database data folder or only the required individual tables. The text is adapted from the MySQL manual.

1. In the source database, first generate the CREATE statement for the target table so that an identical table can be created in the target database. Then execute an SQL command:

   FLUSH TABLES TEMP FOR EXPORT;

   A TEMP.cfg file is created in the MySQL data directory. This file has to be copied to the target machine. I first copy it to /tmp, so that it becomes more accessible.

   sudo cp TEMP.{ibd,cfg} /tmp
   cd /tmp
   chmod 644 TEMP.*
   

   Then execute an SQL command. The previously created TEMP.cfg disappears.

   UNLOCK TABLES;

2. In the target database, execute SQL to create an identical table using the DDL from the source database and discard its tablespace:

   CREATE TABLE `TEMP` (
     `INTERVENTION_LIBELLE` varchar(93) NOT NULL,
     ...
     KEY `INTERVENTION_LIBELLE_idx` (`INTERVENTION_LIBELLE`),
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

   ALTER TABLE TEMP DISCARD TABLESPACE;

   Copy the files from the source machine to the local MySQL data folder:

   cd /data1/mysql/axya/
   scp test@source-machine:/tmp/TEMP.{ibd,cfg} .
   chmod 640 TEMP.*
   chown mysql:mysql TEMP.*
   

   Execute the last SQL command after which the data becomes usable.

   ALTER TABLE TEMP IMPORT TABLESPACE;

How to cast varchar to int or datetime in MySQL

To convert a varchar value into int:

update TEMP set PATIENT_ID=cast(PATIENT_ID_ORIG as UNSIGNED);

To convert a varchar value into datetime

One need to use a troublesome function STR_TO_DATE. The problem with this function is that it never produces errors, instead it produces NULL and a warning that has to be revealed by an additional statement:

SELECT STR_TO_DATE('05/11/2012 08:30:00','%d/%m/%Y %H:%i:%s');
2012-11-05 08:30:00

SELECT STR_TO_DATE('505/11/2012 08:30:00','%d/%m/%Y %H:%i:%s');
null

SHOW WARNINGS;
Warning 1411 Incorrect datetime value: '505/11/2012 08:30:00' for function str_to_date

There might also be crazy conversion to zero dates. I converted valid values like that:

SET  sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES';
SELECT @@SESSION.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

update TEMP set ENTREE_SALLE =STR_TO_DATE(ENTREE_SALLE_ORIG,'%d/%m/%Y %H:%i:%s');

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.

Displaying all SQL commands executed by MySQL Connector/J driver in a buggy or Hibernate-based application

Activating hibernate loggers

While developing an application using JPA to access a database it is really useful to see how inefficient and numerous the executed SQL statements are. In fact, if you use any relations in entities you can be surprised to learn how many SQL statements are executed by Hibernate or Eclipselink to load an entity with relations. According to Hibernate documentation, the SQL statements can be displayed by enabling org.hibernate.SQL logger. It is enough to add a line into log4j.properties:

log4j.logger.org.hibernate.SQL=debug

However, the logged statements will be incomplete with question marks in place of any values. For example, the output can be similar to:

update users set date_format=? where user_id=?
delete from users where user_id=?

To see the hidden by default bind parameters, one needs to enable additional loggers:

log4j.logger.org.hibernate.type=trace
log4j.logger.org.hibernate.type.descriptor.sql=trace

But then the log becomes immense due to predominantly irrelevant output and thus quite illegible. So the point is - there is no standard way in Hibernate to see the executed SQL statements clean and complete. But there is an easy and universal workaround.

Using a customized MySQL logger

Even without JPA, while installing or debugging a poorly documented Java application, it helps to know what SQL commands fail or produce unexpected results. Recently, I have been installing and customizing such an application. Fortunately, it is an open source application and its code can be easily modified. Exposing the failing SQL statements helped me to make undocumented adjustments in the underlying MySQL database so that the application gradually started to function.

The SQL statements processed by MySQL driver can be displayed by adding property profileSQL to the connection URL.

jdbc:mysql://hostname/database?user=user&password=pass&useSSL=false&profileSQL=true

The default logger included in the SQL driver will be used to produce the output. The problem is that the output will include not only the executed SQL statements but also several times as many lines with irrelevant content such as pointless diagnostic messages, timestamps or empty space. Overall the output will be illegible. To record only SQL statements, I composed a customized logger class that filters out all the pollution.

package com.mysql.jdbc.log;

import java.util.Date;

import com.mysql.jdbc.profiler.ProfilerEvent;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

 public class MyStandardLogger implements Log {
  
    public MyStandardLogger(String name) {
        this(name, false);
    }
 
    public MyStandardLogger(String name, boolean logLocationInfo) {
       
    }
 
    public boolean isDebugEnabled() {
        return true;
    }
 
    public boolean isErrorEnabled() {
        return true;
    }
 
    public boolean isFatalEnabled() {
        return true;
    }
 
    public boolean isInfoEnabled() {
        return true;
    }
 
    public boolean isTraceEnabled() {
        return true;
    }
 
    public boolean isWarnEnabled() {
        return true;
    }
 
    public void logDebug(Object message) {
        logInternal( message );
    }
 
    public void logDebug(Object message, Throwable exception) {
        logInternal( message );
    }
 
    public void logError(Object message) {
        logInternal( message );
    }
 
    public void logError(Object message, Throwable exception) {
        logInternal( message );
    }
 
    public void logFatal(Object message) {
        logInternal( message );
    }
 
    public void logFatal(Object message, Throwable exception) {
        logInternal( message );
    }
 
    public void logInfo(Object message) {
        logInternal( message );
    }
 
    public void logInfo(Object message, Throwable exception) {
        logInternal( message );
    }
 
    public void logTrace(Object message) {
        logInternal( message );
    }
 
    public void logTrace(Object message, Throwable exception) {
        logInternal( message );
    }
 
    public void logWarn(Object message) {
        logInternal(  message );
    }
 
    public void logWarn(Object message, Throwable exception) {
        logInternal( message );
    }
    DateFormat df = new SimpleDateFormat("HH:mm:ss.SSS");

    protected void logInternal(Object msg) {
        if (msg instanceof ProfilerEvent) {
            ProfilerEvent evt = (ProfilerEvent) msg;
            String evtMessage = evt.getMessage();

            if (evtMessage != null) {
                System.out.println(">SQL: " + df.format(new Date())+"\t"+evtMessage);
            }
        }
    }
}

The jar containing this class must be placed into the application class path. I put it into the same folder as the MySQL driver - CATALINA_HOME/lib.

In any ordinary application one would have only one place with the connection string. But to debug the application I needed to see the SQL statements received by JDBC driver from Connection created by DriverManger, and DataSource classes obtained from Tomcat or some Spring connection pools. So in some java class I modified the connection string:

String url ="jdbc:mysql://" + host + "/" + database +
                        "?user=" + userName + "&password=" + password +
                        "&zeroDateTimeBehavior=convertToNull&useSSL=false&profileSQL=true&logger=com.mysql.jdbc.log.MyStandardLogger";

In a Spring application context configuration xml one cannot use & sign, so the connections string looked like:

<bean id="businessDataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="${db.driver}"/>
    <property name="url" value="${db.connection_string}${db.portal_db_name}?zeroDateTimeBehavior=convertToNull&amp;useSSL=false&amp;profileSQL=true&amp;logger=com.mysql.jdbc.log.MyStandardLogger"/>
    <property name="username" value="${db.user}"/>
    <property name="password" value="${db.password}"/>
</bean>

And in the Tomcat context.xml the URL was specifed like:

<Resource name="jdbc/cbioportal" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000"
        username="cbio_user" password="pass" driverClassName="com.mysql.jdbc.Driver"
        connectionProperties="zeroDateTimeBehavior=convertToNull;useSSL=false;profileSQL=true;logger=com.mysql.jdbc.log.MyStandardLogger;"
        testOnBorrow="true"
        validationQuery="SELECT 1"
        url="jdbc:mysql://localhost:3306/cbioportal"/>

Another version of MySQL logger passing SQL statements to the included slf4-compatible logger

Wildfly is different from other servers in a few respects. I have not tried to understand why, but the output from System.out.println() is not always saved to the server log. So used a similar class to log SQL statements. The jar was added as a dependency for MySQL driver. I will describe the unusual Wildfly-specific deployment of database drivers that must be installed before a dependent datasource is created in a later post.

package com.mysql.jdbc.log;

import com.mysql.jdbc.profiler.ProfilerEvent;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class MySlf4JLogger extends StandardLogger {

    Logger logger = LoggerFactory.getLogger(getClass().getName());

    public MySlf4JLogger(String name) {
        super(name, false);
    }

    public MySlf4JLogger(String name, boolean logLocationInfo) {
        super(name, logLocationInfo);
    }

    DateFormat df = new SimpleDateFormat("HH:mm:ss.SSS");

    @Override
    protected void logInternal(int level, Object msg, Throwable exception) {
        if (msg instanceof ProfilerEvent) {
            ProfilerEvent evt = (ProfilerEvent) msg;
            String str = evt.getMessage();
            if (str != null) {
                logger.debug(str);
            }
        }
    }
}

Registering the logger of SQL statements in persistence.xml

This technique will nicely expose complete SQL statements with either Hibernate or Eclipselink. For example, how I use the logger in my persistence.xml used by JUnit tests.

 <persistence-unit name="JavaApplication316PUTEST" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
     <class>entities.User</class>
     <class>entities.Food</class>
     <class>entities.Meal</class>
     <shared-cache-mode>NONE</shared-cache-mode>
     <properties>
         <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/wildfly?useSSL=false&amp;profileSQL=true&amp;logger=com.mysql.jdbc.log.MySlf4JLogger"/>
         <property name="javax.persistence.jdbc.user" value="wildfly"/>
         <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
         <property name="javax.persistence.jdbc.password" value="1234"/>
         <property name="javax.persistence.schema-generation.database.action" value="none"/>
     </properties>
 </persistence-unit>

How to compare results of two SQL queries

Sometimes, for example during unit tests of some PL/SQL procedures, it is required to compare the results of any two SQL queries. In the results of the queries both the column composition and all the returned values should be compared. In Oracle database this can be done using DBMS_SQL package.

The simple package below compares row numbers, column numbers, column names and types, and all the values in the corresponding columns of the corresponding rows produced by two specified SQL queries. The procedure compare_query_results throws an informative uncaught exception if any mismatch is detected. The procedure aborts when the first mismatch is detected with the rest values remain not considered. Specifically, the following custom exceptions are thrown:

• The specified two queries returned different row numbers
• The results have different column numbers
• The corresponding column names or types differ
• The values in the corresponding columns of the corresponding rows differ

CREATE OR REPLACE PACKAGE "MY_COMPARE"
IS
  PROCEDURE compare_query_results(
      query1              VARCHAR2,
      query2              VARCHAR2,
      skip_columns_regexp VARCHAR2:=null);
END;
/
CREATE OR REPLACE PACKAGE BODY "MY_COMPARE"
IS
  c1 INTEGER;
  c2 INTEGER;
  rec_tab1 DBMS_SQL.DESC_TAB3;
  rec_tab2 DBMS_SQL.DESC_TAB3;
  namevar VARCHAR2(4000);
  numvar  NUMBER;
  datevar DATE;
  PROCEDURE close_cursors(
      c1 IN OUT INTEGER,
      c2 IN OUT INTEGER )
  IS
  BEGIN
    DBMS_SQL.CLOSE_CURSOR(c1);
    DBMS_SQL.CLOSE_CURSOR(c2);
  END;
  PROCEDURE compare_all_values(
      c1 INTEGER,
      c2 INTEGER,
      rec_tab DBMS_SQL.DESC_TAB3,
      skip_columns_regexp VARCHAR2 )
  IS
    rows_fetched1 INTEGER;
    rows_fetched2 INTEGER;
    row_conter pls_integer:=0;
    namevar1 VARCHAR2(4000);
    numvar1  NUMBER;
    datevar1 DATE;
    namevar2 VARCHAR2(4000);
    numvar2  NUMBER;
    datevar2 DATE;
  BEGIN
    LOOP
      rows_fetched1   := DBMS_SQL.FETCH_ROWS(c1);
      rows_fetched2   := DBMS_SQL.FETCH_ROWS(c2);
      IF rows_fetched1!= rows_fetched2 THEN
        RAISE_APPLICATION_ERROR(-20000, 'Queries returned different row numbers');
      END IF;
      EXIT
    WHEN rows_fetched1=0;
      row_conter     :=row_conter+1;
      FOR i IN rec_tab.first .. rec_tab.last
      LOOP
        IF (skip_columns_regexp IS NOT NULL AND REGEXP_INSTR( rec_tab(i).col_name,skip_columns_regexp)>0) THEN
          CONTINUE;
        END IF;
        IF (rec_tab(i).col_type = 1) THEN
          DBMS_SQL.COLUMN_VALUE(c1, i, namevar1);
          DBMS_SQL.COLUMN_VALUE(c2, i, namevar2);
          IF namevar1!=namevar2 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Column '||rec_tab(i).col_name||' values differ: '||namevar1||'; '||namevar2);
          END IF;
        ELSIF (rec_tab(i).col_type = 2) THEN
          DBMS_SQL.COLUMN_VALUE(c1, i, numvar1);
          DBMS_SQL.COLUMN_VALUE(c2, i, numvar2);
          IF numvar1!=numvar2 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Column '||rec_tab(i).col_name||' values differ: '||numvar1||'; '||numvar2);
          END IF;
        ELSIF (rec_tab(i).col_type = 12) THEN
          DBMS_SQL.COLUMN_VALUE(c1, i, datevar1);
          DBMS_SQL.COLUMN_VALUE(c2, i, datevar2);
          IF datevar1!=datevar2 THEN
            RAISE_APPLICATION_ERROR(-20000, 'Column '||rec_tab(i).col_name||' values differ: '||datevar1||'; '||datevar2);
          END IF;
        ELSE
          RAISE_APPLICATION_ERROR(-20000, 'Unknown column type: '||rec_tab(i).col_type);
        END IF;
      END LOOP;
    END LOOP;
  END;
   
  PROCEDURE compare_column_definitions(
      rec_tab1 DBMS_SQL.DESC_TAB3,
      rec_tab2 DBMS_SQL.DESC_TAB3)
  IS
  BEGIN
    IF rec_tab1.count!=rec_tab2.count THEN
      RAISE_APPLICATION_ERROR(-20001, 'Tables have different numbers of columns');
    END IF;
    FOR i IN rec_tab1.first .. rec_tab1.last
    LOOP
      IF rec_tab1(i).col_name != rec_tab2(i).col_name THEN
        RAISE_APPLICATION_ERROR(-20000, 'Column names differ at index: '||i||'; '|| rec_tab1(i).col_name||'; '||rec_tab2(i).col_name );
      END IF;
      IF rec_tab1(i).col_type != rec_tab2(i).col_type THEN
        RAISE_APPLICATION_ERROR(-20000, 'Column types differ at index: '||i||'; '|| rec_tab1(i).col_type||'; '||rec_tab2(i).col_type );
      END IF;
    END LOOP;
  END;
  FUNCTION open_cursor_and_define_columns(
      query VARCHAR2,
      colcnt OUT INTEGER,
      rec_tab OUT DBMS_SQL.DESC_TAB3)
    RETURN INTEGER
  IS
    c INTEGER;
    d INTEGER;
  BEGIN
    c := DBMS_SQL.OPEN_CURSOR;  
    DBMS_SQL.PARSE(c,query, DBMS_SQL.NATIVE);
    d := DBMS_SQL.EXECUTE(c);  
    DBMS_SQL.DESCRIBE_COLUMNS3(c, colcnt, rec_tab);
     FOR i IN 1 .. colcnt
    LOOP
       IF rec_tab(i).col_type = 2 THEN
        DBMS_SQL.DEFINE_COLUMN(c, i, numvar);
      ELSIF rec_tab(i).col_type = 12 THEN
        DBMS_SQL.DEFINE_COLUMN(c, i, datevar);
      ELSE --1 varchar2
        DBMS_SQL.DEFINE_COLUMN(c, i, namevar, rec_tab(i).col_max_len);
      END IF;
    END LOOP;
    RETURN c;
  END;
  PROCEDURE compare_query_results(
      query1              VARCHAR2,
      query2              VARCHAR2,
      skip_columns_regexp VARCHAR2)
  IS
    colcnt1 INTEGER;
    colcnt2 INTEGER;
  BEGIN
    c1:= open_cursor_and_define_columns(query1, colcnt1, rec_tab1);
    c2:= open_cursor_and_define_columns(query2, colcnt2, rec_tab2);
    BEGIN
      IF colcnt1!=colcnt2 THEN
        RAISE_APPLICATION_ERROR(-20000, 'Tables have different column numbers');
      END IF;
      compare_column_definitions( rec_tab1 ,rec_tab2 );
      compare_all_values(c1 , c2 ,rec_tab1, skip_columns_regexp ) ;
      close_cursors(c1 ,c2 );
      DBMS_OUTPUT.PUT_LINE('THE RESULTS ARE EQUAL');
    EXCEPTION
    WHEN OTHERS THEN
      close_cursors(c1 ,c2 );
      DBMS_OUTPUT.PUT_LINE('VALUES DIFFER, SEE ABOVE');
      raise;
    END;
  END;
END;
/

Evidently, the queries supplied to the procedure must be ordered. Let's try some examples in HR schema. First, let's create a copy of EMPLOYEES tables. This table will be modified and compared to the original.

set serveroutput on;

create table employees_copy as select * from employees;

declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2);
end;
/

THE RESULTS ARE EQUAL

Often the values in the sorted results of two queries are identical, except for primary keys. For the procedure to ignore the values in the primary key column, the user can specify as the third argument a regular expression matching the primary key column name. To demonstrate, I modify employee_id column in the copy table.

update employees_copy set employee_id=employee_id+1111;

declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2);
end;
/

Error starting at line : 5 in command -
declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2);
end;
Error report -
ORA-20000: Column EMPLOYEE_ID values differ: 100; 1211
ORA-06512: at "HR.MY_COMPARE", line 138
ORA-06512: at line 5
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
VALUES DIFFER, SEE ABOVE

As expected, the exception says: Column EMPLOYEE_ID values differ: 100; 1211. Now let's try with the name of the column to skip.

declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2, 'EMPLOYEE_ID');
end;
/

THE RESULTS ARE EQUAL

Let's try selecting different column sets:

declare
query1 varchar2(4000):='select FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,MANAGER_ID from employees order by employee_id';
query2 varchar2(4000):=replace(query1,'PHONE_NUMBER','DEPARTMENT_ID');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2 );
end;
/

Error starting at line : 16 in command -
declare
query1 varchar2(4000):='select FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,MANAGER_ID from employees order by employee_id';
query2 varchar2(4000):=replace(query1,'PHONE_NUMBER','DEPARTMENT_ID');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2 );
end;
Error report -
ORA-20000: Column names differ at index: 4; PHONE_NUMBER; DEPARTMENT_ID
ORA-06512: at "HR.MY_COMPARE", line 138
ORA-06512: at line 5
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
VALUES DIFFER, SEE ABOVE

The thrown exception indicates that: Column names differ at index: 4; PHONE_NUMBER; DEPARTMENT_ID

Let's make subtle change in a row of the copy table.

update employees_copy set FIRST_NAME='TEST!' where employee_id=1225;
 
declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2, 'EMPLOYEE_ID');
end;
/

Error starting at line : 6 in command -
declare
query1 varchar2(4000):='select * from employees  order by employee_id';
query2 varchar2(4000):=replace(query1,'employees','employees_copy');
begin
 MY_COMPARE.COMPARE_QUERY_RESULTS(query1,query2, 'EMPLOYEE_ID');
end;
Error report -
ORA-20000: Column FIRST_NAME values differ: Den; TEST!
ORA-06512: at "HR.MY_COMPARE", line 138
ORA-06512: at line 5
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
VALUES DIFFER, SEE ABOVE

So this simple packaged procedure can help you to make your tests.