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/VistaMany 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 StatementOne 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 groovy
def 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 OutputWe 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 groovy
def 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 results
set HEADING off
-- FEEDBACK turns off message saying number of rows returned
set FEEDBACK off
-- PAGESIZE to 0 removes spaces between "pages" of results
set PAGESIZE 0
select employee_id from employees;
-- Return SQL*Plus settings to defaults
set HEADING on
set FEEDBACK 6
set PAGESIZE 14
exit
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 ParameterIt 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 results
set HEADING off
-- FEEDBACK turns off message saying number of rows returned
set FEEDBACK off
-- PAGESIZE to 0 removes spaces between "pages" of results
set PAGESIZE 0
select first_name || ' ' || last_name "NAME" from employees where employee_id = &1;
-- Return SQL*Plus settings to defaults
set HEADING on
set FEEDBACK 6
set PAGESIZE 14
exit
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 groovy
if (!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 CodeBecause 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 results
set HEADING off
-- FEEDBACK turns off message saying number of rows returned
set FEEDBACK off
-- PAGESIZE to 0 removes spaces between "pages" of results
set PAGESIZE 0
-- VERIFY off turns off the prompts showing variable substitution
set VERIFY off
select first_name || ' ' || last_name "NAME" from employees where employee_id = &1;
-- Return SQL*Plus settings to defaults
set HEADING on
set FEEDBACK 6
set PAGESIZE 14
set VERIFY on
-- Pretend there is some type of error or other condition in which the returning
-- of -4 is appropriate
exit -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 groovy
if (!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 OutputThere 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 results
set HEADING off
-- FEEDBACK turns off message saying number of rows returned
set FEEDBACK off
-- PAGESIZE to 0 removes spaces between "pages" of results
set PAGESIZE 0
-- VERIFY off turns off the prompts showing variable substitution
set VERIFY off
-- COLSEP sets character to be placed between returned columns
set COLSEP ,
-- TRIMSPOOL affects spooling output only; not SQL*Plus
set TRIMSPOOL on
spool name.txt
select last_name, first_name from employees;
spool off
-- Return SQL*Plus settings to defaults
set HEADING on
set FEEDBACK 6
set PAGESIZE 14
set VERIFY on
set COLSEP " "
set TRIMSPOOL off
exit
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 ,Ellen
Ande ,Sundar
Atkinson ,Mozhe
Austin ,David
Baer ,Hermann
Baida ,Shelli
Banda ,Amit
Bates ,Elizabeth
Bell ,Sarah
Bernstein ,David
Bissot ,Laura
Bloom ,Harrison
Bull ,Alexis
. . .
Vollman ,Shanta
Walsh ,Alana
Weiss ,Matthew
Whalen ,Jennifer
Zlotkey ,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 groovy
def 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 RemarksGroovy 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.
