I have Producer that get Serial of my data and update status, get data from view and update data in database, by java I pass the Serial array that I want update but in my case I got Error : : 17023 ->Unsupported feature: createArrayOf
Here My JAVA code :
public int acceptsGoodsInDepots(String userId, String[] ids) throws SQLException {
int insertedCount = 0;
try (Connection connection = JDBCPoolConnectionAPI.getPoolConnection().getConnection();
CallableStatement callableStatement = connection.prepareCall("{call change_trace_goods_for_aceepts(?, ?, ?)}")) {
callableStatement.setString(1, userId);
Array serialArray = connection.createArrayOf("VARCHAR", ids);
callableStatement.setArray(2, serialArray);
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.execute();
insertedCount = callableStatement.getInt(3);
}
return insertedCount;
}
And Here My Producer :
create or replace PROCEDURE change_trace_goods_for_aceepts (
p_receiver IN VARCHAR2,
p_good_serials IN SYS.ODCIVARCHAR2LIST,
p_inserted_count OUT NUMBER
) AS
v_insert_count NUMBER := 0; -- Initialize insert count
BEGIN
FOR i IN 1..p_good_serials.COUNT LOOP
DECLARE
v_trace_status NUMBER;
v_city VARCHAR2(32) ; v_province VARCHAR2(32) ; v_sender VARCHAR2(32) ; v_rec VARCHAR2(32);
BEGIN
-- Get the goods status
SELECT TRACE_STATUS , CITY , PROVINCE , SENDER_ID , RECEIVER_ID INTO v_trace_status , v_city , v_province , v_sender , v_rec
FROM VIEW_DEPOTS_GOODS
WHERE GOODS_SERIAL = p_good_serials(i);
-- Check if goods status is 4
IF v_trace_status = 1 or v_trace_status = 3 or v_trace_status is null THEN
-- Insert into TABLE_TRACE_GOODS
INSERT INTO TABLE_TRACE_GOODS (GOOD_SERIAL, SENDER, RECEIVER, PROVINCE, CITY, STATUS)
VALUES (p_good_serials(i), v_sender, v_rec, v_province, v_city, 4);
v_insert_count := v_insert_count + 1; -- Increment insert count
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Handle if goods serial not found
NULL;
END;
END LOOP;
-- Set the OUT parameter to the insert count
p_inserted_count := v_insert_count;
END;
I need pass String array from java into oracle sql producer. what is my mistake? what can I do for resolve it? in some way the said create database object or type but its common way and I think we have some answer without change oracle database type or create new one.
You want something like (untested as I do not have your database tables):
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
callableStatement.setString(1, userId);
ARRAY a = ((OracleConnection) con).createARRAY("SYS.ODCIVARCHAR2LIST", ids);
((OraclePreparedStatement) callableStatement).setARRAY(2, a);
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.execute();
Depending on how your connections are managed, you may need to unwrap the connection to get to the underlying OracleConnection
object rather than casting.