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

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

this unusual one. have simple toggle update statement when ran in toad works fine (the test record's id 10000244999201)...

update mytable set hasbeenread = case when (hasbeenread = 0) 1 else 0 end id = 10000244999201;

this update statement in package/procedure can called coldfusion , when it's called way not work. procedure runs , success update doesn't toggle hasbeenread column.

procedure toggleread(p_websession number, p_data lib.jsonclob, result out lib.jsonclob) ws websession%rowtype := lib.getwebsession(p_websession); pin lib.paramarray := lib.jsontoarray(p_data); begin --(1) perm checks here --(2) info validation here --(3) code process here --(4) send proper json/xml response. delete debuglog title = 'gtest'; insert debuglog ( username, seqno, title, cfd01, insertdate ) values ( 'gadmin', 555, 'gtest', pin('id'), sysdate ); commit; update mytable set hasbeenread = case when (hasbeenread = 0) 1 else 0 end id = pin('id'); result := lib.response(true,null,null); exception when others err.logandemailerror( ws.fullname, gbody_version, 'toggleread - p_data: '||p_data||', sqlerrm: '||sqlerrm ); end toggleread;

i have verified pin('id') value looking @ debug log happens right before update, right id. have replaced pin('id') , hardcoded 10000244999201 clause, still didn't work. tried putting commit; after update (which shouldn't have because coldfusion commit when comes since i'm not using cftransaction tag around it), still didn't work. when remove case statement , hardocde hasbeenread = 0 or hasbeenread = 1 works. problems looks has case statement. said, works fine when run update in toad. missing?

thanks!

oracle database 11g enterprise edition release 11.2.0.2.0 - 64bit production

coldfusion version: 11,0,0,289974

update: here 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 clob here utilize cf_sql_longvarchar http://stackoverflow.com/questions/11053539/getting-clob-data-from-coldfusion-8 <cfprocparam type="out" cfsqltype="cf_sql_longvarchar" variable="result"> instead utilize cf_sql_clob arraytolist part 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 phone call proc toad works, update works , column value toggles...

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

please don't vote downwards can't explain in comment :)

one mutual way can fire twice utilize of custom tag. illustration if set stored proc in custom tag , called this:

<cf_toggleuser z="#mydata#"/>

custom tags have 2 "execution modes" - start , end. referenced attributes of "thistag" scope within contents of tag. tag (by design) runs 2 times - start , and end. can run them content inbetween - commonly done altering layout or display content in:

<cf_layout> ...some content here </cf_layout>

but means if intend run contents of tag 1 time need include check , run 1 of modes in:

<cfif thistag.executionmode 'start'> ...run procedure </cfif>

this may not problem 1 nuance occurs easy miss. in case results error in data.

note: can pull out final slash in tag (or end tag) cause execute once. illustrate:

this phone call executes 1 time:

<cf_toggleuser z="#mydata#">

while phone call executes twice:

<cf_toggleuser z="#mydata#"/>

oracle plsql coldfusion