CODEWITHSUNDEEP
javaoracle-databasejdbc
Jon Wisniewski
Jon Wisniewski

Reputation: 97

Oracle ORA-01722 While Calling Java Procedure

What I am trying to do is take the inputs from the console and insert them into a database by a procedure call. What is happening is that I am getting an error every time I try to run it.

ORA-01722: invalid number ORA-06512: at "xxxxxxxx.CREATE_APPLICATIONS", line 16 ORA-06512: at line 1

This is the procedure I am trying to run:

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!";
}

Here is the stored procedure:

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;
/

Here are the two sequences:

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;
/

Here is the table definition:

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
);

Upvotes: 0

Views: 339

Answers (2)

APC
APC

Reputation: 146349

The VALUES section of your INSERT statement finishes like this:

SSN, 
STATUS, 
CLAIM_NUMBER_SEQ.NEXTVAL,
ACCOUNT_TYPE
);

You haven't specified the target projection so the VALUES are mapped to the table in column position order. If we look at your table we can see your table looks like this:

"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

So your INSERT tries to apply the status parameter to the claim number column and vice versa. The problem is that status is a string but claim number is numeric, and that's why you get ORA-01722: invalid number.

How do I specify Target projection?

By listing the target columns before the VALUES clause:

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)

Here the target project matches the order of the attributes in the VALUES clause, rather than the table positions.

Incidentally, you will have noticed that I laid out the statements with one element per line. That made it simple to see the error. Neat layout is not just pedantry. Readability is a feature, and code which has that feature is easier to diagnose (and arguably less prone to bugs in the first place).

Upvotes: 1

Luke Woodward
Luke Woodward

Reputation: 65064

Here's your CREATE TABLE statement, abbreviated somewhat:

CREATE TABLE "JONATHANWISNIEWSKI"."BANKING_APPLICATIONS" 
(
  ...
"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
) ...

Here's your INSERT statement, again somewhat abbreviated:

INSERT INTO BANKING_APPLICATIONS
 VALUES (..., SSN, STATUS, 
 CLAIM_NUMBER_SEQ.NEXTVAL, ACCOUNT_TYPE);

See the problem?

You have the STATUS and CLAIM_NUMBER columns the wrong way around. CLAIM_NUMBER is numeric but STATUS isn't, so you get an error attempting to put a non-numeric status value into a numeric claim-number column.

To fix this problem, specify the list of columns you are inserting into in your INSERT statement:

INSERT INTO BANKING_APPLICATIONS 
  (APPLICATION_ID, FIRST_NAME, LAST_NAME, ... )
VALUES (APPLICATION_SEQ.NEXTVAL, FIRST_NAME, LAST_NAME, ...)

That way, the ordering of the columns in the table doesn't matter. As long as the order of the column names and the order of the values within the INSERT statement match, you'll be okay.

Upvotes: 1

Related Questions