Error PLS-00302: 'DBMS_PICKLER' Component Must Be Declared When Using Custom Type in Oracle

I'm encountering an issue with the DBMS_PICKLER package in Oracle. After creating a custom type

create type     NUM_ARRAY is varray(100) of NUMBER;

I'm receiving the following exception:

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.

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 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.

@Repository
@RequiredArgsConstructor
public class TestDaoImpl implements TestDao {
    private final DataSource dataSource;

    @Override
    public String test(TestRequest request) {
        var caller = new SimpleJdbcCall(dataSource);


        caller.withSchemaName("DIM")
                .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:

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]));
    }

}

pom.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>io.programming</groupId>
    <artifactId>problems</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>problems</name>
    <description>Solutions for Programming Problems</description>
    <properties>
        <java.version>21</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc11</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.3.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

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

Solution

As seen in this blog post, this can happen if the user you are connected as has an object called SYS.

That causes a variation on this similar PLS-00302 error; in that case the user has an object with the same name as their own schema.

Here the object is a different schema, but because the JDBC driver is apparently calling SYS.DBMS_PICKLER.something the same kind of name clash is occurring.

The name resolution means it's seeing the current user's SYS object in preference to the schema, because of step 1a - "Search the current schema for an object whose name matches the first piece." It doesn't get to 1b (public synonynm) or 1c (schema).

It then tries to resolve the rest of the name using that SYS object - which it can't do, so it causes the error you are seeing.

To resolve this you will need to remove or change the name of your SYS object. In general, avoid having any objects which match any schema names, as well as key words, reserved words, etc.