Issue with Null Handling in Oracle SQL Function Called via JPA/Hibernate

[RockyCott]

I’m encountering an issue when calling an Oracle SQL function from a Spring Boot application using JPA/Hibernate. The problem occurs when the function includes a null check, and it results in an exception even though valid values are being passed.

Here’s the Oracle function I’m using:

CREATE OR REPLACE FUNCTION MY_SCHEMA.MY_FUNCTION (
	P_PARAM NUMBER
) RETURN CLOB SQL_MACRO AS
	V_SQL CLOB;
BEGIN
	RETURN Q'{
		SELECT P_PARAM FROM DUAL
	}';
END;

This function works correctly and returns the parameter value (e.g., 104) when called from the repository like this:

@Query(value = "SELECT * FROM MY_SCHEMA.MY_FUNCTION(:param) ", nativeQuery = true)
List<JSONObject> findDataByParam(Long param);

However, when I modify the function to include a null check:

CREATE OR REPLACE FUNCTION MY_SCHEMA.MY_FUNCTION (
	P_PARAM NUMBER
) RETURN CLOB SQL_MACRO AS
BEGIN
	IF P_PARAM IS NULL THEN
		RAISE_APPLICATION_ERROR(-20001, 'Parameter is null');
	END IF;

	RETURN Q'{
		SELECT P_PARAM FROM DUAL
	}';
END;

The function throws an exception indicating that the parameter is null, even though I am passing the same value (104). This issue arises despite the value not being null and should be handled correctly.

Questions

• Could JPA/Hibernate be failing to properly inject the value into the Oracle SQL function, causing it to be perceived as null?
• Is it possible that the presence of the IF statement in the SQL function is leading to unexpected behavior when called from JPA/Hibernate?
• Are there any special configurations or considerations required to ensure that values are correctly injected into SQL functions when using JPA/Hibernate?

Any assistance or insights into resolving this issue would be greatly appreciated.

[christophstrobl]

Thank you for getting in touch. If you'd like us to spend some time investigating, please take the time to provide a complete minimal sample (something that we can unzip or git clone, build, and deploy) that reproduces the problem.

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

[spring-projects-issues]

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.