Passing The ODCIVARCHAR2LIST from java to Oracle database 19c

Question

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.

Answer 1

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.