Copy link to clipboard
Copied
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.
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.
Copy link to clipboard
Copied
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,
^_^
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Curious solution! Thanks, Mega_L, for sharing it with us.