I’m encountering an issue with the DBMS_PICKLER package in Oracle. After creating a custom type
<code>create type NUM_ARRAY is varray(100) of NUMBER;
</code>
<code>create type NUM_ARRAY is varray(100) of NUMBER;
</code>
create type NUM_ARRAY is varray(100) of NUMBER;
I’m receiving the following exception:
<code>Caused by: oracle.jdbc.OracleDatabaseException: ORA-06550: line 1, column 17:
PLS-00302: component 'DBMS_PICKLER' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
</code>
<code>Caused by: oracle.jdbc.OracleDatabaseException: ORA-06550: line 1, column 17:
PLS-00302: component 'DBMS_PICKLER' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
</code>
Caused by: oracle.jdbc.OracleDatabaseException: ORA-06550: line 1, column 17:
PLS-00302: component 'DBMS_PICKLER' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Note that the problem occurs when I want to call a function/procedure from Java code. However, when I am calling directly from the PL/SQL console, everything seems correct.
<code>CREATE OR REPLACE FUNCTION TEST(P_NUMBER_LIST IN NUM_ARRAY)
RETURN CLOB
IS
RESULT CLOB;
BEGIN
SELECT JSON_ARRAYAGG(COLUMN_VALUE)
INTO RESULT
FROM (SELECT COLUMN_VALUE FROM TABLE (P_NUMBER_LIST));
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
SELECT TEST(NUM_ARRAY(1, 2, 3)) FROM DUAL;
</code>
<code>CREATE OR REPLACE FUNCTION TEST(P_NUMBER_LIST IN NUM_ARRAY)
RETURN CLOB
IS
RESULT CLOB;
BEGIN
SELECT JSON_ARRAYAGG(COLUMN_VALUE)
INTO RESULT
FROM (SELECT COLUMN_VALUE FROM TABLE (P_NUMBER_LIST));
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
SELECT TEST(NUM_ARRAY(1, 2, 3)) FROM DUAL;
</code>
CREATE OR REPLACE FUNCTION TEST(P_NUMBER_LIST IN NUM_ARRAY)
RETURN CLOB
IS
RESULT CLOB;
BEGIN
SELECT JSON_ARRAYAGG(COLUMN_VALUE)
INTO RESULT
FROM (SELECT COLUMN_VALUE FROM TABLE (P_NUMBER_LIST));
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
SELECT TEST(NUM_ARRAY(1, 2, 3)) FROM DUAL;
Below, I am adding Java code that calls an Oracle function.
<code>@Repository
@RequiredArgsConstructor
public class TestDaoImpl implements TestDao {
private final DataSource dataSource;
@Override
public String test(TestRequest request) {
var caller = new SimpleJdbcCall(dataSource);
caller.withSchemaName("SCHEMA1")
.withFunctionName("TEST")
.declareParameters(
new SqlParameter("P_NUMBER_LIST", Types.ARRAY, "DIM.NUM_ARRAY"),
new SqlOutParameter("RESULT", Types.CLOB)
);
var params = new MapSqlParameterSource()
.addValue("P_NUMBER_LIST", new OracleNumberArray(request.getNumbers()));
var clob = caller.executeFunction(Clob.class, params); // problem occurs in here
return clobToString(clob);
}
}
</code>
<code>@Repository
@RequiredArgsConstructor
public class TestDaoImpl implements TestDao {
private final DataSource dataSource;
@Override
public String test(TestRequest request) {
var caller = new SimpleJdbcCall(dataSource);
caller.withSchemaName("SCHEMA1")
.withFunctionName("TEST")
.declareParameters(
new SqlParameter("P_NUMBER_LIST", Types.ARRAY, "DIM.NUM_ARRAY"),
new SqlOutParameter("RESULT", Types.CLOB)
);
var params = new MapSqlParameterSource()
.addValue("P_NUMBER_LIST", new OracleNumberArray(request.getNumbers()));
var clob = caller.executeFunction(Clob.class, params); // problem occurs in here
return clobToString(clob);
}
}
</code>
@Repository
@RequiredArgsConstructor
public class TestDaoImpl implements TestDao {
private final DataSource dataSource;
@Override
public String test(TestRequest request) {
var caller = new SimpleJdbcCall(dataSource);
caller.withSchemaName("SCHEMA1")
.withFunctionName("TEST")
.declareParameters(
new SqlParameter("P_NUMBER_LIST", Types.ARRAY, "DIM.NUM_ARRAY"),
new SqlOutParameter("RESULT", Types.CLOB)
);
var params = new MapSqlParameterSource()
.addValue("P_NUMBER_LIST", new OracleNumberArray(request.getNumbers()));
var clob = caller.executeFunction(Clob.class, params); // problem occurs in here
return clobToString(clob);
}
}
and created OracleNumberArray class for custom type:
<code>public class OracleNumberArray extends AbstractSqlTypeValue {
private final List<Long> values;
public OracleNumberArray(List<Long> values) {
this.values = isNull(values) ? List.of() : values;
}
@Override
@Deprecated
public Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
con = con.unwrap(oracle.jdbc.OracleConnection.class);
ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
return new ARRAY(desc, con, values.toArray(new Long[0]));
}
}
</code>
<code>public class OracleNumberArray extends AbstractSqlTypeValue {
private final List<Long> values;
public OracleNumberArray(List<Long> values) {
this.values = isNull(values) ? List.of() : values;
}
@Override
@Deprecated
public Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
con = con.unwrap(oracle.jdbc.OracleConnection.class);
ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
return new ARRAY(desc, con, values.toArray(new Long[0]));
}
}
</code>
public class OracleNumberArray extends AbstractSqlTypeValue {
private final List<Long> values;
public OracleNumberArray(List<Long> values) {
this.values = isNull(values) ? List.of() : values;
}
@Override
@Deprecated
public Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
con = con.unwrap(oracle.jdbc.OracleConnection.class);
ArrayDescriptor desc = new ArrayDescriptor(typeName, con);
return new ARRAY(desc, con, values.toArray(new Long[0]));
}
}
Can anyone suggest a solution to this problem? It seems to arise when I attempt to use the DBMS_PICKLER package.
below i am adding full stack trace of an error: https://ctxt.io/2/AACoyLc2EQ