英文:
Why would I get ORA-01422 error while inserting a single row using JDBC
问题
我看到许多与此特定错误相关的问题，其中大部分与SELECT INTO操作有关。
然而，在这种情况下，我试图插入一行记录，却没有找到关于这个特定问题的答案。
我正在使用以下代码：
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();
}
表XMLTYPETEST_XMLTYPE的定义如下：
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 ;
我尝试了各种对象和set*()方法的组合来设置XMLData参数，以及使用executeUpdate()，但结果都一样：
ORA-01422: 精确提取返回的行数超过所请求的行数
如果库版本中有任何线索，以下是相关的依赖项：
<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>
谢谢。
英文:
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.
答案1
得分: 0
越是思考，越觉得不匹配的库版本可疑。我将两个库替换为以下版本：
``` lang-xml
<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>
问题得到解决。/捂脸
<details>
<summary>英文:</summary>
The more I thought about it, the more suspicious the mismatched library versions were. I replaced two libraries with the following versions:
``` lang-xml
<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
专注分享java语言的经验与见解，让所有开发者获益！
评论