Issue with Null Handling in Oracle SQL Function Called via JPA/Hibernate #3590
Description
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.