Oracle error from ColdFusion

[LarrySteele]

I'm posting this in the ColdFusion forum because the error only appears in ColdFusion. I have no problem pulling the data from TOAD or SQLPlus.

Table def:
GTT_RETIRE_ELIG (yes, it's a global temp table)
PERSONID    CHAR(12)
ELIGNOW     CHAR(10)
ELIG5       CHAR(3)
ELIG10      CHAR(3)

Data description:
PERSONID: Nine numbers, equates to US SSN
ELIGNOW: "YES" or the to_char(date) the person will be eligible to retire
ELIG5: "YES" or "NO"
ELIG10: "YES" or "NO"

While in ColdFusion, if I query just the PERSONID, I get data back with no problem (using cfquery, cfdump, cfabort to view output).

However, if I query any other field, I receive: "ORA-01858: a non-numeric character was found where a numeric was expected"

ELIGNOW is the only field that has mix of ~dates~ and strings. I've removed all rows from the table that had ~dates~, meaning all rows have "YES" in the offending column, and it still fails, expecting to see a numeric.

I even took out ELIGNOW so all fields were monotype, and the error still occurred.

If I query for ELIG5 only, which has nothing but "YES" and "NO", I get the same Oracle error even though there's no reason to expect a numeric character in the char field.

I then tried putting in my own values from ColdFusion:

insert into GTT_RETIRE_ELIG
values ('123456789','01/02/2003','YES','YES')

Using this method, ColdFusion had no problem returning the record.

Unfortunately, I've hit a wall and can't figure out how to get past this one. Google, books, and co-workers have not been as helpful as usual.

I've tried numerous permutations of script and code and no joy. Now deadline looms ominously. Any assistance would be greatly appreciated.

 

[LKBrwnDBA]

ORA-01858 a non-numeric character found where a digit was expected

Cause: The input data to be converted using a date format model was incorrect. The format model expected a number but found a non-numeric character.

Action: Check the input data and the date format model to make sure the elements match in number and type, then retry the operation.

This error occurs when you are converting a string to a date.
Check out the column definitions from your tables and the data you are mapping into those tables.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LarrySteele]

Thanks. I'm sending strings to a string field. That's why Oracle is perfectly happy with the view and the global temp table. It's ColdFusion (CF) that's giving me grief.

I even changed the view so the second column is a date field (since CF would cast my char field to date anyway). Oracle was still good-to-go, but CF would not accept the empty fields in my view.

WORKAROUND

After much experimenting, I found that the page correctly reads the data if it's in a static table.

The problems remain when using the view from CF, when trying to fill a global temporary table form the view, or even calling a proc to fill a static table using plain sql (view not involved).

What's working right now is that I have a proc that updates the static table. As long as I run the proc from Oracle, and have CF just read the resultant table, I get the required data. I'll just have to add the proc to the scheduler.

Sure seems awfully cludgy. No way this process should have been this hard.

Thanks again, appreciate the suggestion.

 

[LKBrwnDBA]

No prob. Good luck!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb