This article discusses the problem that the output does not match expectations when executing SQL*Plus commands through Runtime.exec(String) in Java applications. The main reason is that Runtime.exec(String) improperly parses command strings containing complex parameters such as spaces and quotes. The article provides two solutions: passing command parameters as an array using Runtime.exec(String[]), and the more recommended ProcessBuilder class, which provides finer process control and standard flow management to ensure that commands are executed correctly and capture the expected output.

Problem description

In Java applications, developers may need to execute system commands through external processes, such as calling Oracle SQL Plus to execute SQL or PL/SQL scripts. However, when using the Runtime.getRuntime().exec(String cmd) method to execute SQL Plus commands containing complex parameters (especially connection strings with spaces and quotes) , the observed output may not be the expected script execution result, but the help information or usage description of SQL*Plus.

For example, executing the following SQL*Plus command directly on the operating system command line can normally obtain error information for PL/SQL execution:

 sqlplus -s -LOGON <user_name>/<password>@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=host1.com)(Port=1725))(ADDRESS=(PROTOCOL=TCP)(Host=host2.com)(Port=1725))(LOAD_BALANCE = ON)(FAILOVER = ON) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME=service.com)))" @Load.sql</password></user_name>

Expected output (e.g. PL/SQL error):

 BEGIN
*
ERROR at line 1:
ORA-20004: Data is not ready, please check control-M v8 jobs
ORA-06512: at "GLOBAL_OWNER.PKG_COMMON_UTILS", line 282
ORA-06512: at line 2

However, when the same command string is executed in Java using Runtime.getRuntime().exec(String cmd), the output becomes SQL*Plus usage description:

 SQL*Plus: Release 12.1.0.2.0 Production
...
Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.
Usage 1: sqlplus -H | -V
...

Root Cause Analysis

The fundamental reason for this difference is that the Runtime.getRuntime().exec(String cmd) method is different from the way the operating system shell parses commands.

1. Runtime.exec(String cmd) Limitation: When a single string is passed as a command, Runtime.exec(String) does not resolve quotes and spaces intelligently like a shell. It will try to execute the entire string as a whole, or simply split it based on spaces, but will not correctly handle the contents in quotes as a parameter. This means that complex connection strings (such as "(DESCRIPTION=...)") may be mis-segmented or interpreted when passed to sqlplus, causing the sqlplus command to fail to recognize the correct parameters, thus falling back to displaying its usage description.

2. Smart parsing of Shell: On the contrary, when executed directly on the command line, the shell (such as Bash, CMD, etc.) will be responsible for parsing the command string. It can recognize quotes and pass the contents in quotes as a whole to the target program (sqlplus).

Solution

To solve this problem, we need to make sure Java passes command parameters to external programs in the same way as the shell. This can be achieved in two main ways:

Solution 1: Use Runtime.exec(String[] cmdarray)

This method allows you to pass commands and all their parameters as separate elements of a string array. In this way, Java will not try to parse the entire command string by itself, but will pass each array element to the external process as an independent parameter.

Sample code:

 import java.io.IOException;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.ExecutionException;
import java.util.function.Consumer;

public class RunSqlPlusCorrected {

    public static void main(String[] args) {
        try {
            // Decompose the command and its parameters into each element of the string array String[] cmdArray = new String[] {
                "sqlplus",
                "-s",
                "-LOGON",
                "<user_name>/<password>@\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=host1.com)(Port=1725))(ADDRESS=(PROTOCOL=TCP)(Host=host2.com)(Port=1725))(LOAD_BALANCE = ON)(FAILOVER = ON) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME=service.com)))\"",
                "@Load.sql"
            };

            Process process = Runtime.getRuntime().exec(cmdArray);

            // Use StreamGobbler to handle standard output and standard error streams StreamGobbler outputGobbler =
                    new StreamGobbler(process.getInputStream(), System.out::println);
            StreamGobbler errorGobbler =
                    new StreamGobbler(process.getErrorStream(), System.err::println);

            Future> outputFuture = Executors.newSingleThreadExecutor().submit(outputGobbler);
            Future> errorFuture = Executors.newSingleThreadExecutor().submit(errorGobbler);

            int exitCode = process.waitFor();
            System.out.println("Exited with code: " exitCode);

            outputFuture.get(); // Wait for the output stream processing to complete errorFuture.get(); // Wait for the error stream processing to complete} catch (IOException | InterruptedException | ExecutionException e) {
            e.printStackTrace();
        }
    }

    // StreamGobbler auxiliary class, used to asynchronously read process output stream private static class StreamGobbler implements Runnable {
        private InputStream inputStream;
        private Consumer<string> consumer;

        public StreamGobbler(InputStream inputStream, Consumer<string> consumer) {
            this.inputStream = inputStream;
            this.consumer = consumer;
        }

        @Override
        public void run() {
            try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream))) {
                reader.lines().forEach(consumer);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}</string></string></password></user_name>

Notes:

• Runtime.exec(String) has been marked as deprecated in JDK 18 because it does have parsing issues when dealing with complex commands. ProcessBuilder is recommended.
• Take each component of the command (including the command itself, options, parameters, and file paths) as a separate element of the array.
• For parameters that contain spaces but need to be a whole, such as concatenating a string, put it into the array as a separate string element. If the parameter contains quotes inside, these quotes should also be part of the string.

Solution 2: Use ProcessBuilder (recommended)

The ProcessBuilder class provides a more powerful and flexible way to create and manage external processes. It allows you to set up working directories, environment variables, and more granular control over the redirection of standard inputs, outputs, and error streams.

Sample code:

 import java.io.IOException;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.ExecutionException;
import java.util.function.Consumer;
import java.util.Arrays;

public class RunSqlPlusWithProcessBuilder {

    public static void main(String[] args) {
        try {
            // Decompose the command and its parameters into each element of the string array String[] cmdArray = new String[] {
                "sqlplus",
                "-s",
                "-LOGON",
                "<user_name>/<password>@\"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=host1.com)(Port=1725))(ADDRESS=(PROTOCOL=TCP)(Host=host2.com)(Port=1725))(LOAD_BALANCE = ON)(FAILOVER = ON) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME=service.com)))\"",
                "@Load.sql"
            };

            ProcessBuilder pb = new ProcessBuilder(cmdArray);
            // You can set the working directory, for example: pb.directory(new File("/path/to/script/directory"));
            // You can set environment variables, for example: pb.environment().put("VAR_NAME", "VAR_VALUE");

            // Redirect the standard error stream to the standard output stream, so that only one input stream needs to be processed pb.redirectErrorStream(true);

            Process process = pb.start();

            // Use StreamGobbler to process the merged output stream StreamGobbler outputGobbler =
                    new StreamGobbler(process.getInputStream(), System.out::println);

            Future> outputFuture = Executors.newSingleThreadExecutor().submit(outputGobbler);

            int exitCode = process.waitFor();
            System.out.println("Exited with code: " exitCode);

            outputFuture.get(); // Wait for the output stream processing to complete} catch (IOException | InterruptedException | ExecutionException e) {
            e.printStackTrace();
        }
    }

    // StreamGobbler auxiliary class (same as above)
    private static class StreamGobbler implements Runnable {
        private InputStream inputStream;
        private Consumer<string> consumer;

        public StreamGobbler(InputStream inputStream, Consumer<string> consumer) {
            this.inputStream = inputStream;
            this.consumer = consumer;
        }

        @Override
        public void run() {
            try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream))) {
                reader.lines().forEach(consumer);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}</string></string></password></user_name>

Advantages of ProcessBuilder:

• Parameter parsing: Like Runtime.exec(String[]), the ProcessBuilder constructor accepts a list of strings or arrays to ensure that each parameter is passed correctly, avoiding shell parsing problems.
• Stream redirection: ProcessBuilder provides rich stream redirection options, such as redirectOutput(), redirectError(), redirectInput(), and the very practical redirectErrorStream(true), which can merge standard error streams into standard output streams, simplifying stream processing logic, and avoiding the risk of deadlocks (when the parent process does not read the child process's output stream in time, the child process may block because the buffer is full).
• Environment and Directory: It is easy to set the working directory and environment variables of the child process, which is very useful for certain commands that require a specific environment to run correctly.
• Chained calls: ProcessBuilder supports chain calls, making the code more concise and easy to read.

Summarize

When executing external commands in Java, especially those with complex parameters or containing special characters (such as spaces, quotes), you should avoid using Runtime.getRuntime().exec(String cmd). The best practice is to use Runtime.getRuntime().exec(String[] cmdarray) or the more recommended java.lang.ProcessBuilder class. By breaking the command and its parameters into independent string array elements, you can ensure that these parameters are correctly passed to the external process, thus obtaining the expected execution result. At the same time, it is important to correctly handle the standard output and standard error flow of external processes to avoid process blocking and obtaining complete execution information. For database operations, although direct call to SQLplus is useful in certain specific scenarios, it is generally recommended to use the JDBC driver for database interaction because it provides a safer, more efficient and type-safe programming interface.

The above is the detailed content of Troubleshooting and solution for output exceptions by calling SQLPlus command in Java. For more information, please follow other related articles on the PHP Chinese website!