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;
Transactionstable andParam_valuesdo not appear anywhere in the code you posted.Transactionstable to see for myselfTransactionsshouldn't be the culprit, for it shows correct expected parameter values when the procedure is invoked with sql. Nevertheless, I'll ask for its definition on monday when I come back to work