5

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;
9
  • However, after running this, this is what I see in Transactions table Param_values column What am I missing? Transactions table and Param_values do not appear anywhere in the code you posted. Commented Jun 8, 2019 at 15:19
  • @Abra Hmm I suppose I erroneously assumed it's a default/system table. In any case, the person who does work with DB complains that she's receiving nulls from my java end, and I was told to use that Transactions table to see for myself Commented Jun 8, 2019 at 15:31
  • In either case, I'd expect Transactions shouldn'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 Commented Jun 8, 2019 at 15:39
  • What java version are you using? Are you using Oracle's JDBC driver? If so, which version? If not then which JDBC driver are you using? Commented Jun 8, 2019 at 15:48
  • edited driver versions Commented Jun 10, 2019 at 7:28

2 Answers 2

2
+25

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
Sign up to request clarification or add additional context in comments.

4 Comments

unfortunately conn.createArrayOf throws java.sql.SQLException: Unsupported feature
using your way of creating struct and my old way of creating array doesn't solve original problem
@Coderino_Javarino regarding your comment: conn.createArrayOf throws java.sql.SQLException: Unsupported feature It's because you are using an old JDBC driver. (See my last comment to your original question.)
I get same exception even with most newest version 19.3 driver ojdbc8 from oracle.com/technetwork/database/application-development/jdbc/…
0

Unsatisfying closure, but DB personnel tore down the environment, made a new one, and it started working. So the issue wasn't with java or driver side. Whatever went wrong with DB remains an unrecoverable mystery

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.