时间戳

首页 > 解决方案 > 调用 Java 过程时 Oracle ORA-01722

java - 调用 Java 过程时 Oracle ORA-01722

问题描述

我要做的是从控制台获取输入并通过过程调用将它们插入数据库。发生的事情是每次尝试运行它时都会出错。

ORA-01722：无效编号 ORA-06512：在“xxxxxxxx.CREATE_APPLICATIONS”，第 16 行 ORA-06512：在第 1 行

这是我试图运行的过程：

private String insert_application() {
    try {
        Connection conn = cf.getConnection();
        String sql = "{call create_applications (?,?,?,?,?,?,?,?,?,?,?,?)}";

        CallableStatement call = conn.prepareCall(sql);

        call.setString(1, this.getFirst_name());
        call.setString(2, this.getLast_name());
        call.setString(3, this.getAddress());
        call.setString(4, this.getCity());
        call.setString(5, this.getState());
        call.setInt(6, Integer.parseInt(this.getZipcode()));
        call.setString(7, this.getUsername());
        call.setString(8, this.getPassword());
        call.setInt(9, Integer.parseInt(this.getPhone()));
        call.setInt(10, Integer.parseInt(this.getSSN()));
        call.setString(11, this.getStatus());
        call.setString(12, this.getAccount_type());

        call.execute();
    } catch (SQLException e) {
        e.printStackTrace();
        return "Error: Having issues with the database. Please try again later! Inserting error";
    }

    return "File created!";
}

这是存储过程：

CREATE OR REPLACE PROCEDURE create_applications
  ( FIRST_NAME IN BANKING_APPLICATIONS.FIRST_NAME%TYPE,
    LAST_NAME IN BANKING_APPLICATIONS.LAST_NAME%TYPE,
    ADDRESS IN BANKING_APPLICATIONS.ADDRESS%TYPE,
    CITY IN BANKING_APPLICATIONS.CITY%TYPE,
    STATE IN BANKING_APPLICATIONS.STATE%TYPE,
    ZIPCODE IN BANKING_APPLICATIONS.ZIPCODE%TYPE,
    USERNAME IN BANKING_APPLICATIONS.USERNAME%TYPE,
    PASSWORD IN BANKING_APPLICATIONS.PASSWORD%TYPE,
    SSN IN BANKING_APPLICATIONS.SSN%TYPE,
    PHONE IN BANKING_APPLICATIONS.PHONE%TYPE,
    STATUS IN BANKING_APPLICATIONS.STATUS%TYPE,
    ACCOUNT_TYPE IN BANKING_APPLICATIONS.ACCOUNT_TYPE%TYPE)
AS
BEGIN
    INSERT INTO BANKING_APPLICATIONS
    VALUES (APPLICATION_SEQ.NEXTVAL, FIRST_NAME, LAST_NAME, ADDRESS, CITY, 
    STATE, ZIPCODE, USERNAME, PASSWORD, PHONE, SSN, STATUS, 
    CLAIM_NUMBER_SEQ.NEXTVAL, ACCOUNT_TYPE); 
    COMMIT;
END;
/

这是两个序列：

CREATE SEQUENCE APPLICATION_SEQ START WITH 1;

CREATE OR REPLACE TRIGGER APPLICATION_BIR 
   BEFORE INSERT ON BANKING_APPLICATIONS 
   FOR EACH ROW
BEGIN
   SELECT APPLICATION_SEQ.NEXTVAL
   INTO   :new.APPLICATION_ID
   FROM   dual;
END;
/

CREATE SEQUENCE CLAIM_NUMBER_SEQ START WITH 1;

CREATE OR REPLACE TRIGGER CLAIM_NUMBER_BIR  
   BEFORE INSERT ON BANKING_APPLICATIONS 
   FOR EACH ROW
BEGIN
   SELECT CLAIM_NUMBER_SEQ.NEXTVAL
   INTO   :new.CLAIM_NUMBER
   FROM   dual;
END;
/

这是表定义：

CREATE TABLE "BANKING_APPLICATIONS" 
("APPLICATION_ID" NUMBER(15,0) NOT NULL ENABLE, 
 "FIRST_NAME" VARCHAR2(15 BYTE) NOT NULL ENABLE, 
 "LAST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE, 
 "ADDRESS" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
 "CITY" VARCHAR2(25 BYTE) NOT NULL ENABLE, 
 "STATE" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
 "ZIPCODE" NUMBER(5,0) NOT NULL ENABLE, 
 "USERNAME" VARCHAR2(15 BYTE) NOT NULL ENABLE, 
 "PASSWORD" VARCHAR2(15 BYTE) NOT NULL ENABLE, 
 "PHONE" NUMBER(10,0) NOT NULL ENABLE, 
 "SSN" NUMBER(9,0) NOT NULL ENABLE, 
 "CLAIM_NUMBER" NUMBER(15,0) NOT NULL ENABLE, 
 "STATUS" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
 "ACCOUNT_TYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE
);

标签： javaoraclejdbc

解决方案

INSERT 语句的 VALUES 部分完成如下：

SSN, 
STATUS, 
CLAIM_NUMBER_SEQ.NEXTVAL,
ACCOUNT_TYPE
);

您尚未指定目标投影，因此 VALUES 按列位置顺序映射到表。如果我们查看您的表格，我们可以看到您的表格如下所示：

"SSN" NUMBER(9,0) NOT NULL ENABLE, 
"CLAIM_NUMBER" NUMBER(15,0) NOT NULL ENABLE, 
"STATUS" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
"ACCOUNT_TYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE

因此，您的 INSERT 尝试将status参数应用于claim number列，反之亦然。问题是它 status是一个字符串，但却claim number 是数字，这就是你得到ORA-01722: invalid number.

如何指定目标投影？

通过在 VALUES 子句之前列出目标列：

INSERT INTO BANKING_APPLICATIONS(
  APPLICATION_ID, 
  FIRST_NAME, 
  LAST_NAME, 
  ADDRESS, 
  CITY, 
  STATE,
  ZIPCODE, 
  USERNAME, 
  PASSWORD, 
  PHONE, 
  SSN, 
  STATUS, 
  CLAIM_NUMBER, 
  ACCOUNT_TYPE)
VALUES (
  APPLICATION_SEQ.NEXTVAL,
  FIRST_NAME,
  LAST_NAME,
  ADDRESS,
  CITY, 
  STATE, 
  ZIPCODE, 
  USERNAME, 
  PASSWORD, 
  PHONE, 
  SSN, 
  STATUS, 
  CLAIM_NUMBER_SEQ.NEXTVAL, 
  ACCOUNT_TYPE)

这里目标项目匹配 VALUES 子句中属性的顺序，而不是表位置。

顺便说一句，您会注意到我在语句中每行一个元素。这使得查看错误变得简单。整洁的布局不仅仅是迂腐。可读性是一个特性，具有该特性的代码更容易诊断（并且可以说首先不太容易出现错误）。

推荐阅读