• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Coldfusion 2018 - Error Executing Database Query

Explorer ,
Apr 24, 2019 Apr 24, 2019

Copy link to clipboard

Copied

Hello,

   I am using Coldfusion 2018 with Oracle Database. One of my pages in the application gives me an error as shown below

How can this be resolved ?   I am not getting the same error on Test server where the same version of Cold fusion 2018 is installed.

Thanks

IQ

Error Executing Database Query.

  ORA-01858: a non-numeric character was found where a numeric was expected   
  The error occurred in C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 134
Called from C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 1
Called from C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 134
Called from C:/inetpub/wwwroot/TOPS/ValidateConsumptionList.cfm: line 1
132 :        and dd.data_date = TO_DATE(<cfqueryparam 133 :                                   value="#time1#" 134 :                                   cfsqltype="VARCHAR" 135 :                           maxlength="28">,'YYYY/MM/DD hh24') 136 :        WHERE lu.IPN in (304305,304350,304354,304352,304309,304310,304311,304312, 304313, 304314)

and dd.data_date = TO_DATE(<cfqueryparam

133 : value="#time1#"

134 : cfsqltype="VARCHAR"

135 : maxlength="28">,'YYYY/MM/DD hh24')

136 : WHERE lu.IPN in (304305,304350,304354,304352,304309,304310,304311,304312, 304313, 304314)

Views

6.7K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , May 14, 2019 May 14, 2019

We're getting there. This error says there is a problem with a column name.

Check whether there is a column name having an illegal character. Did you perhaps use a column name that is a reserved word in Oracle?

Votes

Translate

Translate
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

Data type is Date

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

Thanks for your reply. To rule options out, what happens when you test with:

dd.data_date = TO_DATE(<cfqueryparam

value="#time1#"

cfsqltype="VARCHAR"

maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN')

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

I get

Error Executing Database Query.            

            ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

IQ1  wrote

I get

Error Executing Database Query.            

            ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

The error is no longer at line 134?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

yes the line number of error is different,

Charlie, is there anyway for us to download or upgrade to Enterprise edition instead of Standard edition ?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

IQ1  wrote

yes the line number of error is different

Could you then show us the corresponding code?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

This is the code being used

<cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
    BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#, '#Session.UserName#', #multi_report_def_id#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#DateFormat(dateto, "dd-mm-yyyy")# #TimeFormat(dateto, "HH:mm:ss")#', 'DD-MM-YYYY HH24:MI:SS'), #period#); END;

 
</cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

What happens when you replace #period# with 'AMERICAN'?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

still the same result when it is AMERICAN

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

Suggestion (note the nn in the timeFormat mask):

<cfset toDateTime=dateFormat(dateto, "dd-mm-yyyy") & " " & timeFormat(dateto, "HH:nn:ss")>

<cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#, '#Session.UserName#', #multi_report_def_id#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN'); END;

<cfquery

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

Thanks, I tried your suggestion but get the following error.

Error Executing Database Query.

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1  
 
The error occurred in C:/inetpub/wwwroot/TOPS_test/MultiSectionReportsResults.cfm: line 74

72 : <cfset toDateTime=dateFormat(dateto, "dd-mm-yyyy") & " " & timeFormat(dateto, "HH:nn:ss")>
73 : <cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
74 : BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#, '#Session.UserName#', #multi_report_def_id#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN'); END;
75 : </cfquery>
76 :

SQLSTATE   65000

DATASOURCE   histt

VENDORERRORCODE   6502

SQL    BEGIN dbo.multi_report_pkg.run_multi_report(4522, 'fsuresh2', 7, TO_DATE( (param 1) ,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('08-05-2019 23:59:00', 'DD-MM-YYYY HH24:MI:SS'), 'AMERICAN'); END; 

Resources: •Check the ColdFusion documentation to verify that you are using the correct syntax.

•Search the Knowledge Base to find a solution to your problem.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

If these efforts with bkbk don't work out, let's go back to an earlier point you made: the oracle dsn you have differs between prod and test. This could easily be the root cause of the issue, now that we know (you say) that the values of the vars are the same on both (and so the sql should be identical).

You said that you could not see an oracle driver to pick in prod, like you could in test. So I suspect your prod is running with a cf Standard license, while perhaps test is running the free Dev editon, which runs as basically the equivalent to cf Enterprise.

Sadly, that means your test env has access to features your prod does not, like that built-in Adobe-provided Oracle driver (This is not a problem unique to CF: it happens with SQL Server as well, and perhaps also with oracle, where free Dev editions offer enterprise features but don't allow for emulation of standard features).

So you mentioned implementing some jdbc connection string to get thins working in prod. What is that? More important, what is the jdbc driver being used? And as you or someone seemingly had to download and implement an oracle driver jar, which was it?

Finally, have you considered implementing that as a new dsn in test, simply for testing to see if the same problem would happen there? (You could give it a new dsn name, so as to have no impact on use of the existing one in test.) If it then had the same error, you know it's about the driver.

Then you could focus your question on THAT, knowing not it's not really a CF question. You could even take the question to an oracle forum, but note you'd talk about it being a "parameterized query", as that's the generic term for what cfqueryparam does. That said, it would help to prove if your error with the 0 time happens even as a hard-coded sql statement, to avoid complicating things with mention of parameterized queries, if you may ask about it elsewhere.

You may well find, perhaps in the docs of whatever driver you are using, that there's some connection string that would fix your problem. Or  maybe there's an oracle plsql function which could fix it while workibg ok with either driver, and so could work in either test or prod.

Let us know how it goes. I really think this may be where you should turn next.


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

Inside your cfqueryparam you have .....cfsqltype="VARCHAR"....  it needs to be  CF_SQL_VARCHAR, so it reads cfsqltype="CF_SQL_VARCHAR".

I think that should do it.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

Changing  VARCHAR to CF_SQL_VARCHAR does not help, the error continues to remain the same.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'),

could be changed to this

<cfqueryparam value="TO_DATE(#datefrom#,'YYYY/MM/DD HH24:MI:SS','AMERICAN')" cfsqltype="CF_SQL_VARCHAR">

This way the whole to_date() function is sent in the (param 1)

OR why dont you do like you did for the toDateTime, set a varable fromDateTIme and remove the cfqueryparam altogether.

Another item i was screching my head on is the BEGIN and END (anonymous SQL block) that is allowed in ORACLE, I have never had luck with that personally.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

when I make the change you suggest, I now get an error . So looks like we are getting closer to resolving this.

Error Executing Database Query.            

            ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at line 1

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 08, 2019 May 08, 2019

Copy link to clipboard

Copied

I found the cause of the error: an extra bracket ")". Sorry, IQ1

This should do it.

<cfset toDateTime=dateFormat(dateto, "dd-mm-yyyy") & " " & timeFormat(dateto, "HH:nn:ss")>

<cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#, '#Session.UserName#', #multi_report_def_id#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN'); END;

<cfquery

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

Thanks, I tried your suggestion. This time the error is

Error Executing Database Query.

ORA-06550: line 1, column 182: PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * % & | = - + < / > at in is mod remainder not range rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ The symbol ")" was substituted for ";" to continue.  
 
The error occurred in C:/inetpub/wwwroot/TOPS_test/MultiSectionReportsResults.cfm: line 74

72 : <cfset toDateTime=dateFormat(datefrom, "dd-mm-yyyy") & " " & timeFormat(datefrom, "HH:nn:ss")>
73 : <cfquery name="PrepareMultiReportData" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
74 : BEGIN dbo.multi_report_pkg.run_multi_report(#Session.multi_rep_id#, '#Session.UserName#', #multi_report_def_id#, TO_DATE(<cfqueryparam value="#datefrom#" cfsqltype="VARCHAR" maxlength="28">,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN'); END;
75 : </cfquery>
76 :

SQLSTATE   65000
DATASOURCE   histt
VENDORERRORCODE   6550
SQL    BEGIN dbo.multi_report_pkg.run_multi_report(4534, 'fquresh2', 7, TO_DATE( (param 1) ,'YYYY/MM/DD HH24:MI:SS','AMERICAN'), TO_DATE('09-05-2019 00:00:00', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN'); END; 

Resources: 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

Well, that error is consistant with the error I would expect when attempting to execute Anonomsys blocks of code inside an <CFQUERY> tag.

Please try this <cfstoredproc> to call and see what happens.  OH, and what is the value of the todateTime variable? I only as that due to the other error of  "(full) year must be between -4713 and +9999, and not be ".

<cfstoredproc procedure="dbo.multi_report_pkg.run_multi_report" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#Session.multi_rep_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#Session.UserName#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#multi_report_def_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#datefrom#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">

</cfstoredproc>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

Sorry , I am new with coldfusion., can you also let me know the code to call a stored procedure from coldfusion.

Thanks

IQ

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

Hi, IQ!

Matthew has included that in his response. Use the CFSTOREDPROC, CFPROCPARAM and CFPROCRESULT tags to call stored procedures.

Dave Watts, Eidolon LLC

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

So I replaced the code with the stored procedure as suggested and as shown below

<cfstoredproc procedure="dbo.multi_report_pkg.run_multi_report" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#Session.multi_rep_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#Session.UserName#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#multi_report_def_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#datefrom#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">

<cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#period#">

</cfstoredproc>

The error I get now is the same as was obtained earlier

Error Executing Database Query.

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at line 1  
 
The error occurred in C:/inetpub/wwwroot/TOPS_test/MultiSectionReportsResults.cfm: line 88

86 :     <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">
87 : 
88 :  <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#period#">
89 :
90 : </cfstoredproc>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

Please wrap your query in a try/catch block and post the result.

For example:

<cftry>

  <cfstoredproc procedure="dbo.multi_report_pkg.run_multi_report" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#Session.multi_rep_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#Session.UserName#">

    <cfprocparam cfsqltype="CF_SQL_NUMERIC" value="#multi_report_def_id#">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#datefrom#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="TO_DATE('#toDateTime#', 'DD-MM-YYYY HH24:MI:SS', 'AMERICAN')">

    <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#period#">

  </cfstoredproc>

  <cfcatch type="any">

  <cfdump var="#cfcatch#" label="cfcatch" >

  <cfdump var="#variables#" label="variables" showudfs="false" >

  </cfcatch>

</cftry>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

I have attached an output after the try catch was added.

try_catch.png

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 09, 2019 May 09, 2019

Copy link to clipboard

Copied

so I am assuming you don't see it. The first date is coming in as 09-MAY-2019 12:00 AM and you mask for the ORACLE TO_DATE() function are not correct.  nothing to do with CF but the data you are pushing to call the proc.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation