I have a problem with a plsql function which is not returning information in data. The function has to fetch all the notifications from the NOTIFICATIONS table but which do not have any insertion in the USER_NOTIFICATIONS table.
The USER_NOTIFICATIONS table has entries where in each row there is a user_id, of the user who "read" a notification with a particular NOTIFICATION_ID. What I want is for it to bring me the notifications that have not been read by the user.
This is the function
FUNCTION NEW_NOTIFICATION(
user_id IN NUMBER,
fecha_val IN VARCHAR2,
start_position IN NUMBER,
end_position IN NUMBER
)
RETURN CLOB
AS
l_result1 CLOB;
l_result2 CLOB := '';
l_result3 CLOB;
l_result_size NUMBER;
l_count NUMBER;
l_row_check NUMBER := start_position;
l_row_number NUMBER := 0;
l_json_record VARCHAR2(4000);
CURSOR c_notifications_new IS
SELECT
'{ "ID": "' || ID || '", "RECIPIENTS": "' || RECIPIENTS || '", "MESSAGE_TITLE": "' || MESSAGE_TITLE || '", "MESSAGE_BODY": "' || MESSAGE_BODY || '", "MULTIMEDIA_ID": "' || MULTIMEDIA_ID || '", "NOTIFICATION_DATE_FROM": "' || TO_CHAR(NOTIFICATION_DATE_FROM, 'DD/MM/YYYY HH24:MI:SS') || '", "NOTIFICATION_DATE_TO": "' || TO_CHAR(NOTIFICATION_DATE_TO, 'DD/MM/YYYY HH24:MI:SS') || '", "SEND_BY_EMAIL": "' || SEND_BY_EMAIL || '", "CREATED_AT": "' || TO_CHAR(CREATED_AT, 'DD/MM/YYYY HH24:MI:SS') || '"}' AS json_record
FROM NOTIFICATIONS N
WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
AND NOT EXISTS (
SELECT 1
FROM USER_NOTIFICATIONS UN
WHERE UN.USER_ID = user_id AND UN.NOTIFICATION_ID = N.ID
)
AND N.DELETED_AT IS NULL
ORDER BY N.CREATED_AT DESC;
BEGIN
SELECT COUNT(*) INTO l_count
FROM NOTIFICATIONS N
WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
AND NOT EXISTS (
SELECT 1
FROM USER_NOTIFICATIONS UN
WHERE UN.USER_ID = user_id AND UN.NOTIFICATION_ID = N.ID
)
AND N.DELETED_AT IS NULL;
FOR r in c_notifications_new LOOP
IF l_row_check + 1 >= start_position AND l_row_check + 1 <= end_position THEN
l_json_record := r.json_record;
l_result_size := DBMS_LOB.GETLENGTH(l_result1 || l_result2 || l_json_record || '}');
IF l_result_size > 10000 THEN
EXIT;
ELSE
IF l_row_check != 0 THEN
l_result2 := l_result2 || ', ';
END IF;
l_result2 := l_result2 || l_json_record;
l_row_check := l_row_check + 1;
l_row_number := l_row_number + 1;
END IF;
END IF;
END LOOP;
l_result1 := '{ "count": ' || l_count || ',"rows": ' || l_row_number || ', "data": [';
l_result3 := l_result1 || l_result2 || ']}';
RETURN l_result
And this functions works
FUNCTION NEW_NOTIFICATION(
user_id IN NUMBER,
fecha_val IN VARCHAR2,
start_position IN NUMBER,
end_position IN NUMBER
)
RETURN CLOB
AS
l_result1 CLOB;
l_result2 CLOB := '';
l_result3 CLOB;
l_result_size NUMBER;
l_count NUMBER;
l_row_check NUMBER := start_position;
l_row_number NUMBER := 0;
l_json_record VARCHAR2(4000);
CURSOR c_notifications_new IS
SELECT
'{ "ID": "' || ID || '", "RECIPIENTS": "' || RECIPIENTS || '", "MESSAGE_TITLE": "' || MESSAGE_TITLE || '", "MESSAGE_BODY": "' || MESSAGE_BODY || '", "MULTIMEDIA_ID": "' || MULTIMEDIA_ID || '", "NOTIFICATION_DATE_FROM": "' || TO_CHAR(NOTIFICATION_DATE_FROM, 'DD/MM/YYYY HH24:MI:SS') || '", "NOTIFICATION_DATE_TO": "' || TO_CHAR(NOTIFICATION_DATE_TO, 'DD/MM/YYYY HH24:MI:SS') || '", "SEND_BY_EMAIL": "' || SEND_BY_EMAIL || '", "CREATED_AT": "' || TO_CHAR(CREATED_AT, 'DD/MM/YYYY HH24:MI:SS') || '"}' AS json_record
FROM NOTIFICATIONS N
WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
AND N.DELETED_AT IS NULL
ORDER BY N.CREATED_AT DESC;
BEGIN
SELECT COUNT(*) INTO l_count
FROM NOTIFICATIONS N
WHERE TO_DATE(fecha_val, 'DD/MM/YYYY') BETWEEN N.NOTIFICATION_DATE_FROM AND N.NOTIFICATION_DATE_TO
AND N.DELETED_AT IS NULL;
FOR r in c_notifications_new LOOP
IF l_row_check + 1 >= start_position AND l_row_check + 1 <= end_position THEN
l_json_record := r.json_record;
l_result_size := DBMS_LOB.GETLENGTH(l_result1 || l_result2 || l_json_record || '}');
IF l_result_size > 10000 THEN
EXIT;
ELSE
IF l_row_check != 0 THEN
l_result2 := l_result2 || ', ';
END IF;
l_result2 := l_result2 || l_json_record;
l_row_check := l_row_check + 1;
l_row_number := l_row_number + 1;
END IF;
END IF;
END LOOP;
l_result1 := '{ "count": ' || l_count || ',"rows": ' || l_row_number || ', "data": [';
l_result3 := l_result1 || l_result2 || ']}';
RETURN l_result
So it looks like the problem is here:
AND NOT EXISTS (
SELECT 1
FROM USER_NOTIFICATIONS UN
WHERE UN.USER_ID = user_id AND UN.NOTIFICATION_ID = N.ID
)
In other words, this condition is responsible for selecting only rows from the NOTIFICATIONS table that do not have a match in the USER_NOTIFICATIONS table for the specific user (user_id). This implies that the notification has not yet been read by that user, since there is no entry in USER_NOTIFICATIONS that matches the combination of USER_ID and NOTIFICATION_ID.
By the way I'm using Oracle DB version 11
Summary: Do not name PL/SQL variable with the same name as an SQL column name.
AND NOT EXISTS (
SELECT 1
FROM USER_NOTIFICATIONS UN
WHERE UN.USER_ID = user_id AND UN.NOTIFICATION_ID = N.ID
)
Contains the filter condition:
WHERE UN.USER_ID = user_id
In this case, user_id refers to the local SQL scope and the PL/SQL variable user_id is shadowed by the column value so you are effectively doing:
WHERE UN.USER_ID = UN.user_id
which is (almost) the same as 1 = 1 so all rows are going to be filtered out if the other filter matches.
Change the function signature to use something other than the column name (for example, you could adopt the convention of using an i_ prefix for IN parameters):
FUNCTION NEW_NOTIFICATION(
i_user_id IN NUMBER,
i_fecha_val IN VARCHAR2,
i_start_position IN NUMBER,
i_end_position IN NUMBER
)
and then change the rest of the code to use the new identifier for the PL/SQL variables and do not try to use the same identifier for PL/SQL variables and SQL columns.