in java oracle How can I bind value ,java.sql.SQLException: Missing IN or OUT parameter at index::

Question

this is my SQL

SELECT *
  FROM (SELECT T_GDS.OWNER_NO,
               T_GDS.GOODS_NO,
               T_GDS.GOODS_NAME,
               T_GDS.GOODS_LENGTH,
               T_GDS.GOODS_WIDTH,
               T_GDS.GOODS_HEIGHT,
                T_GDS.MARKETINGAUTHORISATIONHOLDER,
               (SELECT MIN(T_STK.DISPLAY_LOCATION)
                  FROM VIW_FD_STOCK T_STK
                 WHERE T_STK.GOODS_ID = T_GDS.GOODS_ID) DISPLAY_LOCATION
          FROM FD_GOODS T_GDS
          LEFT JOIN REC_GOODS_RST T_RST
            ON T_GDS.GOODS_ID = T_RST.GOODS_ID
         WHERE T_GDS.IS_AVAILABLE = 'Y'
           AND T_RST.LWH_MOD_FLG IS NULL
           AND T_GDS.IS_GIFT = 'N'
           AND (T_GDS.GOODS_LENGTH < = 1 OR T_GDS.GOODS_WIDTH < = 1 OR
               T_GDS.GOODS_HEIGHT < = 1)
           AND EXISTS
         (SELECT 1 FROM FD_STOCK T WHERE T.GOODS_ID = T_GDS.GOODS_ID))
 WHERE (:GOODS_NO IS NULL OR GOODS_NO LIKE '%' || :GOODS_NO || '%')
   AND (:GOODS_NAME IS NULL OR GOODS_NAME LIKE '%' || :GOODS_NAME || '%') 
   AND (:DISPLAY_LOCATION IS NULL OR DISPLAY_LOCATION LIKE '%' || :DISPLAY_LOCATION || '%') 
   ORDER BY DISPLAY_LOCATION,GOODS_NO

call = conn.prepareCall(SQL);
call.setObject("GOODS_NO", obj.get("Value"), obj.getInteger("Type"))
call.setObject("GOODS_NAME", obj.get("Value"), obj.getInteger("Type"))
call.setObject("DISPLAY_LOCATION", obj.get("Value"), obj.getInteger("Type"))

but I get error java.sql.SQLException: Missing IN or OUT parameter at index:: 4

I have two "GOODS_NO","GOODS_NAME" and "DISPLAY_LOCATION" in SQL so what should I do?

java.sql.SQLException: Missing IN or OUT parameter at index:: 4 — ighack, Mar 27 '21 at 11:59
if I have only one "GOODS_NO","GOODS_NAME" and "DISPLAY_LOCATION", it works — ighack, Mar 27 '21 at 12:06
I'm not very familiar with java and cannot tell you if this can work the way you expect, but `CallableStatement` is intended to be used for procedure call. For DML you need `PreparedStatement` which in turn does not support named parameters. You can workaround this with your own wrapper like [this](https://stackoverflow.com/q/2309970/), or rewrite your code like `GOODS_NO like coalesce('%' || :GOODS_NO || '%', '%')` to avoid repetition. — astentx, Mar 27 '21 at 14:14

score 0 · Accepted Answer · answered Mar 29 '21 at 05:14

OracleConnection connection = conn.unwrap(OracleConnection.class);
ocall = (OracleCallableStatement) connection.prepareCall(SQL);
……………………………………
ocall.setObjectAtName(obj.getString("ParameterName"),obj.get("Value"),obj.getInteger("Type"));

in java oracle How can I bind value ,java.sql.SQLException: Missing IN or OUT parameter at index::

1 Answers1