NamedStoredProcedureQuery: Cannot resolve named output with no input parameters

[HVollmilch]

Hi there!

I'm not quite sure, but i think i experience an issue using NamedStoredProcedureQuery, calling stored procedures that only have output but no input parameters.

I follwed the example of [DATAJPA-707], defining a NamedStoredProcedureQuery on my Entity

@NamedStoredProcedureQuery(
	name = "MyEntity.example", procedureName = "example",
	parameters = {
		@StoredProcedureParameter(mode = ParameterMode.OUT, name = "example1", type = Integer.class),
		@StoredProcedureParameter(mode = ParameterMode.OUT, name = "example2", type = Integer.class)
	})

Referencing it in my Repository

@Procedure(procedureName = "MyEntity.example")
Map<String, Integer> example(); 

When i call this method at runtime, I get an exception:

 Positional parameter [1] is not registered with this procedure call   (ProcedureParameterMetadata:141)

Climbing up the stack, it seems that useNamedParameters of StoredProcedureJpaQuery is set to false. This is calclulated on startup and is false, because my Repository method has no (named input) parameters. If i set useNamedParameters to true using the debugger, my procedure call works fine. So i guess there should be another option or logic behind this, but may i miss something or this is not an issue in current versions anymore .

Im using spring-data-jpa 2.3.1

Thanks!
Denis

[gregturn]

We recently rolled out some updates to our support for stored procedures. Can you see if this problem still exists with the latest snapshots?

[HVollmilch]

Hi!
No, problem still exists in Version 2.6.0-M1 and 2.6.0-SNAPSHOT. Only error message changed to "OUT/INOUT parameter not available: 1".

[pathania29]

Hi!
No, problem still exists in Version 2.6.0-M1 and 2.6.0-SNAPSHOT. Only error message changed to "OUT/INOUT parameter not available: 1".

For me it works if i make one parameter as ParameterMode.INOUT and then when calling Stored Procedure pass that value as null.
@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "example1", type = Integer.class),
e.g, Map<String, Integer> example(null)
Getting same issue. Any update on same.

[sridhark27]

OpenFindingDTO.java

@NamedStoredProcedureQuery(name = "OpenFindingDTO.NamedQuery_GetOpenFindingsProcedure",
    procedureName = "PRC_OPEN_FINDINGS",
    resultClasses = {OpenFindingDTO.class},
    parameters = {
    		
    		@StoredProcedureParameter(name = "C_OUTPUT", mode = ParameterMode.REF_CURSOR, type = ResultSet.class)
    })

@Entity
@AllArgsConstructor
public class OpenFindingDTO implements Serializable{
	
	private static final long serialVersionUID = 1L;
	
	public static final String NamedQuery_GetOpenFindingsProcedure = "fetchOpenFindings";

	@Id
	@Column(name="findingID")
	private Integer findingID;
	
	@Column(name="reviewID")
	private Integer reviewID;
	
	@Column(name="reviewIDStr")
	private String reviewIDStr;

	public Integer getFindingID() {
		return findingID;
	}

	public void setFindingID(Integer findingID) {
		this.findingID = findingID;
	}

	public Integer getReviewID() {
		return reviewID;
	}

	public void setReviewID(Integer reviewID) {
		this.reviewID = reviewID;
	}

	public String getReviewIDStr() {
		return reviewIDStr;
	}

	public void setReviewIDStr(String reviewIDStr) {
		this.reviewIDStr = reviewIDStr;
	}
	
			
}

OpenFindingRepository.java

@Repository
public interface OpenFindingRepository extends CrudRepository<OpenFindingDTO, Integer> {
	
	@Procedure(name="OpenFindingDTO.NamedQuery_GetOpenFindingsProcedure")
	List<OpenFindingDTO> getOpenFindings();

}

ReportServiceImpl.java

@Service
@Slf4j
public class ReportServiceImpl implements ReportService {

	@Autowired
	ToolUtility toolUtil;
		
	@Autowired
	OpenFindingRepository openFindingRepository;
	
	public Object createFile() {
		log.info("Entered into ReportServiceImpl .createFile()");
		Object obj=null;
		try {
			
			List<OpenFindingDTO> listOpenFindingDTO = openFindingRepository.getOpenFindings();
						
		} catch (SecurityException | IOException e) {
			
			e.printStackTrace();
		}
		return obj;
	}
	@Override
	public String generateReport() {
		createFile();
		return "done";
	}
}

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 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>2.5.6</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
  <groupId>com.xxx.rt.report</groupId>
  <artifactId>rt-report-service</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>rt-report-service</name>
  <description>Data check report</description>
  <properties>
		<java.version>11</java.version>
		<spring-cloud.version>2020.0.4</spring-cloud.version>
		<jacoco.version>0.8.6</jacoco.version>
		<sonar.java.coveragePlugin>jacoco</sonar.java.coveragePlugin>
		<sonar.dynamicAnalysis>reuseReports</sonar.dynamicAnalysis>
		<sonar.jacoco.reportPath>${project.basedir}/../target/jacoco.exec</sonar.jacoco.reportPath>
		<sonar.language>java</sonar.language>
	</properties>
  <dependencies>
  		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger2</artifactId>
			<version>2.9.2</version>
		</dependency>
  		
  		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger-ui</artifactId>
			<version>2.9.2</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-redis</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-bootstrap</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-config</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-zipkin</artifactId>
			<version>2.2.8.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-sleuth</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-feign</artifactId>
			<version>1.4.7.RELEASE</version>
		</dependency>
  		<dependency>
	  		<groupId>com.oracle.database.jdbc</groupId>
	  		<artifactId>ojdbc8</artifactId>
  		</dependency>
  		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-stream</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-stream-binder-rabbit</artifactId>
		</dependency> 
		
		
		<dependency>
		    <groupId>javax.activation</groupId>
		    <artifactId>activation</artifactId>
		    <version>1.1.1</version>
		</dependency>
		
		<dependency>
		    <groupId>org.apache.commons</groupId>
		    <artifactId>commons-collections4</artifactId>
		    <version>4.3</version>
		</dependency>
		
		<dependency>
		    <groupId>org.apache.commons</groupId>
		    <artifactId>commons-compress</artifactId>
		    <version>1.18</version>
		</dependency>
		
		<dependency>
		    <groupId>commons-logging</groupId>
		    <artifactId>commons-logging</artifactId>
		    <version>1.2</version>
		</dependency>
		
		<dependency>
		    <groupId>org.apache.commons</groupId>
		    <artifactId>commons-math3</artifactId>
		    <version>3.6.1</version>
		</dependency>
		
		<dependency>
		    <groupId>com.github.virtuald</groupId>
		    <artifactId>curvesapi</artifactId>
		    <version>1.06</version>
		</dependency>
		
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>4.1.0</version>
		</dependency>
		
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-excelant</artifactId>
		    <version>4.1.0</version>
		</dependency>
		
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-scratchpad</artifactId>
		    <version>4.1.0</version>
		</dependency>
		
		
		
  </dependencies>
  
  <dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.springframework.cloud</groupId>
				<artifactId>spring-cloud-dependencies</artifactId>
				<version>${spring-cloud.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>
</project>

PRC_OPEN_FINDINGS.sql

CREATE PROCEDURE PRC_OPEN_FINDINGS ( C_OUTPUT OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN C_OUTPUT FOR

      select A.FINDING_ID_PK as findingID,
                B.REVIEW_ID_PK as reviewID,
                B.REVIEW_ID_STR as reviewIDStr

      FROM hellotable;
   END;

As it is cursor, I should take mode = ParameterMode.REF_CURSOR

[mp911de]

This might be resolved via #1759 that used binding details from method parameters and not from @NamedStoredProcedureQuery. Care to retest against the latest Spring Data snapshots?

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.