Calling OracleDB procedure with table type parameter from Java -- DB always receives nulls instead of values

Question

For some reason, DB doesn't receive the values it's given via a table parameter. It sees correct count of rows in the table, and also the given count of columns is correct (else I get an error for mismatch), and yet values themselves are null.

DB version (SELECT * FROM V$VERSION):

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Tested with oracle drivers ojdbc6 (version 11.2.0.4), ojdbc7 (version 11.2.0.4), ojdbc7 (version 12.1.0.2).

This is signature of the DB procedure:

Procedure Send_Message_Test (
    i_Receiver_List_Users_Tbl    In Receiver_List_Users_Tbl
);

the types:

CREATE OR REPLACE Type Receiver_List_Users_Rt Force As Object (
  User_Id Varchar2(30 Char)
)
/

CREATE OR REPLACE Type Receiver_List_Users_Tbl Is Table Of Receiver_List_Users_Rt

This is minimal complete Java Spring Boot application to invoke it:

Pom.xml

<?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 http://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>1.5.14.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.test</groupId>
    <artifactId>test</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>test</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </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>
            </plugin>
        </plugins>
    </build>

</project>

src/main/resources/application.properties

spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@192.168.1.233:1521/sis1
spring.datasource.username=<omitted>
spring.datasource.password=<omitted>
spring.datasource.tomcat.test-while-idle=true
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.test-on-return=false
spring.datasource.tomcat.validation-query=select 1 from dual
spring.datasource.tomcat.max-active=100
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.remove-abandoned-timeout=60
spring.datasource.tomcat.remove-abandoned=true
spring.datasource.tomcat.log-abandoned=true

src/main/java/com/test/test/TestApplication.java

package com.test.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.datasource.DataSourceUtils;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

@SpringBootApplication
public class TestApplication implements ApplicationRunner {

    public static void main(String[] args) {
        SpringApplication.run(TestApplication.class, args);
    }

    @Autowired
    DataSource dataSource;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        Connection conn = DataSourceUtils.getConnection(dataSource);
        CallableStatement callStmt = null;

        Statement alterDateFormatStmt = conn.createStatement();
        alterDateFormatStmt.execute("alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD'");
        alterDateFormatStmt.close();

        // create PLSQL procedure statement
        String stmStr = "{call Notification_Manage_v2.Send_Message_Test (?)}";

        // create Oracle statement and set parameters
        callStmt = conn.prepareCall(stmStr);

        StructDescriptor recordDescriptor = StructDescriptor.createDescriptor("RECEIVER_LIST_USERS_RT",
                callStmt.getConnection());
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("RECEIVER_LIST_USERS_TBL",
                callStmt.getConnection());
        callStmt.setObject(1, new ARRAY(arrayDescriptor, callStmt.getConnection(),
                new STRUCT[] { new STRUCT(recordDescriptor, callStmt.getConnection(), new Object[] { "test" }) }));

        callStmt.execute();
    }

}

However, after running this, this is what I see in Transactions table Param_values column:

i_Receiver_List_Users_Tbl => Receiver_List_Users_Tbl(Receiver_List_Users_Rt ())

Any insight would be much appreciated.

---

This is how from DB side the param_values column is formed:

CREATE OR REPLACE Type Receiver_List_Users_Rt Force As Object (User_Id Varchar2(30 Char));
CREATE OR REPLACE Type Receiver_List_Users_Tbl Is Table Of Receiver_List_Users_Rt;


Procedure Send_Message_Test (i_Receiver_List_Users_Tbl In Receiver_List_Users_Tbl
                            ) Is
  --
  tbl_Receiver_List_Users    Receiver_List_Users_Tbl := Receiver_List_Users_Tbl();
  v_Param_Receiver_List_Users Varchar2(3000);
  --
Begin
  --
  For e_Usr In (Select t_Receiver_List_Users.User_Id User_Id
                From Table(i_Receiver_List_Users_Tbl) t_Receiver_List_Users
               ) Loop
    --
    v_Param_Receiver_List_Users := v_Param_Receiver_List_Users ||
                       Case When v_Param_Receiver_List_Users Is Not Null Then ', ' End||
                       'Receiver_List_Users_Rt ('||e_Usr.User_Id||')';
    --
  End Loop;
  --
  If v_Param_Receiver_List_Users Is Not Null Then
    v_Param_Receiver_List_Users := 'Receiver_List_Users_Tbl('||v_Param_Receiver_List_Users||')';
  End If;
  --
  --
  dbms_output.put_Line('i_Receiver_List_Users_Tbl => '||v_Param_Receiver_List_Users); -- !!!!!!!! no values receive
  --
  --
End;

Answer 1

My guess is that you are not correctly creating nor populating the IN parameter of the PL/SQL stored procedure. Below is a code snippet taking parts of the code you posted ("TestApplication.java") and adding code that I hope will solve your problem.

EDITED

Connection conn = DataSourceUtils.getConnection(dataSource); // your code
Object[] attributes = new Object[1];
attributes[0] = "Test";
java.sql.Struct obj = conn.createStruct("Receiver_List_Users_Rt", attributes);
Object[] elems = new Object[1];
elems[0] = obj;
oracle.jdbc.OracleConnection oraConn = (oracle.jdbc.OracleConnection) conn;
java.sql.Array objs = oraConn.createARRAY("Receiver_List_Users_Tbl", elems);
callStmt.setArray(1, objs);
callStmt.execute(); // your code