Oracle PL/SQL CASE UPDATE statement not working when called from ColdFusion

Question

This is a strange one. I have a simple toggle update statement that when ran in TOAD works just fine (the test record's ID is 10000244999201)...

update myTable set
hasbeenread = case when (hasbeenread = 0) then 1 else 0 end
where id = 10000244999201;

This update statement is also in a package/procedure that can be called from ColdFusion and when it's called this way it does not work. The procedure runs and I get success back but the update doesn't toggle the hasbeenread column.

procedure toggleRead(p_webSession number, p_data lib.jsonclob, result out lib.jsonclob) is

    ws websession%rowtype := lib.getWebSession(p_webSession);
    pin lib.paramArray := lib.jsonToArray(p_data);

begin
    --(1) Do perm checks here
    --(2) Do data validation here
    --(3) Code your process here
    --(4) Send back a proper JSON/XML response.

    delete from debuglog where title = 'gtest';
    insert into debuglog ( username, seqno, title, cfd01, insertdate )
    values ( 'GADMIN', 555, 'gtest', pin('id'), sysdate );
    commit;

    update myTable set
        hasbeenread = case when (hasbeenread = 0) then 1 else 0 end
    where id = pin('id');

    result := lib.response(true,null,null);

    exception
        when others then
            err.logAndEmailError( ws.fullName, gbody_version, 'toggleRead - p_data: '||p_data||', SQLERRM: '||sqlerrm );

end toggleRead;

I have verified the pin('id') value by looking at the debug log that happens right before the update, it is the correct id. I have even replaced pin('id') and hardcoded 10000244999201 into the where clause, that still didn't work. I tried putting a commit; after the update (which I shouldn't have to do because ColdFusion will commit when it comes back since I'm not using a cftransaction tag around it), that still didn't work. But when I remove the CASE statement and hardocde it to hasbeenread = 0 or hasbeenread = 1 it then works. So the problems looks like it has to do with the CASE statement. But like I said, it works fine when I run that update in TOAD. What am I missing?

Thanks!

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

ColdFusion version: 11,0,0,289974

UPDATE: Here is the ColdFusion call...

<cfstoredproc procedure="lib.getJson" datasource="#session.sv.ds#" returncode="no">
    <cfprocparam type="in" cfsqltype="cf_sql_decimal" variable="webSession_in" value="#session.sv.csid#">
    <cfprocparam type="in" cfsqltype="cf_sql_clob" variable="data" value="#z.data#">
    <!---
    How to get back a clob
    Here they say to use CF_SQL_LONGVARCHAR
    http://stackoverflow.com/questions/11053539/getting-clob-data-from-coldfusion-8
    <cfprocparam type="out" cfsqltype="cf_sql_longvarchar" variable="result">
    But instead we use CF_SQL_CLOB but then do the ArrayToList part you see below.
    --->
    <cfprocparam type="out" cfsqltype="cf_sql_clob" variable="z.result">
</cfstoredproc>
<cfif notnull(z.result)>
    <cfset z.result = ArrayToList(z.result,"")>
</cfif>
<cfreturn z.result />

UPDATE: When I call the proc from TOAD it works, the update works and the column value toggles...

declare
    r clob;
begin
    c3.toggleread(146992,'{"ID":10000244999201}',r);
    dbms_output.put_line(r);
end;
/

Answer 1

One common way it can fire twice is the use of a custom tag. For example if you put your stored proc in a custom tag and called it like this:

<Cf_toggleuser z="#mydata#"/>

Custom tags have 2 "execution modes" - start and end. They are referenced as attributes of the "thistag" scope within the contents of the tag. The tag (by design) runs two times - a start and and end. This is so you can run them with content inbetween - commonly done for altering layout or display content as in:

<cf_layout>

...some content here

</cf_layout>

But it also means if you intend to ONLY run the contents of the tag one time you need to include a check and only run it for one of the modes as in:

<cfif thistag.executionmode IS 'Start'>
...run the procedure
</cfif>

This may not be your problem but it is one nuance that occurs that is easy to miss. In your case the results were an error in data.

NOTE: you can also pull out the final slash in your tag (or the end tag) to cause it to execute only once. To illustrate:

This call only executes one time:

<Cf_toggleuser z="#mydata#">

While this call executes twice:

<Cf_toggleuser z="#mydata#"/>