Spring Boot调用Oracle存储过程返回值缺失右引号问题求解

Let's break down why you're seeing this "Y instead of the expected "Y" (or just Y/N) and some cleaner solutions beyond your temporary fix:

Possible Root Causes #

1. Stored Procedure Parameter Definition Issue:

   • If your output parameter is defined as CHAR(n) instead of VARCHAR2(n), Oracle will pad the string with spaces to match the defined length. Some JDBC driver versions might mishandle this padding, leading to truncated or malformed string output.
   • Alternatively, if the output parameter's length is too short (e.g., VARCHAR2(2) for a 3-character string like "Y"), Oracle will automatically truncate the last character, leaving you with only "Y.

2. JDBC Driver Bug:

   • Older versions of Oracle's JDBC driver (ojdbc) have known quirks with string handling for stored procedure outputs. This is especially true if you're using an outdated driver version that doesn't fully support your Oracle database version.

3. Unintended Quoting in Stored Procedure:

   • Double-check if your stored procedure is explicitly returning the value with quotes (e.g., returning '"Y"' instead of 'Y'). While SQL Navigator might display this correctly, the JDBC driver could be parsing it unexpectedly.

Clean Solutions #

1. Fix the Stored Procedure Parameter (Root Fix) #

First, verify and adjust your stored procedure's output parameter:

• Change the parameter type from CHAR(n) to VARCHAR2(n) (use a length that's sufficient for your expected output, e.g., VARCHAR2(3) if you need to return quoted values like "Y").
• Example stored procedure adjustment:

  CREATE OR REPLACE PROCEDURE check_user_exists(
      p_user_id IN VARCHAR2,
      p_status OUT VARCHAR2 -- Use VARCHAR2(3) instead of CHAR(2)
  ) AS
  BEGIN
      -- Your logic here
      p_status := '"Y"'; -- Or just 'Y' if quotes aren't needed
  END;
  

2. Upgrade Oracle JDBC Driver #

If you're using an older ojdbc version (e.g., ojdbc6 for Oracle 11g), upgrade to the latest stable version compatible with your database:

• For Oracle 12c+, use ojdbc8 (available via Maven/Gradle).
• Maven dependency example:

  <dependency>
      <groupId>com.oracle.database.jdbc</groupId>
      <artifactId>ojdbc8</artifactId>
      <version>21.13.0.0</version>
  </dependency>
  

3. Optimize Java Reading Logic #

If you need to keep the quoted output, use a concise way to clean up the string instead of manual handling:

• Remove surrounding quotes directly:

  CallableStatement cs = connection.prepareCall("{call check_user_exists(?, ?)}");
  cs.setString(1, userId);
  cs.registerOutParameter(2, Types.VARCHAR);
  cs.execute();
  
  // Clean up the string - removes leading/trailing quotes in one line
  String status = cs.getString(2).replaceAll("^\"|\"$", "");
  

• Use Apache Commons Lang (if available):
  If you already have Commons Lang in your project, use its StringUtils.strip method for cleaner code:

  String status = StringUtils.strip(cs.getString(2), "\"");
  

4. Ensure Correct Parameter Registration #

Always register the output parameter with the correct JDBC type. For string outputs, use Types.VARCHAR instead of Types.CHAR to avoid padding-related issues:

cs.registerOutParameter(2, Types.VARCHAR); // Correct for VARCHAR2 parameters

内容的提问来源于stack exchange，提问作者Thiagarajan Ramanathan