Missing IN or OUT parameter at index::

Here is my java code

String insertSql = "CALL INSERT_EXAMPLE(?, ?, ?)";
CallableStatement stmt = conn.prepareCall(insertSql);
this.stmt.setObject(1, nameText);
this.stmt.setObject(2, phoneText);
this.stmt.registerOutParameter(3, Types.INTEGER);
boolean results = stmt.execute();
Integer t_id = null;
if(results) {
    ResultSet rs = stmt.getResultSet();
    if(rs.next()) {
         t_id = stmt.getInt("id");
    }
}

Here is my procedure

CREATE OR REPLACE PROCEDURE INSERT_EXAMPLE(
    name                              IN                        VARCHAR2,
    phone                             IN                        VARCHAR2, 
    OUT_RESULT_CODE                   OUT                       INTEGER
    )
AS 
    id       NUMBER;
BEGIN
  INSERT INTO USERS (
        T_NAME,
        T_PHONE
    ) VALUES (
        name,
        phone,
        ) RETURNING id INTO T_ID;
    COMMIT;
    OUT_RESULT_CODE := id;
END INSERT_EXAMPLE;

Here is my sql

CREATE TABLE "DB_NAME"."USERS"(
"T_ID" NUMBER,
"T_NAME" VARCHAR2(400 BYTE),
"T_PHONE" VARCHAR2(400 BYTE),
CONSTRAINT "USERS_PK" PRIMARY KEY ("T_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
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 "TABLESPACE1" ;
CREATE OR REPLACE NONEDITIONABLE TRIGGER "DB_NAME"."USERS_ON_INSERT"
BEFORE INSERT ON USERS
FOR EACH ROW BEGIN
SELECT USERS_SEQ.NEXTVAL
INTO :new.T_ID FROM dual; END; / ALTER TRIGGER "DB_NAME"."USERS_ON_INSERT" ENABLE;

Error:

java.sql.SQLException: Missing IN or OUT parameter at index:: 1

and this line: boolean results = stmt.execute();

Solution 1:

Your procedure has several errors:

1. Trailing comma after phone at the end of the VALUES.
2. id and t_id are the wrong way round in the RETURNING clause.

You can simplify the code to:

CREATE OR REPLACE PROCEDURE INSERT_EXAMPLE(
    name            IN  USERS.T_NAME%TYPE,
    phone           IN  USERS.T_PHONE%TYPE,
    OUT_RESULT_CODE OUT USERS.T_ID%TYPE
)
AS 
BEGIN
  INSERT INTO USERS (
    T_NAME,
    T_PHONE
  ) VALUES (
    name,
    phone
  ) RETURNING t_id INTO OUT_RESULT_CODE;
  
  COMMIT;
END INSERT_EXAMPLE;
/

Also, you should not have the COMMIT in the procedure as it will allow you to chain several procedure calls into the same transaction and then COMMIT/ROLLBACK them all together. If you COMMIT in the procedure and there is a subsequent error then you cannot ROLLBACK to the start of the transaction. Instead, you should either have the transaction set to auto-commit when it is closed or to explicitly commit before you close the connection.

db<>fiddle here

I can't test the Java code at the moment but I think it should be:

String insertSql = "CALL INSERT_EXAMPLE(?, ?, ?)";
CallableStatement stmt = conn.prepareCall(insertSql);
stmt.setObject(1, nameText);
stmt.setObject(2, phoneText);
stmt.registerOutParameter(3, Types.INTEGER);
boolean results = stmt.execute();
Integer t_id = null;
if(results) {
  t_id = stmt.getInt(3);
}

As an aside, you have defined the table with "T_PHONE" VARCHAR2(400 BYTE); do you really expect a 400 byte string for a phone number?