Oracle’s SQL*Plus is not as user-friendly as SQL Developer or JDeveloper for manipulating data in an Oracle database, but it is still commonly used. In fact, SQL*Plus is often preferred to the tools with fancier user interfaces for quick and dirty manipulation, for running SQL scripts, for being run as part of shell scripts, and for other non-interactive uses. Furthermore, Oracle’s SQL*Plus page calls SQL*Plus “the primary interface to the Oracle Database server.” In this blog post, I look at using SQL*Plus in conjunction with Groovy scripts.
Groovy’s Process Management plays the most significant role in the process of using Groovy with SQL*Plus. This functionality is easily applied thanks to the GDK’s String extension that includes an execute() method which returns a Process running the command contained within that GDK-extended String. This mechanism will be used repeatedly in the examples in this blog post.
Notes Related Specifically to Windows/Vista
Many books, blogs, and articles on using Groovy with Windows rightly point out that some of the commonly used commands in Windows are not actually executables, but are instead built-in commands. These commands must be prepended with cmd /C to be executed appropriately. However, this step is unnecessary when invoking SQL*Plus from Groovy on Windows because SQL*Plus is a separate executable.
When using Vista, it is important to run SQL*Plus with the appropriate privileges. Because the Groovy scripts in this post run SQL*Plus, the scripts should be executed in a console window that is being run under administrative privileges. An error message including the phrase “The requested operation requires elevation” is seen when the Groovy scripts using SQL*Plus are executed without the appropriate privileges. This is demonstrated in the next screen snapshot.
Example 1: Basic Query Statement
One of the easiest ways of using SQL*Plus is to write a file containing commands to be run within SQL*Plus. These files often end with the .sql suffix and are typically executed in SQL*Plus by prefacing the name of script file (with path if the file is not in the current working directory) with the @ symbol.
In this first example, that SQL*Plus script file is called 01-employeeIds.sql and is a single-line script:
01-employeeIds.sql
select employee_id from employees;
The above script is expected to be executed against the HR sample schema that is supplied with most modern versions of the Oracle database. The Groovy to run this SQL*Plus script file is contained in the next code listing for sql01.groovy:
sql01.groovy
#!/usr/bin/env groovydef sqlplusCmd = "sqlplus hr/hr @01-employeeIds.sql".execute()println "${sqlplusCmd.class}"sqlplusCmd.in.eachLine { line -> println line }
The above Groovy script specifies an explicit call to SQL*Plus with the ‘hr’ username and associated ‘hr’ password. It then runs the script showed above by prefacing its name with the “@” symbol. All of this is included in a GDK-extended String upon which the execute() method is invoked. The object returned from that call is a GDK-extended Process instance (as illustrated by the println call below its instantiation) from which each line is printed.
The initial results of running the above script are shown in the next screen snapshot:
In the above output, the first of many rows returned from the database are shown after the SQL*Plus banner is displayed and the line showing that the GDK String.execute() method returns a java.lang.ProcessImpl is displayed.
When run as shown above, this script never ends because the SQL*Plus session is never exited. This is easily addressed by adding the word “exit” to the end of the SQL script, which will be done in the next example. The next screen snapshot demonstrates how the script appears to “hang” when the SQL*Plus session has not been explicitly exited.
Example 2: Cleaning Up SQL*Plus’s Output
We often don’t want the SQL*Plus banner to be displayed when running SQL*Plus via script. SQL*Plus provides the -S option to suppress this banner. The next version of the Groovy script will take advantage of that as shown in sql02.groovy:
sql02.groovy
#!/usr/bin/env groovydef sqlplusCmd = "sqlplus -S hr/hr @02-employeeIds.sql".execute()sqlplusCmd.in.eachLine { line -> println line }
The only changes to this script from the Groovy script in the first example is the addition of -S to suppress SQL*Plus’s banner and the calling of a different SQL script file (02-employeeIds.sql), which will be shown next.
When running SQL*Plus with scripts, it is often convenient to suppress other portions of feedback in addition to suppressing the SQL*Plus banner. Many of these types of output are turned off or suppressed within SQL*Plus by using the SET command to set the relevant property to a desired value. The code listing for 02-employeeIds.sql demonstrates turning off query results feedback, header information, and page separation in the SQL*Plus results and also explicitly exits from SQL*Plus.
02-employeeIds.sql
-- HEADING off turns off column headings in output of query resultsset HEADING off-- FEEDBACK turns off message saying number of rows returnedset FEEDBACK off-- PAGESIZE to 0 removes spaces between "pages" of resultsset PAGESIZE 0
select employee_id from employees;
-- Return SQL*Plus settings to defaultsset HEADING onset FEEDBACK 6set PAGESIZE 14exit
The next two screen snapshots demonstrate the leaner/cleaner output with the SQL*Plus banner suppressed along with no headers, no page separation, and no feedback. The second of the images proves that exit successfully exits SQL*Plus (and the invoking Groovy script).
Example 3: Passing in Parameter
It is often the case that we want to run SQL*Plus scripts depending on parameters set dynamically when the script is executed. SQL*Plus accepts command-line parameters and this feature is leveraged in the next SQL script listing.
03-employeeFind.sql
-- HEADING off turns off column headings in output of query resultsset HEADING off-- FEEDBACK turns off message saying number of rows returnedset FEEDBACK off-- PAGESIZE to 0 removes spaces between "pages" of resultsset PAGESIZE 0
select first_name || ' ' || last_name "NAME" from employees where employee_id = &1;
-- Return SQL*Plus settings to defaultsset HEADING onset FEEDBACK 6set PAGESIZE 14exit
In the above script, an employee’s full name is returned based on a provided parameter (&1) representing the employee’s ID. To support this expected parameter, the Groovy script must supply that ID to the SQL*Plus script. This is shown in the Groovy script sql03.sql:
sql03.grooy
#!/usr/bin/env groovyif (!args){ println "You must supply the ID of the employee of interest." System.exit(-1)}def sqlplusCmd = "sqlplus -S hr/hr @03-employeeFind.sql ${args[0]}".execute()sqlplusCmd.in.eachLine { line -> println line }
This Groovy script exits promptly if no parameter is provided and informs the user that an ID is required. If the ID is provided, it is passed to the SQL*Plus script by appending it to the end of the SQL*Plus invocation. The output from this is shown next.
The screen snapshot just shown demonstrates that the parameter passing from command-line to Groovy script to SQL*Plus worked fine. The only downside is that the SQL*Plus variable substitution is explicitly shown and this may not always be desirable. I can turn this off by specifying set VERIFY off in the SQL*Plus script. When I do that, the output is as shown next.
Example 4: Handling Return SQL*Plus Return Code
Because Groovy’s process management capability uses a GDK-extended Process, the Process.exitValue() method can be used to access the codes returned from invoked commands. This allows the Groovy code to analyze the codes returned from the invoked script and act appropriately.
To illustrate this, the SQL*Plus script used above is modified to return a -4. The modified version is shown next.
04-employeeFind.sql
-- HEADING off turns off column headings in output of query resultsset HEADING off-- FEEDBACK turns off message saying number of rows returnedset FEEDBACK off-- PAGESIZE to 0 removes spaces between "pages" of resultsset PAGESIZE 0-- VERIFY off turns off the prompts showing variable substitutionset VERIFY off
select first_name || ' ' || last_name "NAME" from employees where employee_id = &1;
-- Return SQL*Plus settings to defaultsset HEADING onset FEEDBACK 6set PAGESIZE 14set VERIFY on
-- Pretend there is some type of error or other condition in which the returning-- of -4 is appropriateexit -4
The modified Groovy script that takes advantage of Process.exitValue() to process this returned code is shown next. This Groovy script simply prints the value, but it could implement alternative logic based on the code returned.
sql04.groovy
#!/usr/bin/env groovyif (!args){ println "You must supply the ID of the employee of interest." System.exit(-1)}def sqlplusCmd = "sqlplus -S hr/hr @04-employeeFind.sql ${args[0]}".execute()sqlplusCmd.in.eachLine { line -> println line }println "Return value: ${sqlplusCmd.exitValue()}"
When this script is executed, its output looks like that shown in the next screen snapshot.
Example 5: Processing Significant SQL*Plus Output
There are times when the Groovy script needs to process more than a return code. One way of doing this is to have the SQL*Plus script write out data to an external file that the Groovy script can process. It helps, of course, that Groovy provides some nice GDK File extensions.
The SQL*Plus SPOOL command can be used to spool output to an operating system file.
An example of a SQL*Plus script that writes employee last names and first names, separated by a comma, on individual lines of a file named “name.txt” is shown next.
05-employeeList.sql
-- HEADING off turns off column headings in output of query resultsset HEADING off-- FEEDBACK turns off message saying number of rows returnedset FEEDBACK off-- PAGESIZE to 0 removes spaces between "pages" of resultsset PAGESIZE 0-- VERIFY off turns off the prompts showing variable substitutionset VERIFY off-- COLSEP sets character to be placed between returned columnsset COLSEP ,-- TRIMSPOOL affects spooling output only; not SQL*Plusset TRIMSPOOL on
spool name.txtselect last_name, first_name from employees;spool off
-- Return SQL*Plus settings to defaultsset HEADING onset FEEDBACK 6set PAGESIZE 14set VERIFY onset COLSEP " "set TRIMSPOOL offexit
When a Groovy script runs this file, the expected file named name.txt is generated. A portion of it (top and bottom) is shown next.
Abel ,EllenAnde ,SundarAtkinson ,MozheAustin ,DavidBaer ,HermannBaida ,ShelliBanda ,AmitBates ,ElizabethBell ,SarahBernstein ,DavidBissot ,LauraBloom ,HarrisonBull ,Alexis
. . .
Vollman ,ShantaWalsh ,AlanaWeiss ,MatthewWhalen ,JenniferZlotkey ,Eleni
This output file can be processed in the same Groovy script that caused it to be generated. Groovy provides rich features for file handling and String manipulation, making it highly effective in this situation.
sql05.groovy
#!/usr/bin/env groovydef sqlplusCmd = "sqlplus -S hr/hr @05-employeeList.sql".execute()def file = new File("name.txt")file.eachLine { println "Name (Last, First): ${it}" }
Much more sophisticated logic could be performed on the contents of the name.txt file, but this demonstrates how easy it is to fix the spooled output with the Groovy script. The bottom portion of the script’s execution is shown in the next screen snapshot.
Conclusion and Final Remarks
Groovy and SQL*Plus can be used effectively together to script data-related functionality. Groovy offers many of the advantages of traditional scripting languages when used in conjunction with SQL*Plus, but offers the added advantage of enjoying full access to the JVM and the plethora of Java libraries. Groovy can be used with existing SQL*Plus scripts or can access the database directly with its powerful and convenient JDBC abstraction GroovySql.
