PLSQL works not correct in ajax callback

Question

I have following oracle apex ajax callback process:

DECLARE
    inspection_id number;
    inner_id number;
BEGIN
    inspection_id := apex_application.g_x01;
    inner_id := apex_application.g_x02;

    apex_debug.info('=====================================');
    apex_debug.info('DELETE ENTRY WITH INSPECTION_ID: '||inspection_id||' AND INNER_ID: '||inner_id);
    DELETE FROM CHLI_IMAGES WHERE (INSPECTION_ID = inspection_id AND INNER_ID = inner_id);
    apex_debug.info('ROWS DELETED '|| SQL%ROWCOUNT);

    apex_json.open_object;
    apex_json.write('success', true);
    apex_json.write('message', sqlerrm);
    apex_json.write('INSPECTION_ID', inspection_id);
    apex_json.write('INNER_ID', inner_id);
    apex_json.write('result', true);
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
END;

And it is called by the js here:

apex.server.process("DeleteFromDB", {
                            x01: 0,   //inspection_id
                            x02: 2,   //inner_id
                        }, {
                            success: function (pData) {
                                console.log(pData);
                                if (pData.success === true) {
                                    resolve(true);
                                }
                            },
                            error: function (request, status, error) {
                                console.log(request);
                                resolve(false);
                            }
                        });

The really weired things is that it does not work as expected. This code deletes not only rows with inespection = 0 & inner_id = 2. It also deletes every other row in table. The two ids comming correctly to the process I cheked it within debugging. The JS snippet is in a async loop, but I also checked within debugging that it runs only once.
The weired thing is that it only works with this static line:

DELETE FROM CHLI_IMAGES WHERE (INSPECTION_ID = 0 AND INNER_ID = 2);

Does oracle apex has a bug or do I overlook something always and always again.

Thanks in advance,
Filip.

That code looks fine, I don't see how that would be a bug in apex. I think you're overlooking something that we cannot see — Koen Lostrie, Oct 14 '22 at 07:23
@KoenLostrie But how is this possible. I see this PLSQL output in debug menu only once. So I am pretty sure that this process runs only once. And since the process runs with the correct values it has nothing to do with my js code anymore. I uploaded the complete plsql code. I completly lost. Do you have an idea what could cause this? — Filip Degenhart, Oct 14 '22 at 07:33
You have apex_debug lines in the code. What is the output of those lines and what is the expected output. Can you add that to your question ? — Koen Lostrie, Oct 14 '22 at 08:18
From the behaviour you are describing, the values for the bind variable(s) is/are null. I'd put an IF clause around the DELETE statement to only run if both are not null or do an ```INSPECTION_ID = NVL(inspection_id,-1)``` (same for 2nd col) to prevent any unwanted deletes — Koen Lostrie, Oct 14 '22 at 08:23
I don't know Apex or Ajax, but surely `WHERE (INSPECTION_ID = inspection_id AND INNER_ID = inner_id)` has to be true for every row where inspection_id and inner_id are not null? I suggest using different names for the local variables, or (if Apex allows it) naming the block and prefixing the variables with the block name. — William Robertson, Oct 15 '22 at 09:57
@WilliamRobertson omg. Thank you. Yes that was correct. Wanna post a answer so I can mark it as correct? — Filip Degenhart, Oct 17 '22 at 14:52

score 2 · Accepted Answer · answered Oct 17 '22 at 21:05

The problem is here:

DELETE FROM CHLI_IMAGES WHERE (INSPECTION_ID = inspection_id AND INNER_ID = inner_id);

As the language is case-insensitive, that just means inspection_id and inner_id are equal to themselves, i.e. not null. Give the variables different names, for example:

declare
    l_inspection_id number;
    l_inner_id number;
begin
    l_inspection_id := apex_application.g_x01;
    l_inner_id := apex_application.g_x02;

    apex_debug.info('=====================================');
    apex_debug.info('DELETE ENTRY WITH INSPECTION_ID: '|| l_inspection_id ||' AND INNER_ID: '||l_inner_id);
    delete chli_images i where i.inspection_id = l_inspection_id and i.inner_id = l_inner_id;
    ...

PLSQL works not correct in ajax callback

1 Answers1