This article discusses in depth the issue of inconsistent output and execution directly in the shell when executing SQL*Plus commands through Runtime.exec(String) in Java. The core reason is that Java improperly parses complex command strings. The article provides a solution to correctly pass command parameters using Runtime.exec(String[]) and the more recommended ProcessBuilder, and emphasizes the importance of properly handling process input and output streams to ensure that the commands execute as expected and capture the full output.

1. Analysis of problem background and phenomenon

When trying to execute external commands in Java applications, especially commands like SQL*Plus with complex parameters and special characters (such as quotes, brackets), developers may encounter a common problem: the output of a command executed in Java is inconsistent with the output executed directly in the operating system's shell.

For example, a SQL*Plus command designed to execute a SQL script and expect to capture its error output:

 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>

When this command is executed directly in the shell, the execution result or error message of the SQL*Plus script will be displayed as expected, for example:

 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 through Java's Runtime.getRuntime().exec(String cmd) method, its output may become SQL*Plus usage help information rather than the actual execution result:

 SQL*Plus: Release 12.1.0.2.0 Production
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.
Usage 1: sqlplus -H | -V
... (A lot of usage help information) ...

This phenomenon shows that Java's Runtime.exec(String) method fails to correctly parse parameters in the command string, causing the SQL*Plus program to think that it has not received any valid parameters, and thus print out its instructions for use.

2. The root of the problem: the difference in command string parsing

The Runtime.exec(String cmd) method internally passes in single string commands to the operating system's default shell (such as cmd.exe on Windows or /bin/sh or /bin/bash on Unix/Linux) to execute. This process involves the Shell's own command parsing logic. However, Java's exec method may have a slight difference in how strings are parsed before passing them to the shell, especially when dealing with complex parameters containing spaces, quotes, special characters (such as ().

More importantly, this behavior of Runtime.exec(String) has been marked as deprecated in JDK 18 and later because it does have uncertainty when dealing with complex commands. When command strings contain spaces or quotes, the default word segmenter inside Java may not be able to accurately split them into independent parameters, causing external programs such as sqlplus to receive an error or incomplete parameter list.

3. Solution: explicitly pass command parameters

To solve this problem, we should avoid relying on the internal string parsing mechanism of Runtime.exec(String), and instead explicitly pass the command and all its parameters as independent string array elements. Java provides two main ways to achieve this:

3.1 Using Runtime.exec(String[] cmdarray)

This method receives an array of strings where the first element is the executable command itself and the subsequent element is its parameters. In this way, Java will not try to parse the entire command string by itself, but will directly pass each array element to the external process as an independent parameter.

Sample code:

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

public class RunSqlPlusFixed {

    public static void main(String[] args) {
        // Note: The user/password and connection strings here are examples, and the production environment should avoid hard-coded or plaintext storage String userName = "<user_name>";
        String password = "<password>";
        String connectString = "(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)))";
        String sqlScript = "@Load.sql"; // Assume Load.sql is in the current working directory // Put commands and each parameter into the array as independent string elements String[] cmd = new String[] {
            "sqlplus",
            "-s",
            "-LOGON",
            userName "/" password "@" connectString, // Connect string as a whole parameter sqlScript
        };

        Process process;
        try {
            process = Runtime.getRuntime().exec(cmd);

            // Important: The output stream and error stream of the process must be consumed to avoid process blocking StreamGobbler outputGobbler =
                    new StreamGobbler(process.getInputStream(), System.out::println);
            StreamGobbler errorGobbler =
                    new StreamGobbler(process.getErrorStream(), System.err::println);

            ExecutorService executorService = Executors.newFixedThreadPool(2);
            Future> outputFuture = executorService.submit(outputGobbler);
            Future> errorFuture = executorService.submit(errorGobbler);

            int exitCode = process.waitFor(); // Wait for the process to complete System.out.println("SQL*Plus process exited with code: " exitCode);

            outputFuture.get(); // Make sure the output stream is fully read errorFuture.get(); // Make sure the error stream is fully read executorService.shutdown(); // Close thread pool} catch (IOException | InterruptedException | ExecutionException e) {
            e.printStackTrace();
        }
    }

    // StreamGobbler helper class, used to asynchronously read the input stream of a process 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) {
                System.err.println("Error reading stream: " e.getMessage());
            }
        }
    }
}</string></string></password></user_name>

In the above code, SQL*Plus' connection string (DESCRIPTION=...) is passed as a whole to the sqlplus command, because it is usually considered a separate parameter in the shell. By splicing the entire connection string with the username/password and using it as an element in the array, we ensure that the sqlplus command receives it correctly.

3.2 Using ProcessBuilder (recommended)

The ProcessBuilder class provides a more powerful and flexible way to create and manage external processes. It allows you to:

• Define commands and parameters more clearly.
• Set up the working directory.
• Configure environment variables.
• Redirecting standard input, output, and error streams, such as redirecting error streams to output streams, simplifying stream processing.

Sample code:

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

public class RunSqlPlusProcessBuilder {

    public static void main(String[] args) {
        String userName = "<user_name>";
        String password = "<password>";
        String connectString = "(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)))";
        String sqlScript = "Load.sql"; // Assume Load.sql is in the current working directory List<string> command = new ArrayList();
        command.add("sqlplus");
        command.add("-s");
        command.add("-LOGON");
        command.add(userName "/" password "@" connectString); // The entire connection string is used as a single parameter command.add("@" sqlScript); // The script path ProcessBuilder processBuilder = new ProcessBuilder(command);
        // You can set the working directory, for example:
        // processBuilder.directory(new File("/path/to/sql/scripts"));

        // Redirect the standard error stream to the standard output stream, so that only one stream is processBuilder.redirectErrorStream(true);

        try {
            Process process = processBuilder.start();

            // Just process one output stream (as it contains the original stdout and stderr)
            StreamGobbler outputGobbler =
                    new StreamGobbler(process.getInputStream(), System.out::println);

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

            int exitCode = process.waitFor();
            System.out.println("SQL*Plus process exited with code: " exitCode);

            outputFuture.get(); // Make sure the output stream is fully read executorService.shutdown();

        } catch (IOException | InterruptedException | ExecutionException e) {
            e.printStackTrace();
        }
    }

    // StreamGobbler auxiliary class remains unchanged 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) {
                System.err.println("Error reading stream: " e.getMessage());
            }
        }
    }
}</string></string></string></password></user_name>

Using ProcessBuilder's redirectErrorStream(true) method is a very useful trick, which can incorporate standard error streams of processes into standard output streams, simplifying the Java side's processing of output and avoiding the complexity of managing two streams at the same time.

4. Precautions and best practices

1. Correct splitting of parameters : Whether using Runtime.exec(String[]) or ProcessBuilder, the most important thing is to correctly split the command and all its parameters into independent string elements. Any argument that is considered a whole in the shell (even if it contains spaces or special characters, such as parts wrapped in quotes), should be a separate string element in Java's parameter array.
2. Handling process I/O streams : This is the most overlooked but crucial point when executing external processes. The standard output stream (stdout) and standard error stream (stderr) of external processes may generate large amounts of data. If these streams are not read in time, they may fill the operating system's buffer, causing the child process to block, and even the parent process (Java application) may hang as a result. Therefore, it is best practice to always use separate threads (such as StreamGobbler) to consume these streams asynchronously.
3. Security : It is very unsafe to hardcode database usernames and passwords directly in the code. In production environments, more secure mechanisms should be used to manage credentials such as environment variables, configuration file encryption, Java KeyStore, or Vault services.
4. Error handling : It is necessary to catch IOException (the command cannot be executed), InterruptedException (the thread is interrupted while waiting for the process) and ExecutionException (the asynchronous task execution error). It is also important to check the process's exit code (the return value of process.waitFor()) because non-zero exit code usually indicates that the command execution failed.
5. Resource Management : Ensure that all relevant input/output streams are closed after the process is completed and that ExecutorService is closed if no longer needed.

By following these guidelines, you can reliably execute external commands in your Java application and accurately capture their output, avoiding unexpected behaviors caused by command parsing problems.

The above is the detailed content of Detailed explanation and solution for the problem of inconsistent output of SQLPlus commands in Java. For more information, please follow other related articles on the PHP Chinese website!