A Tale of Two Oracle JDKs

There has been concern recently that Java developers will inadvertently use the wrong Oracle-provided JDK implementation now (as of JDK 11) that Oracle provides builds of the open source OpenJDK and also provides commercial JDK builds based largely on the OpenJDK source.

The table below compares and contrasts the two versions of JDK that Oracle provides (but Oracle won't be the only supplier of JDK builds available for free and/or for support charge). Please keep in mind this represents my best personal understanding of the differences and similarities of Oracle's two offerings; please check with an authoritative source before making decisions regarding which Oracle JDK implementation to use (or even whether to use an Oracle implementation).

JDK Builds from Oracle (https://jdk.java.net/)
Characteristic	Oracle OpenJDK Builds	Oracle JDK (Java SE Downloads)
Oracle's Descriptions	"End users and developers looking for free JDK versions: Oracle OpenJDK offers the same features and performance as Oracle JDK under the GPL license."	"Oracle Customers and ISVs targeting Oracle LTS releases: Oracle JDK is Oracle's supported Java SE version for customers and for developing, testing, prototyping or demonstrating your Java applications."
Web Address	https://jdk.java.net/11/	https://www.oracle.com/technetwork/java/javase/downloads/jdk11-downloads-5066655.html
License	GNU General Public License, version 2, with the Classpath Exception	Oracle Technology Network License Agreement for Oracle Java SE
Build Platforms (Binaries)	Linux / x64 (tar.gz) macOS / x64 (tar.gz) Windows / x64 (zip)	Linux / x64 (tar.gz, deb, rpm) macOS / x64 (tar.gz, dmg) Windows / x64 (zip, exe) Solaris SPARC (tar.gz)
Pay for Production Use	No	Yes
Oracle Support	Select bug fixes and security patches until next JDK version's General Availability release	Java SE Subscription (Support for LTS versions for up to 8 years)
		Several Other Paid Support Offerings under "Oracle Customers"
java -version Example	openjdk version "11" 2018-09-25 OpenJDK Runtime Environment 18.9 (build 11+28) OpenJDK 64-Bit Server VM 18.9 (build 11+28, mixed mode)	java version "11" 2018-09-25 Java(TM) SE Runtime Environment 18.9 (build 11+28) Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11+28, mixed mode)
Required to Accept License Agreement	No	Yes
Java Flight Recorder	Yes	Yes
Java Mission Control	Yes	Yes
Advanced Management Console	No	Yes
This table represents my personal understanding only; refer to Oracle documentation and OpenJDK documentation for more authoritative information (see "References" below).

There are other implementations of the JDK that will be available as well, with some being free and some requiring payment. I did not discuss those alternatively provided JDKs in this post in order to keep the comparison cleaner and simpler between the "Oracle OpenJDK builds" and the "Oracle JDK builds".

References

• Oracle Java SE Support Roadmap
• Oracle JDK Releases for Java 11 and Later
• Java SE Offerings
• Update and FAQ on the Java SE Release Cadence
• OpenJDK Trademark Notice (Oracle owns the trademark)

Too Many PreparedStatement Placeholders in Oracle JDBC

There are multiple causes of the ORA-01745 ("invalid host/bind variable name error") error when using an Oracle database. The Oracle 9i documentation on errors ORA-01500 through ORA-02098 provides more details regarding ORA-01745. It states that the "Cause" is "A colon in a bind variable or INTO specification was followed by an inappropriate name, perhaps a reserved word." It also states that the "Action" is "Change the variable name and retry the operation." In the same Oracle 12g documentation, however, there is no description of "cause" or "action" for ORA-01745, presumably because there are multiple causes and multiple corresponding actions associated with this message. In this post, I will focus on one of the perhaps less obvious causes and the corresponding action for that cause.

Some of the common causes for ORA-01745 that I will NOT be focusing on in this post include using an Oracle database reserved name (reserved word) as an identifier, extraneous or missing colon or comma, or attempting to bind structure names (rather than variables) to the placeholders.

In addition to the causes just listed and likely in addition to other potential causes of ORA-01745, another situation that can cause the ORA-01745 error is using too many ? placeholders in a JDBC PreparedStatement with the Oracle database. I will demonstrate in this post that the number of ? placeholders in a PreparedStatement that cause this ORA-01745 is 65536 (2¹⁶).

I have blogged previously on the ORA-01795 error that occurs when one attempts to include more than 1000 values in an Oracle SQL IN condition. There are multiple ways to deal with this limitation and one of the alternative approaches might be to use multiple ORs to "OR" together more than 1000 values. This will typically be implemented with a PreparedStatement and with a ? placeholder placed in the SQL statement for each value being OR-ed. This PreparedStatement-based alternate approach employing ? placeholders will only work as long as the number of vales being OR-ed together is smaller than 65536.

The code listing that follows demonstrates how a SQL query against the Oracle HR schema can be generated to make it easy to reproduce the ORA-01745 error with too many ? placeholders (full code listing is available on GitHub).

Building Up Prepared Statement with Specified Number of ? Placeholders

/**
 * Constructs a query using '?' for placeholders and using
 * as many of these as specified with the int parameter.
 *
 * @param numberPlaceholders Number of placeholders ('?')
 *    to include in WHERE clause of constructed query.
 * @return SQL Query that has provided number of '?" placeholders.
 */
private String buildQuery(final int numberPlaceholders)
{
   final StringBuilder builder = new StringBuilder();
   builder.append("SELECT region_id FROM countries WHERE ");
   for (int count=0; count < numberPlaceholders-1; count++)
   {
      builder.append("region_id = ? OR ");
   }
   builder.append("region_id = ?");
   return builder.toString();
}

The next code listing demonstrates building a PreparedStatement based on the query constructed in the last code listing and setting its placeholders with a number of consecutive integers that match the number of ? placeholders.

Configuring PreparedStatement's ? Placeholders

/**
 * Execute the provided query and populate a PreparedStatement
 * wrapping this query with the number of integers provided
 * as the second method argument.
 * 
 * @param query Query to be executed.
 * @param numberValues Number of placeholders to be set in the
 *    instance of {@code PreparedStatement} used to execute the
 *    provided query.
 */
private void executeQuery(final String query, final int numberValues)
{
   try (final Connection connection = getDatabaseConnection();
        final PreparedStatement statement = connection.prepareStatement(query))
   {
      for (int count = 0; count < numberValues; count++)
      {
         statement.setInt(count+1, count+1);
      }
      final ResultSet rs = statement.executeQuery();
      while (rs.next())
      {
         out.println("Region ID: " + rs.getLong(1));
      }
   }
   catch (SQLException sqlException)
   {
      out.println("ERROR: Unable to execute query - " + sqlException);
   }
}

The next screen snapshot shows the ORA-01745 error occurring when the number of ? placeholders applied is 65536.

This example shows that there is a maximum number of ? placeholders that can be used in an Oracle SQL statement. Fortunately, there are other ways to accomplish this type of functionality that do not have this ORA-01475 limit of 65536 ? placeholders or the 1000 IN elements limit that causes an ORA-01795 error

Fixed-Point and Floating-Point: Two Things That Don't Go Well Together

One of the more challenging aspects of software development can be dealing with floating-point numbers. David Goldberg's 1991 Computing Surveys paper What Every Computer Scientist Should Know About Floating-Point Arithmetic is a recognized classic treatise on this subject. This paper not only provides an in-depth look at how floating-point arithmetic is implemented in most programming languages and computer systems, but also, through its length and detail, provides evidence of the nuances and difficulties of this subject. The nuances of dealing with floating-point numbers in Java and tactics to overcome these challenges are well documented in sources such as JavaWorld's Floating-point Arithmetic, IBM DeveloperWorks's Java's new math, Part 2: Floating-point numbers and Java theory and practice: Where's your point?, Dr. Dobb's Java's Floating-Point (Im)Precision and Fixed, Floating, and Exact Computation with Java's Bigdecimal, Java Glossary's Floating Point, Java Tutorial's Primitive Data Types, and NUM04-J. Do not use floating-point numbers if precise computation is required.

Most of the issues encountered and discussed in Java related to floating-point representation and arithmetic are caused by the inability to precisely represent (usually) decimal (base ten) floating point numbers with an underlying binary (base two) representation. In this post, I focus on similar consequences that can result from mixing fixed-point numbers (as stored in a database) with floating-point numbers (as represented in Java).

The Oracle database allows numeric columns of the NUMBER data type to be expressed with two integers that represent "precision" and "scale". The PostgreSQL implementation of the numeric data type is very similar. Both Oracle's NUMBER(p,s) and PostgreSQL's numeric(p,s) allow the same datatype to represent essentially an integral value (precision specified but scale not specified), a fixed-point number (precision and scale specified), or a floating-point number (neither precision nor scale specified). Simple Java/JDBC-based examples in this post will demonstrate this.

For the examples in this post, a simple table named DOUBLES in Oracle and doubles in PostgreSQL will be created. The DDL statements for defining these simple tables in the two database are shown next.

createOracleTable.sql

CREATE TABLE doubles
(
   int NUMBER(5),
   fixed NUMBER(3,2),
   floating NUMBER
);

createPgTable.sql

CREATE TABLE doubles
(
   int numeric(5),
   fixed numeric(3,2),
   floating numeric
);

With the DOUBLES table created in Oracle database and PostgreSQL database, I'll next use a simple JDBC PreparedStatement to insert the value of java.lang.Math.PI into each table for all three columns. The following Java code snippet demonstrates this insertion.

Inserting Math.PI into DOUBLES Columns

/** SQL syntax for insertion statement with placeholders. */
private static final String INSERT_STRING =
   "INSERT INTO doubles (int, floating, fixed) VALUES (?, ?, ?)";


final Connection connection = getDatabaseConnection(databaseVendor);
try (final PreparedStatement insert = connection.prepareStatement(INSERT_STRING))
{
   insert.setDouble(1, Math.PI);
   insert.setDouble(2, Math.PI);
   insert.setDouble(3, Math.PI);
   insert.execute();
}
catch (SQLException sqlEx)
{
   err.println("Unable to insert data - " + sqlEx);
}

Querying DOUBLES Columns

/** SQL syntax for querying statement. */
private static final String QUERY_STRING =
   "SELECT int, fixed, floating FROM doubles";

final Connection connection = getDatabaseConnection(databaseVendor);
try (final Statement query = connection.createStatement();
     final ResultSet rs = query.executeQuery(QUERY_STRING))
{
   out.println("\n\nResults for Database " + databaseVendor + ":\n");
   out.println("Math.PI :        " + Math.PI);
   while (rs.next())
   {
      final double integer = rs.getDouble(1);
      final double fixed = rs.getDouble(2);
      final double floating = rs.getDouble(3);
      out.println("Integer NUMBER:  " + integer);
      out.println("Fixed NUMBER:    " + fixed);
      out.println("Floating NUMBER: " + floating);
   }
   out.println("\n");
}
catch (SQLException sqlEx)
{
   err.println("Unable to query data - " + sqlEx);
}

The output of running the above Java insertion and querying code against the Oracle and PostgreSQL databases respectively is shown in the next two screen snapshots.

Comparing Math.PI to Oracle's NUMBER Columns

Comparing Math.PI to PostgreSQL's numeric Columns

The simple examples using Java and Oracle and PostgreSQL demonstrate issues that might arise when specifying precision and scale on the Oracle NUMBER and PostgreSQL numeric column types. Although there are situations when fixed-point numbers are desirable, it is important to recognize that Java does not have a fixed-point primitive data type and use BigDecimal or a fixed-point Java library (such as decimal4j or Java Math Fixed Point Library) to appropriately deal with the fixed-point numbers retrieved from database columns expressed as fixed points. In the examples demonstrated in this post, nothing is really "wrong", but it is important to recognize the distinction between fixed-point numbers in the database and floating-point numbers in Java because arithmetic that brings the two together may not have the results one would expect.

In Java and other programming languages, one needs to not only be concerned about the effect of arithmetic operations and available precision on the "correctness" of floating-point numbers. The developer also needs to be aware of how these numbers are stored in relational database columns in the Oracle and PostgreSQL databases to understand how precision and scale designations on those columns can affect the representation of the stored floating-point number. This is especially applicable if the representations queried from the database are to be used in floating-point calculations. This is another (of many) examples where it is important for the Java developer to understand the database schema being used.

SPOOLing Queries with Results in psql

SQL*Plus, the Oracle database's command-line tool, provides the SPOOL command to "store query results in a file." The next screen snapshot shows SPOOL used in SQL*Plus to spool the listing of user tables to a file called C:\pdf\output.txt.

Both the executed query and the results of the query have been spooled to the file output.txt as shown in the next listing of that file.

Oracle's SQL*Plus's SPOOL-ed output.txt

SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
REGIONS                                                                         
LOCATIONS                                                                       
DEPARTMENTS                                                                     
JOBS                                                                            
EMPLOYEES                                                                       
JOB_HISTORY                                                                     
PEOPLE                                                                          
NUMERAL                                                                         
NUMBER_EXAMPLE                                                                  
COUNTRIES                                                                       

10 rows selected.

SQL> spool off

PostgreSQL's command-line tool, psql, provides functionality similar to SQL*Plus's SPOOL with the \o (\out) meta-command. The following screen snapshot shows this in action in psql.

The file output.txt written via psql's \o meta-command is shown in the next listing.

         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | albums | table | postgres
(1 row)

Only the results of the query run in psql are contained in the generated output.txt file. The query itself, even the longer query produced by using \set ECHO_HIDDEN on, is not contained in the output.

One approach to ensuring that the query itself is output with the query's results written to the file is to use the \qecho meta-command to explicitly write the query to the spooled file before running the query. This is demonstrated in the next screen snapshot.

Using \qecho in conjunction with \o does place the query itself in the written file with the query's results as shown in the next listed output.

select * from albums;
           title           |     artist      | year 
---------------------------+-----------------+------
 Back in Black             | AC/DC           | 1980
 Slippery When Wet         | Bon Jovi        | 1986
 Third Stage               | Boston          | 1986
 Hysteria                  | Def Leppard     | 1987
 Some Great Reward         | Depeche Mode    | 1984
 Violator                  | Depeche Mode    | 1990
 Brothers in Arms          | Dire Straits    | 1985
 Rio                       | Duran Duran     | 1982
 Hotel California          | Eagles          | 1976
 Rumours                   | Fleetwood Mac   | 1977
 Kick                      | INXS            | 1987
 Appetite for Destruction  | Guns N' Roses   | 1987
 Thriller                  | Michael Jackson | 1982
 Welcome to the Real World | Mr. Mister      | 1985
 Never Mind                | Nirvana         | 1991
 Please                    | Pet Shop Boys   | 1986
 The Dark Side of the Moon | Pink Floyd      | 1973
 Look Sharp!               | Roxette         | 1988
 Songs from the Big Chair  | Tears for Fears | 1985
 Synchronicity             | The Police      | 1983
 Into the Gap              | Thompson Twins  | 1984
 The Joshua Tree           | U2              | 1987
 1984                      | Van Halen       | 1984
(23 rows)

The main downside to use of \qecho is that it must be used before every statement to be written to the output file.

The psql variable ECHO can be set to queries to have "all SQL commands sent to the server [sent] to standard output as well." This is demonstrated in the next screen snapshot.

Unfortunately, although setting the psql variable ECHO to queries leads to the query being output along with the results in the psql window, the query is not written to the file by the \o meta-command. Instead, when \o is used with ECHO set to queries, the query itself is printed out again to the window and the results only are written to the specified file. This is because, as the documentation states (I added the emphasis), the \o meta-command writes "the query output ... to the standard output." This is demonstrated in the next screen snapshot.

I have not been able to figure out a way to easily use the \o meta-data command and have both the query and its results written to the file without needing to use \qecho. However, another approach that doesn't require \qecho is to run not try to spool the file output from within psql interactively, but to instead execute a SQL script input file externally.

For example, if I make an input file called input.sql that consisted only of a single line with query

  select * from albums;

I could run psql with the command

  psql -U postgres --echo-queries < input.txt > outputWithQuery.txt

to read that single-line file with the query and write output to the outputWithQuery.txt file. The --echo-queries option works like the \set ECHO queries from within psql and running this command successfully generates the prescribed output file with query and results. The following screen snapshot and the code listing following that demonstrate this.

outputWithQuery.txt

select * from albums;
           title           |     artist      | year 
---------------------------+-----------------+------
 Back in Black             | AC/DC           | 1980
 Slippery When Wet         | Bon Jovi        | 1986
 Third Stage               | Boston          | 1986
 Hysteria                  | Def Leppard     | 1987
 Some Great Reward         | Depeche Mode    | 1984
 Violator                  | Depeche Mode    | 1990
 Brothers in Arms          | Dire Straits    | 1985
 Rio                       | Duran Duran     | 1982
 Hotel California          | Eagles          | 1976
 Rumours                   | Fleetwood Mac   | 1977
 Kick                      | INXS            | 1987
 Appetite for Destruction  | Guns N' Roses   | 1987
 Thriller                  | Michael Jackson | 1982
 Welcome to the Real World | Mr. Mister      | 1985
 Never Mind                | Nirvana         | 1991
 Please                    | Pet Shop Boys   | 1986
 The Dark Side of the Moon | Pink Floyd      | 1973
 Look Sharp!               | Roxette         | 1988
 Songs from the Big Chair  | Tears for Fears | 1985
 Synchronicity             | The Police      | 1983
 Into the Gap              | Thompson Twins  | 1984
 The Joshua Tree           | U2              | 1987
 1984                      | Van Halen       | 1984
(23 rows)

I don't know how to exactly imitate SQL*Plus's writing of the query with its results from within SQL*Plus in psql without needing to add \qecho meta-commands, but passing the input script to psql with the --echo-queries option works very similarly to invoking and spooling the script from within SQL*Plus.

Does PostgreSQL Have an ORA-01795-like Limit?

The Oracle database requires that no more than 1000 entries be used in a SQL IN portion of a WHERE clause and will throw an ORA-01795 error if that number is exceeded. If a value needs to be compared to more than 1000 values, approaches other than use of IN must be applied. I wondered if this limitation applies to PostgreSQL and decided to write a simple application to find out.

For my simple test application, I wanted a very simple table to use with both an Oracle database and a PostgreSQL database.

Oracle: Creating Single Column Table And Inserting Single Row

CREATE TABLE numeral(numeral1 number);
INSERT INTO numeral (numeral1) VALUES (15);

PostgreSQL: Creating Single Column Table and Inserting Single Row

CREATE TABLE numeral(numeral1 numeric);
INSERT INTO numeral (numeral1) VALUES (15);

Building the SQL Query

Java 8 makes it to build up a query to test the condition of more than 1000 values in an IN clause. The next code snippet focuses on how this can be accomplished easily.

Java 8 Construction of SQL Query

final String queryPrefix = "SELECT numeral1 FROM numeral WHERE numeral1 IN ";
final String inClauseTarget =
   IntStream.range(1, numberOfInValues+1).boxed().map(String::valueOf).collect(Collectors.joining(",", "(", ")"));
final String select = queryPrefix + inClauseTarget;

The string constructed by the Java 8 code shown in the last code listing looks like this:

SELECT numeral1 FROM numeral WHERE numeral1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001)

Running the Query

When the above SQL query statement is executed against an Oracle database, the ORA-01795 error is manifest:

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

The PostgreSQL database does not have this same limitation as shown by its output below:

15

The full Java class I used to demonstrate the above findings in available at https://github.com/dustinmarx/databasedemos/blob/master/dustin/examples/inparameters/Main.java.

Conclusion

There are numerous ways to avoid the ORA-01795 error when using an Oracle database. However, I was curious if the same limitation existed for PostgreSQL and apparently it doesn't (I'm using PostgreSQL 9.4.4 in these examples). In fact, when I tried as many as one million IN values, PostgreSQL was still able to process the query, albeit noticeably slower than with a smaller number of IN values.

Single Quotes in Oracle Database Index Column Specification

In my previous post, I mentioned that a downside of using double quotes to explicitly specify case in Oracle identifiers is the potential for being confused with the use of single quotes for string literals. Although I don't personally think this is sufficient reason to avoid use of double quotes for identifiers in Oracle, it is worth being aware of this potential confusion. When to use single quotes versus when to use double quotes has been a source of confusion for users new to databases that distinguish between the two for some time. In this post, I look at an example of how accidental misuse of single quote where no quote is more appropriate can lead to the creation of an unnecessary index.

The SQL in the simple script createPersonTable.sql generates a table called PEOPLE and an index will be implicitly created for this table's primary key ID column. However, the script also contains an explicit index creation statement that, at first sight, might appear to also create an index on this primary key column.

createPersonTable.sql

CREATE TABLE people
(
   id number PRIMARY KEY,
   last_name varchar2(100),
   first_name varchar2(100)
);

CREATE INDEX people_pk_index ON people('id');

We might expect the statement that appears to explicitly create the primary key column index to fail because that column is already indexed. As the output below shows, it does not fail.

When a query is run against the indexes, it becomes apparent why the explicit index creation did not fail. It did not fail because it was not creating another index on the same column. The single quotes around what appears to be the "id" column name actually make that 'id' a string literal rather than a column name and the index that is created is a function-based index rather than a column index. This is shown in the query contained in the next screen snapshot.

The index with name PEOPLE_PK_INDEX was the one explicitly created in the script and is a function-based index. The implicitly created primary key column index has a system-generated name. In this example, the function-based index is a useless index that provides no value.

It's interesting to see what happens when I attempt to explicitly create the index on the column by using double quotes with "id" and "ID". The first, "id", fails ("invalid identifier") because Oracle case folds the name 'id' in the table creation to uppercase 'ID' implicitly. The second, "ID", fails ("such column list already indexed") because, in this attempt, I finally am trying to create an index on the same column for which an index was already implicitly created.

In my original example, the passing of a literal string as the "column" to the index creation statement resulted in it being created as a useless function-based index. It could have been worse if my intended primary key column index hadn't already been implicitly created because then I might not have the index I thought I had. This, of course, could happen when creating an index for a column or list of columns that won't have indexes created for them implicitly. There is no error message to warn us that the single-quoted string is being treated as a string literal rather than as a column name.

Conclusion

The general rule of thumb to remember when working with quotation marks in Oracle database is that double quotes are for identifiers (such as column names and table names) and single quotes are for string literals. As this post has demonstrated, there are times when one may be misused in place of the other and lead to unexpected results without necessarily displaying an error message.

Downsides of Mixed Identifiers When Porting Between Oracle and PostgreSQL Databases

Both the Oracle database and the PostgreSQL database use the presence or absence of double quotes to indicate case sensitive or case insensitive identifiers. Each of these databases allows identifiers to be named without quotes (generally case insensitive) or with double quotes (case sensitive). This blog post discusses some of the potential negative consequences of mixing quoted (or delimited) identifiers and case-insenstive identifiers in an Oracle or PostgreSQL database and then trying to port SQL to the other database.

Advantages of Case-Sensitive Quoted/Delimiter Identifiers

There are multiple advantages of case sensitive identifiers. Some of the advertised (real and perceived) benefits of case sensitive database identifiers include:

• Ability to use reserved words, key words, and special symbols not available to identifiers without quotes.
  • PostgreSQL's keywords:
    • reserved ("only real key words" that "are never allowed as identifiers")
    • unreserved ("special meaning in particular contexts," but "can be used as identifiers in other contexts").
    • "Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands."
  • Oracle reserved words and keywords:
    • Oracle SQL Reserved Words that can only be used as "quoted identifiers, although this is not recommended."
    • Oracle SQL Keywords "are not reserved," but using these keywords as names can lead to "SQL statements [that] may be more difficult to read and may lead to unpredictable results."
    • "Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character."
    • "Quoted identifiers can contain any characters and punctuations marks as well as spaces."
• Ability to use the same characters for two different identifiers with case being the differentiation feature.
• Avoid dependency on a database's implementation's case assumptions and provide "one universal version."
• Explicit case specification avoids issues with case assumptions that might be changeable in some databases such as SQL Server.
• Consistency with most programming languages and operating systems' file systems.
• Specified in SQL specification and explicitly spells out case of identifiers rather than relying on specific implementation details (case folding) of particular database.
• Additional protection in cases where external users are allowed to specify SQL that is to be interpreted as identifiers.

Advantages of Case-Insensitive Identifiers

There are also advantages associated with use of case-insensitive identifiers. It can be argued that case-insensitive identifiers are the "default" in Oracle database and PostgreSQL database because one must use quotes to specify when this default case-insensitivity is not the case.

• Case-insensitivity is the "default" in Oracle and PostgreSQL databases.
• The best case for readability can be used in any particular context. For example, allows DML and DDL statements to be written to a particular coding convention and then be automatically mapped to the appropriate case folding for various databases.
• Avoids errors introduced by developers who are unaware of or unwilling to follow case conventions.
• Double quotes (" ") are very different from single quotes (' ') in at least some contexts in both the Oracle and PostgreSQL databases and not using case-sensitive identifier double quotes eliminates need to remember the difference or worry about the next developer not remembering the difference.
  • Oracle database single quotes: "Text, character, and string literals are always surrounded by single quotation marks."
  • Oracle database double quotes: "A quoted identifier begins and ends with double quotation marks (")."
  • PostgreSQL single quotes: Use single quotes in PostgreSQL for "literals"/"string values".
  • PostgreSQL double quotes: "delimited identifier or quoted identifier ... is formed by enclosing an arbitrary sequence of characters in double-quotes (")."
• Many of the above listed "advantages" may not really be good practices:
  • Using reserved words and keywords as identifiers is probably not good for readability anyway.
  • Using symbols allowed in quoted identifiers that are not allowed in unquoted identifiers may not be necessary or even desirable.
  • Having two different variables of the same name with just different characters cases is probably not a good idea.

Default Case-Insensitive or Quoted Case-Sensitive Identifiers?

In Don’t use double quotes in PostgreSQL, Reuven Lerner makes a case for using PostgreSQL's "default" (no double quotes) case-insensitive identifiers. Lerner also points out that pgAdmin implicitly creates double-quoted case-sensitive identifiers. From an Oracle DBA perspective, @MBigglesworth79 calls quoted identifiers in Oracle an Oracle Gotcha and concludes, "My personal recommendation would be against the use of quoted identifiers as they appear to cause more problems and confusion than they are worth."

A key trade-off to be considered when debating quoted case-sensitive identifiers versus default case-insensitive identifiers is one of being able to (but also required to) explicitly specify identifiers' case versus not being able to (but not having to) specify case of characters used in the identifiers.

Choose One or the Other: Don't Mix Them!

It has been my experience that the worst choice one can make when designing database constructs is to mix case-sensitive and case-insensitive identifiers. Mixing of these make it difficult for developers to know when case matters and when it doesn't, but developers must be aware of the differences in order to use them appropriately. Mixing identifiers with implicit case and explicit case definitely violates the Principle of Least Surprise and will almost certainly result in a frustrating runtime bug.

Another factor to consider in this discussion is case folding choices implemented in Oracle database and PostgreSQL database. This case folding can cause unintentional consequences, especially when porting between two databases with different case folding assumptions. The PostgreSQL database folds to lowercase characters (non-standard) while the Oracle database folds to uppercase characters. This significance of this difference is exemplified in one of the first PostgreSQL Wiki "Oracle Compatibility Tasks": "Quoted identifiers, upper vs. lower case folding." Indeed, while I have found PostgreSQL to be heavily focused on being standards-compliant, this case folding behavior is one place that is very non-standard and cannot be easily changed.

About the only "safe" strategy to mix case-sensitive and case-insensitive identifiers in the same database is to know that particular database's default case folding strategy and to name even explicitly named (double quoted) identifiers with exactly the same case as the database will case fold non-quoted identifiers. For example, in PostgreSQL, one could name all identifiers in quotes with completely lowercase characters because PostgreSQL will default unquoted identifiers to all lowercase characters. However, when using Oracle, the opposite approach would be needed: all quoted identifiers should be all uppercase to allow case-sensitive and case-insensitive identifiers to be intermixed. Problems will arise, of course, when one attempts to port from one of these databases to the other because the assumption of lowercase or uppercase changes. The better approach, then, for database portability between Oracle and PostgreSQL databases is to commit either to using quoted case-sensitive identifiers everywhere (they are then explicitly named the same for both databases) or to use default case-insensitive identifiers everywhere (and each database will appropriately case fold appropriately in its own approach).

Conclusion

There are advantages to both identifiers with implicit case (case insensitive) and to identifiers with explicit (quoted and case sensitive) case in both Oracle database and PostgreSQL database with room for personal preferences and tastes to influence any decision on which approach to use. Although I prefer (at least at the time of this writing) to use the implicit (default) case-insensitive approach, I would rather use the explicitly spelled-out (with double quotes) identifier cases in all cases than mix the approach and use explicit case specification for identifiers in some cases and implicit specification of case of identifiers in other cases. Mixing the approaches makes it difficult to know which is being used in each table and column in the database and makes it more difficult to port the SQL code between databases such as PostgreSQL and Oracle that make different assumptions regarding case folding.

Additional Reading

• Database identifiers, quoting and case sensitivity
• Oracle Database 11g (11.1.1) Database SQL Language Reference: Schema Object Names and Qualifiers
• PostgreSQL 9.4.4 Documentation: Identifiers and Keywords
• Are there benefits to a case sensitive database?
• Reason why oracle is case sensitive?

Java Minor Releases Scheme Tweaked Again

In 2013, Oracle announced the Java SE - Change in Version Numbering Scheme. The announcement stated that Limited Update releases (those "that include new functionality and non-security fixes") and Critical Patch Updates (CPUs) [those "that only include fixes for security vulnerabilities"] would be released with specific version number schemes. In particular, Limited Use Releases would have version numbers with multiples of 20 while Critical Patch Updates would have version numbers that are multiples of 5 and come after the latest Limited Use Release version number. The purpose of this scheme change was to allow room for versions with numbers between these, which allows Oracle "to insert releases – for example security alerts or support releases, should that become necessary - without having to renumber later releases."

Yesterday's announcement ("Java CPU and PSU Releases Explained") states, "Starting with the release of Java SE 7 Update 71 (Java SE 7u71) in October 2014, Oracle will release a Critical Patch Update (CPU) at the same time as a corresponding Patch Set Update (PSU) for Java SE 7." This announcement explains the difference between a CPU and a PSU:

Critical Patch Update	CPU	"Fixes to security vulnerabilities and critical bug fixes."	Minimum recommended for everyone.
Patch Set Update	PSU	"All fixes in the corresponding CPU" and "additional non-critical fixes."	Recommended only for those needing bugs fixed by PSU additional fixes.

Yesterday's announcement states that PSU releases (which are really CPU+ releases) will be released along with their corresponding CPU releases. Because the additional fixes that a PSU release contains beyond what's in the CPU release are expected to be part of the next CPU release, developers are encouraged to experiment with PSU releases to ensure that coming CPU features work well for them.

Groovy Sql and Oracle 'Invalid column index' SQLException

There are some minor potential dangers associated with Groovy's def keyword. The Groovy style and language feature guidelines for Java developers provides some warnings about use of def. In this blog post, I demonstrate an advantage of being more explicit in typing when using Groovy SQL with an Oracle database to avoid a potential "Invalid column index" SQLException because I've run into this issue a few times.

The following Groovy script provides comments on Oracle database tables matching a provided search string. In this case, what the script does is not as important as look at the code that defines the SQL query string (lines 18-21).

searchDbComments.groovy (using def without String typing or as String)

#!/usr/bin/env groovy
// searchDbComments.groovy

this.class.classLoader.rootLoader.addURL(
   new URL("file:///C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar"))

if (args.length < 1)
{
   println "USAGE: searchDbComments.groovy <searchString>"
   System.exit(-1)
}

def searchString = args[0].toUpperCase()

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr",
                          "oracle.jdbc.pool.OracleDataSource")
def dbTableCommentsQry = """
SELECT table_name, table_type, comments
  FROM user_tab_comments
 WHERE UPPER(comments) LIKE '%${searchString}%'"""

sql.eachRow(dbTableCommentsQry)
{
   println "${it.table_name} (${it.table_type}): ${it.comments}"
}

When the above code is executed, the following error is generated:

WARNING: Failed to execute: 
SELECT table_name, table_type, comments
  FROM user_tab_comments
 WHERE UPPER(comments) LIKE '%?%' because: Invalid column index

Caught: java.sql.SQLException: Invalid column index

java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5303)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8323)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8259)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9012)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8993)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
    at searchDbComments.run(searchDbComments.groovy:23)

Addressing the "invalid column index" SQLException is easy. One solution is to change the "def" on lines 18-21 to an explicit "String" type. Another solution, shown in the next code listing, is to use Groovy's "as" coercion keyword to explicitly allow the "def" to be used and have the dbTableCommentsQry variable be typed as a String.

searchDbComments.groovy (using as String)

#!/usr/bin/env groovy
// searchDbComments.groovy

this.class.classLoader.rootLoader.addURL(
   new URL("file:///C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar"))

if (args.length < 1)
{
   println "USAGE: searchDbComments.groovy <searchString>"
   System.exit(-1)
}

def searchString = args[0].toUpperCase()

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr",
                          "oracle.jdbc.pool.OracleDataSource")
def dbTableCommentsQry = """
SELECT table_name, table_type, comments
  FROM user_tab_comments
 WHERE UPPER(comments) LIKE '%${searchString}%'""" as String

sql.eachRow(dbTableCommentsQry)
{
   println "${it.table_name} (${it.table_type}): ${it.comments}"
}

Using "def" only or no "def" with no type at all leads to the above error. Explicitly defining the String variable used in the query either via static typing or via use of "as" keyword allows the code to execute properly. One could use a static typing with "def", but that is thought to be redundant.

There is nothing necessarily wrong about using "def," but one does need to be careful with its application. Guillaume Laforge has written that "def is fine in method bodies or for particular dynamic aspects, but for everything that is a 'contract' (method signatures, properties, etc), it's better to use explicit types."

Miscellaneous Musings about RMOUG Training Days 2013

I was only able to attend a portion of Rocky Mountain Oracle Users Group (RMOUG) Training Days 2013, but this was my 13th year to attend all or part of this conference (and my 11th year presenting). I have always enjoyed meeting and talking to the people who attend even as my interests have diverged somewhat from the database-focus that formerly aligned so well for me with the conference. As I have posted on before, this year's conference had a lot of focus on mobile application development in addition to the normal breadth and depth of database-oriented topics. In this post, I look at some of the excellent questions (and my responses) I was asked at my presentation on JavaFX and Groovy and look at some other things I learned while attending.

JavaFX and Groovy

I started my session at 2:45 pm with about 10 people in attendance, but the audience reached about 25 people in size by the end. It was another good RMOUG Training Days audience with good questions that made me think they were understanding what I was trying to convey. I'll address some of those questions with my responses now.

One audience member asked how it worked in the JavaFX example that the overridden start method was never called from the static main function of the application. I explained that the same application class extends javafx.application.Application and, thanks to that inheritance, the start(Stage) method is invoked automatically as part of the JavaFX application lifecycle. That start(Stage) method is declared abstract in Application so concrete child implementations must inherit it. Only a call to one of the Application.launch methods was seen in my example, but the start(Stage) method overridden in the child class gets called via polymorphism during the application's execution.

Another question asked in my presentation had to deal with Oracle's use of JavaFX in their own tooling options such as JDeveloper, Oracle ADF, and the like. I obviously do not know the extent of this, but I was happy to speculate that Oracle would like to use JavaFX in more internal tools to leverage their investment in it. One attendee wondered if some of the Oracle ADF-based mobile applications shown in other sessions of this conference used JavaFX underneath. I don't know enough about that to confirm that.

Although I did not demonstrate it in my presentation, I did mention the availability of SceneBuilder. I also mentioned that while other IDEs such as JDeveloper can support JavaFX, NetBeans appears to be the current leader in JavaFX support.

One or two questions surrounded JavaFX's place in the competitive landscape. I attempted to contrast it with Flex, Silverlight, HTML5, Swing, other languages' graphics libraries, and other native mobile development platforms. In some ways, the determination of what JavaFX is competing against is based on how one wants to use it. In desktop applications, the most common "competitors" are pure Swing (without JavaFX), pure SWT (without JavaFX), Adobe AIR, and other languages' graphical libraries. In web application, obvious "competitors" are HTML5/jQuery and Flex. On mobile applications, obvious competitors include the web stack as well as native languages such as Objective-C for the iOS devices. It could be argued that it's a good thing that JavaFX has or soon will have so many different platforms to compete on, giving JavaFX developers flexibility to apply their skills easily to multiple platforms.

I talked about GroovyFX's support for concurrency as provided in the javafx.concurrent package and I referenced the Concurrency in JavaFX article. An audience member asked about Groovy's support for concurrency. I responded to this by briefly discussing gpars and explaining that gpars (Groovy Parallel Systems) is now bundled with Groovy (since Groovy 1.8).

Additional questions were asked that were, I thought, insightful, but I am not talented enough to write them down and respond to them at the same time and have now temporarily forgotten some of the others.

Before leaving coverage of my own presentation, I want to include two slides that I showed in the presentation and in a "slide show" that I had running while waiting for the start time to arrive. These slides summarize some key moments in the history of JavaFX and history of Groovy.

Thinking back upon the history of JavaFX and Groovy, it is easy to see some similarities between the two. Both started with significant enthusiasm that then seemed to wane for a while before resurgent interest and coming back stronger than ever. It seems to me that SpringSource has been a pivotal player in providing stability to Groovy and making it more popular than ever and Oracle has done the same for JavaFX (deprecating JavaFX Script and embracing standard Java APIs was bold but well played).

Oracle Fusion Application Development

I was able to attend Ann Horton's presentation "Web Development Techniques from an Oracle Fusion Applications Developer." Ann's presentation was filled with screen snapshots, making it easy to see how to use the graphical-based tools to build Oracle Fusion Applications and use Oracle Fusion Middleware. I don't have any experience with Oracle Applications, but a lot of people do. Although I probably won't have the opportunity to work with them anytime soon, I like to see what other technologies are out there and enjoyed seeing a different way of developing applications. As is the case with much of the software development that occurs, Oracle provides tools that make much of the creation drag-and-drop and selecting things with the mouse.

Ann described "Oracle Fusion Applications Suite" as the "next generation of Oracle Applications" and added that "Fusion" implies integration of multiple products "under a common umbrella and one look and feel." Ann showed via numerous screen captures (often annotated with arrows, underlines, or other markings to provide focus) how to build up an application using Oracle Fusion Applications Suite.

One of the things I like to do at a technical conference is find out what others are using for tooling. Ann mentioned that thousands of developers have worked on Oracle Fusion Applications Suite and that they have used tools such as ade (Application Development Environment), JUnit, Selenium, OATS, and JAudit in their work. I thought it was interesting that JSP and JSF fragments (.jspx and .jsff files) were shown in the presentation.

Ann talked about Fusion developers using and providing "Fusion Guidelines, Standards, and Patterns" (GPS). More details about the Oracle Fusion Applications can be found in Oracle Fusion Applications Developer's Guide.

Oracle ADF and Mobile Development

Until I read the abstracts for this conference, I had not even realized that Oracle ADF can be used to develop applications for iOS and Android devices, but the subject of Oracle ADF Mobile was a popular one at the conference. The main page for Oracle ADF Mobile describes it like this:

Oracle ADF Mobile is an HTML5 and Java mobile development framework that enables developers to build and extend enterprise applications for iOS and Android from a single code base. Based on a hybrid mobile architecture, ADF Mobile supports access to native device services, enables offline applications and protects enterprise investments from future technology shifts.

The Oracle ADF Mobile FAQ states that Oracle ADF Mobile is licensed "as part of the Oracle Application Development Framework (ADF)" and adds that "Oracle ADF can be licensed either as 'Oracle Application Development Framework and TopLink' item on the technology price list, or as part of the Oracle WebLogic licenses." The FAQ also addresses device support: "Both iOS (5.x and above) and Android (2.3.x and above) devices are supported. Furthermore, both the tablet and smart phones running these mobile operating systems are supported."

One of the audience members asked me how using JavaFX differed from using Oracle ADF Mobile and licensing is obviously one of the major differences.

Other Blog Posts on RMOUG Training Days 2013

• Galo Balda's Blog: RMOUG Training Days 2013
• Oracle SQL Tuning Tools and Tips: RMOUG Training Days February 11 (day 1)
• RMOUG Training Days February 13 (day 3)
• Oracle Data Warrior: RMOUG Training Days 2013 – OTN Lab Day
• Oracle Data Warrior: RMOUG Training Days 2013 – Day 1
• Oracle Data Warrior: RMOUG Training Days 2013 – Day 2
• Martin Widlake's Yet Another Oracle Blog: RMOUG – Here I Come
• RMOUG Training Days 2013 - Denver Colorado
• Arup Nanda: My Sessions in RMOUG 2013

Conclusion

I would have liked to attend more sessions at RMOUG Training Days 2013, but enjoyed the brief time I was able to spend there this year. It was good to see people I've known for a number of years and to meet new people. It is always good to remember that there is much more to software development than the relatively narrow view one can get when working in the same circles and communities all the time.

Scripted Reports with Groovy

Groovy has become my favorite scripting language and in this blog I look at some of Groovy's features that make it particularly attractive for presenting text-based reports. The post will show how custom text-based reports of data stored in the database can be easily presented with Groovy. I will highlight several attractive features of Groovy along the way.

I use the Oracle Database 11g Express Edition (XE) for the data source in my example in this post, but any data source could be used. This example does make use of Groovy's excellent SQL/JDBC support and uses the Oracle sample schema (HR). A visual depiction of that sample schema is available in the sample schema documentation.

My example of using Groovy to write a reporting script involves retrieving data from the Oracle HR sample schema and presenting that data via a text-based report. One portion of the script needs to acquire this data from the database and Groovy adds only minimal ceremony to the SQL statement needed to do this. The following code snippet from the script shows use of Groovy's multi-line GString to specify the SQL query string in a user-friendly format and to process the results of that query.

def employeeQueryStr =
"""SELECT e.employee_id, e.first_name, e.last_name,
          e.email, e.phone_number,
          e.hire_date, e.job_id, j.job_title,
          e.salary, e.commission_pct, e.manager_id,
          e.department_id, d.department_name,
          m.first_name AS mgr_first_name, m.last_name AS mgr_last_name
     FROM employees e, departments d, jobs j, employees m
    WHERE e.department_id = d.department_id
      AND e.job_id = j.job_id
      AND e.manager_id = m.employee_id(+)"""

def employees = new TreeMap<Long, Employee>()
import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr",
                          "oracle.jdbc.pool.OracleDataSource")
sql.eachRow(employeeQueryStr)
{
   def employeeId = it.employee_id as Long
   def employee = new Employee(employeeId, it.first_name, it.last_name,
                               it.email, it.phone_number,
                               it.hire_date, it.job_id, it.job_title,
                               it.salary, it.commission_pct, it.manager_id as Long,
                               it.department_id as Long, it.department_name,
                               it.mgr_first_name, it.mgr_last_name)
   employees.put(employeeId, employee)
}

The Groovy code above only adds a small amount of code on top of the Oracle SQL statement. The specified SELECT statement joins multiple tables and includes an outer join as well (outer join needed to include the President in the query results despite that position not having a manager). The vast majority of the first part of the code is the SQL statement that could be run as-is in SQL*Plus or SQL Developer. No need for verbose exception catching and result set handling with Groovy's SQL support!

There are more Groovy-specific advantages to point out in the code snippet above. Note that the import statement to import groovy.sql.Sql was allowed when needed and did not need to be at the top of the script file. The example also used Sql.newInstance and Sql.eachRow(GString,Closure). The latter method allows for easy application of a closure to the results of the query. The it special word is the default name for items being processed in the closure. In this case,it can be thought of a a row in the result set. Values in each row are accessed by the underlying database columns' names (or aliases in the case of mgr_first_name and mgr_last_name).

One of the advantages of Groovy is its seamless integration with Java. The above code snippet also demonstrated this via Groovy's use of TreeMap, which is advantageous because it means that the new Employee instances placed in the map based on data retrieved from the database will always be available in order of employee ID.

In the code above, the information retrieved from the database and processed via the closure is stored for each row in a newly instantiated Employee object. This Employee object provides another place to show off Groovy's brevity and is shown next.

Employee.groovy

@groovy.transform.Canonical
class Employee
{
   Long employeeId
   String firstName
   String lastName
   String emailAddress
   String phone_number
   Date hireDate
   String jobId
   String jobTitle
   BigDecimal salary
   BigDecimal commissionPercentage
   Long managerId
   Long departmentId
   String departmentName
   String managerFirstName
   String managerLastName
}

The code listing just shown is the entire class! Groovy's property supports makes getter/setter methods automatically available for all the defined class attributes. As I discussed in a previous blog post, the @Canonical annotation is a Groovy AST (transformation) that automatically creates several useful common methods for this class [equals(Object), hashCode(), and toString()]. There is no explicit constructor because @Canonical also handles this, providing a constructor that accepts that class's arguments in the order they are specified in their declarations. It is difficult to image a scenario in which it would be easier to easily and quickly create an object to store retrieved data values in a script.

A JDBC driver is needed for this script to retrieve this data from the Oracle Database XE and the JAR for that driver could be specified on the classpath when running the Groovy script. However, I like my scripts to be as self-contained as possible and this makes Groovy's classpath root loading mechanism attractive. This can be used within this script (rather than specifying it externally when invoking the script) as shown next:

this.class.classLoader.rootLoader.addURL(
   new URL("file:///C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar"))

Side Note: Another nifty approach for accessing the appropriate dependent JAR or library is use of Groovy's Grape-provided @Grab annotation. I didn't use that here because Oracle's JDBC JAR is not available in any legitimate Maven central repositories that I am aware of. An example of using this approach when a dependency is available in the Maven public repository is shown in my blog post Easy Groovy Logger Injection and Log Guarding.

With the data retrieved from the database and placed in a collection of simple Groovy objects built for holding this data and providing easy access to it, it is almost time to start presenting this data in a text report. Some constants defined in the script are shown in the next excerpt from the script code.

int TOTAL_WIDTH = 120
String HEADER_ROW_SEPARATOR = "=".multiply(TOTAL_WIDTH)
String ROW_SEPARATOR = "-".multiply(TOTAL_WIDTH)
String COLUMN_SEPARATOR = "|"
int COLUMN_SEPARATOR_SIZE = COLUMN_SEPARATOR.size()
int COLUMN_WIDTH = 22
int TOTAL_NUM_COLUMNS = 5
int BALANCE_COLUMN_WIDTH = TOTAL_WIDTH-(TOTAL_NUM_COLUMNS-1)*COLUMN_WIDTH-COLUMN_SEPARATOR_SIZE*(TOTAL_NUM_COLUMNS-1)-2

The declaration of constants just shown exemplify more advantages of Groovy. For one, the constants are statically typed, demonstrating Groovy's flexibility to specifying types statically as well as dynamically. Another feature of Groovy worth special note in the last code snippet is the use of the String.multiply(Number) method on the literal Strings. Everything, even Strings and numerics, are objects in Groovy. The multiply method makes it easy to create a String of that number of the same repeating character.

The first part of the text output is the header. The following lines of the Groovy script write this header information to standard output.

println "\n\n${HEADER_ROW_SEPARATOR}"
println "${COLUMN_SEPARATOR}${'HR SCHEMA EMPLOYEES'.center(TOTAL_WIDTH-2*COLUMN_SEPARATOR_SIZE)}${COLUMN_SEPARATOR}"
println HEADER_ROW_SEPARATOR
print "${COLUMN_SEPARATOR}${'EMPLOYEE ID/HIRE DATE'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
print "${'EMPLOYEE NAME'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
print "${'TITLE/DEPARTMENT'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
print "${'SALARY INFO'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
println "${'CONTACT INFO'.center(BALANCE_COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
println HEADER_ROW_SEPARATOR

The code above shows some more addictive features of Groovy. One of my favorite aspects of Groovy's GString support is the ability to use Ant-like ${} expressions to provide executable code inline with the String. The code above also shows off Groovy's GDK String's support for the center(Number) method that automatically centers the given String withing the specified number of characters. This is a powerful feature for easily writing attractive text output.

With the data retrieved and available in our data structure and with the constants defined, the output portion can begin. The next code snippet shows use of Groovy's standard collections each method to allow iteration over the previously populated TreeMap with a closure applied to each iteration.

employees.each
{ id, employee ->
   // first line in each output row
   def idStr = id as String
   print "${COLUMN_SEPARATOR}${idStr.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   def employeeName = employee.firstName + " " + employee.lastName
   print "${employeeName.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   def jobTitle = employee.jobTitle.replace("Vice President", "VP").replace("Assistant", "Asst").replace("Representative", "Rep")
   print "${jobTitle.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   def salary = '$' + (employee.salary as String)
   print "${salary.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   println "${employee.phone_number.center(BALANCE_COLUMN_WIDTH)}${COLUMN_SEPARATOR}"

   // second line in each output row
   print "${COLUMN_SEPARATOR}${employee.hireDate.getDateString().center(COLUMN_WIDTH)}"
   def managerName = employee.managerFirstName ? "Mgr: ${employee.managerFirstName[0]}. ${employee.managerLastName}" : "Answers to No One"
   print "${COLUMN_SEPARATOR}${managerName.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   print "${employee.departmentName.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   String commissionPercentage = employee.commissionPercentage ?: "No Commission"
   print "${commissionPercentage.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   println "${employee.emailAddress.center(BALANCE_COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   println ROW_SEPARATOR
}

The last code snippet is where the data retrieved from the database is output in a relatively attractive text format. The example shows how handles in a closure can be named to be more meaningful. In this case, they are named id and employee and represent the key (Long) and value (Employee) of each entry in the TreeMap.

There are other Groovy features in the last code snippet worth special mention. The presentation of commission uses Groovy's Elvis operator (?:), which makes even Java's conditional ternary look verbose. In this example, if the employee's commission percentage meets Groovy truth standards, that percentage is used; otherwise, "No Commission" is printed.

The handling of the hire date provides another opportunity to tout Groovy's GDK benefits. In this case, Groovy GDK Date.getDateString() is used to easily access the date-only portion of the Date class (time not desired for hire date) without explicit use of a String formatter. Nice!

The last code example also demonstrates use of the as keyword to coerce (cast) variables in a more readable way and also demonstrates more leverage of Java features, in this case taking advantage of Java String's replace(CharSequence, CharSequence) method. Groovy adds some more goodness to String again in this example, however. The example demonstrates Groovy's supporting extracting the first letter only of the manager's first name using subscript (array) notation ([0]) to get only the first character out of the string.

So far in this post, I've shown snippets of the overall script as I explained the various features of Groovy that are demonstrated in each snippet. The entire script is shown next and that code listing is followed by a screen snapshot of how the output appears when the script is executed. The complete code for the Groovy Employee class was shown previously.

generateReport.groovy: The Complete Script

#!/usr/bin/env groovy

// Add JDBC driver to classpath as part of this script's bootstrapping.
// See http://marxsoftware.blogspot.com/2011/02/groovy-scripts-master-their-own.html.
// WARNING: This location needs to be adjusted for specific user environment.
this.class.classLoader.rootLoader.addURL(
   new URL("file:///C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar"))


int TOTAL_WIDTH = 120
String HEADER_ROW_SEPARATOR = "=".multiply(TOTAL_WIDTH)
String ROW_SEPARATOR = "-".multiply(TOTAL_WIDTH)
String COLUMN_SEPARATOR = "|"
int COLUMN_SEPARATOR_SIZE = COLUMN_SEPARATOR.size()
int COLUMN_WIDTH = 22
int TOTAL_NUM_COLUMNS = 5
int BALANCE_COLUMN_WIDTH = TOTAL_WIDTH-(TOTAL_NUM_COLUMNS-1)*COLUMN_WIDTH-COLUMN_SEPARATOR_SIZE*(TOTAL_NUM_COLUMNS-1)-2



// Get instance of Groovy's Sql class
// See http://marxsoftware.blogspot.com/2009/05/groovysql-groovy-jdbc.html
import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr",
                          "oracle.jdbc.pool.OracleDataSource")

def employeeQueryStr =
"""SELECT e.employee_id, e.first_name, e.last_name,
          e.email, e.phone_number,
          e.hire_date, e.job_id, j.job_title,
          e.salary, e.commission_pct, e.manager_id,
          e.department_id, d.department_name,
          m.first_name AS mgr_first_name, m.last_name AS mgr_last_name
     FROM employees e, departments d, jobs j, employees m
    WHERE e.department_id = d.department_id
      AND e.job_id = j.job_id
      AND e.manager_id = m.employee_id(+)"""

def employees = new TreeMap<Long, Employee>()
sql.eachRow(employeeQueryStr)
{
   def employeeId = it.employee_id as Long
   def employee = new Employee(employeeId, it.first_name, it.last_name,
                               it.email, it.phone_number,
                               it.hire_date, it.job_id, it.job_title,
                               it.salary, it.commission_pct, it.manager_id as Long,
                               it.department_id as Long, it.department_name,
                               it.mgr_first_name, it.mgr_last_name)
   employees.put(employeeId, employee)
}

println "\n\n${HEADER_ROW_SEPARATOR}"
println "${COLUMN_SEPARATOR}${'HR SCHEMA EMPLOYEES'.center(TOTAL_WIDTH-2*COLUMN_SEPARATOR_SIZE)}${COLUMN_SEPARATOR}"
println HEADER_ROW_SEPARATOR
print "${COLUMN_SEPARATOR}${'EMPLOYEE ID/HIRE DATE'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
print "${'EMPLOYEE NAME'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
print "${'TITLE/DEPARTMENT'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
print "${'SALARY INFO'.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
println "${'CONTACT INFO'.center(BALANCE_COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
println HEADER_ROW_SEPARATOR

employees.each
{ id, employee ->
   // first line in each row
   def idStr = id as String
   print "${COLUMN_SEPARATOR}${idStr.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   def employeeName = employee.firstName + " " + employee.lastName
   print "${employeeName.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   def jobTitle = employee.jobTitle.replace("Vice President", "VP").replace("Assistant", "Asst").replace("Representative", "Rep")
   print "${jobTitle.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   def salary = '$' + (employee.salary as String)
   print "${salary.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   println "${employee.phone_number.center(BALANCE_COLUMN_WIDTH)}${COLUMN_SEPARATOR}"

   // second line in each row
   print "${COLUMN_SEPARATOR}${employee.hireDate.getDateString().center(COLUMN_WIDTH)}"
   def managerName = employee.managerFirstName ? "Mgr: ${employee.managerFirstName[0]}. ${employee.managerLastName}" : "Answers to No One"
   print "${COLUMN_SEPARATOR}${managerName.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   print "${employee.departmentName.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   String commissionPercentage = employee.commissionPercentage ?: "No Commission"
   print "${commissionPercentage.center(COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   println "${employee.emailAddress.center(BALANCE_COLUMN_WIDTH)}${COLUMN_SEPARATOR}"
   println ROW_SEPARATOR
}

In this blog post, I've attempted to show how Groovy provides numerous features and other syntax support that make it easier to write scripts for generating readable and relatively attractive output. For more general Groovy scripts that provide text output support, see Formatting simple tabular text data. Although these are nice general solutions, an objective of my post has been to show that it is easy and does not take much time to write customized scripts for generating custom text output with Groovy. Small Groovy-isms such as easily centering a String, easily converting a Date to a String, extracting any desired character from a string based on array position notation, and easily accessing database data make Groovy a powerful tool in generating text-based reports.