Why would I get ORA-01422 error while inserting a single row using JDBC

user_238357 0
Viewed 82

I see many related questions about this particular error - most of them having to do with SELECT INTO operations.

In this case though, I'm trying to insert a single row and don't find any answers to this particular issue.

I'm using the following code:

final String insertSQL = "INSERT INTO XMLTYPETEST_XMLTYPE (ACTIVITYGUID, XMLDATA) VALUES (:1, XMLType(:2))";
final String guid = "BFACFEB-8ACE-4145-B04A-759822E0AA7D";
final String xml = "<Activity><Changes></Changes></Activity>";
try (OraclePreparedStatement stmt = 
        (OraclePreparedStatement) conn.prepareStatement(insertSQL)) {
    stmt.setString(1, guid);
    stmt.setString(2, xml);
    stmt.execute();

} catch (SQLException ex) {
    Logger.getLogger(DBConnectionTest.class.getName())
          .log(Level.SEVERE, null, ex);
    fail();
}

The table XMLTYPETEST_XMLTYPE is defined as follows:



  CREATE TABLE "PLAYGROUND"."XMLTYPETEST_XMLTYPE" 
   (    "ACTIVITYGUID" CHAR(36 BYTE), 
    "XMLDATA" "XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 XMLTYPE COLUMN "XMLDATA" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA ;


Name          Type           
------------  -------------- 
ACTIVITYGUID  CHAR(36)       
XMLDATA       PUBLIC.XMLTYPE

I've tried various combinations of objects and set*() methods for setting the XMLData parameter as well as using executeUpdate(), all with the same result:

ORA-01422: exact fetch returns more than requested number of rows

In case there's any clue in the library versions, here are the relevant dependencies:

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/oracle/xdb -->
<dependency>
    <groupId>oracle</groupId>
    <artifactId>xdb</artifactId>
    <version>1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle/xmlparserv2 -->
<dependency>
    <groupId>com.oracle.database.xml</groupId>
    <artifactId>xmlparserv2</artifactId>
    <version>19.3.0.0</version>
</dependency>

Thanks.

jdbc oracle java
1 Answers

The more I thought about it, the more suspicious the mismatched library versions were. I replaced two libraries with the following versions:

        <dependency>
            <groupId>com.oracle.database.xml</groupId>
            <artifactId>xmlparserv2</artifactId>
            <version>12.2.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.xml</groupId>
            <artifactId>xdb6</artifactId>
            <version>12.2.0.1</version>
        </dependency>

That fixed the problem. /facepalm

user_2383570
Related