Error Executing Database Query

[mega_L]

I have a web application written in CF8 with Oracle 11g as the back end. This application has been used very often for more than 6 years. Currently I am moving to CF10 and did only a little modification to the CF codes. When I ran the new application in my test server, everything seems to be working just fine except when it calls an Oracle Package. This action produces an error:  Error Executing Database Query The strange thing is everything work before and after calling the procedure, I tested using cfabort This Oracle Package is still working in production server (CF8)  but not when it is called by CF10. My question is: Is there any changes for CF10 when calling a procedures? or is there any hotfix that I'm not aware of? The code is as follow:   SELECT case trim(to_char(SYSDATE, 'DAY')) WHEN 'MONDAY' then '1' else '2' end AS TodaysDate                 from dual     

            SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) = 

            SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) =         CF codes to Stop the process and email admin                                                             

              Error Executing Database Query shows up when it hit to run cfstoredproc. The codes are exactly the same as in CF8, this template was not modified. Exceptions 14:03:53.053 - Database Exception - in /home/space/users/www/GL/glproc.cfm : line 93     Error Executing Database Query. 

[WolfShade]

I have never used CFSTOREDPROC, so can't really comment on this.  But, I'd check the Adobe CF docs on CFSTOREDPROC and check if anything has changed between CF8 and CF10.

V/r,

^_^

[BKBK]

mega_L wrote:

SELECT case trim(to_char(SYSDATE, 'DAY')) WHEN 'MONDAY' then '1' else '2' end AS TodaysDate                 from dual     

            SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) =

            SELECT Count(other_id) AS NoRecFound     FROM gl_dup_ids_ssns     WHERE Trim(create_date) =         CF codes to Stop the process and email admin                                                            

Surely, that cannot be the actual SQL, as it is obviously invalid.

[mega_L]

I looked at the CF administrator application log and see a better error message: Error Executing Database Query. Executing stored procedures is not allowed Is there a setting in the administrator where I can enable and disable ColdFusion to use CFSTOREPROC? if there is, I can't find it. For BKBK:

That query is actually working because I can see it got run in the debug screen

[mega_L · Accepted solution]

I found the answer! In case someone out there also facing the same issue. In the administrator, Datasource Advance setting, go down and find: Allowed SQL where there are checkboxes for Select, Update,Delete,Insert and one of them is store procedure checkbox. My checkbox was not checked that's why ColdFusion can't call a store procedure. I Checked and saved it and I'm good to go.

[BKBK]

Curious solution! Thanks, Mega_L, for sharing it with us.