TOAD 10.6执行SQL报错ORA-01858，请求排查查询语句问题

Hey there, let's work through that ORA-01858 error you're hitting in TOAD 10.6 with your SQL statement. First, a quick reminder: ORA-01858 means Oracle found a non-numeric character where it expected a number, but in your case, this almost certainly ties back to date conversion issues or mismatched parameter types with the stored function you're calling.

Let's break down the fixes step by step:

1. Fix Implicit Date Conversion (Most Likely Culprit) #

When you pass date strings like '01-may-2017' directly, Oracle relies on your session's NLS settings to convert them to date values. If those settings don't match your string format, it can trigger ORA-01858. The simple fix is to use explicit date conversion with TO_DATE() and a clear format mask:

SELECT * 
FROM TABLE(fdr_dal_txns.get_txn_trans_adjst_consol (
    short_string_col('1BFV') ,
    'POST_DT' ,
    short_string_col('MCH','GP3', 'OTC') ,
    TO_DATE('01-may-2017', 'DD-mon-YYYY') ,
    TO_DATE('30-june-2017', 'DD-mon-YYYY') 
)) 
WHERE trd_id_num IN ('17FHKBBSSML', '17FHVBBRJD8')

This removes any guesswork for Oracle and ensures your date strings are parsed correctly.

2. Verify Function Parameter Types #

Double-check the definition of fdr_dal_txns.get_txn_trans_adjst_consol to confirm the 4th and 5th parameters expect DATE values (not VARCHAR2). If they do expect strings, make sure your date strings match the exact format the function is coded to handle (e.g., maybe it requires 'YYYY-MM-DD' instead of 'DD-mon-YYYY').

3. Check TOAD's NLS Settings (Just to Be Safe) #

TOAD's session-level NLS_DATE_FORMAT might not align with your date string format. You can check this by going to View > Toad Options > Database > NLS and looking at the NLS_DATE_FORMAT value. If it's set to something like 'DD-MON-RR' or a different format, that could cause implicit conversion failures. Using TO_DATE() explicitly (as in step 1) bypasses this issue entirely.

4. Test the Function in Isolation #

To narrow down where the error is coming from, try calling the function directly without the outer SELECT and WHERE clause:

SELECT fdr_dal_txns.get_txn_trans_adjst_consol (
    short_string_col('1BFV') ,
    'POST_DT' ,
    short_string_col('MCH','GP3', 'OTC') ,
    TO_DATE('01-may-2017', 'DD-mon-YYYY') ,
    TO_DATE('30-june-2017', 'DD-mon-YYYY') 
) FROM DUAL;

If this still throws ORA-01858, the problem is definitely within the function's parameter handling or internal logic, not the outer query.

5. Rule Out Hidden Characters (Long Shot) #

Occasionally, date strings can have invisible characters like trailing spaces or non-printable characters that break conversion. Try trimming your date strings or retyping them manually to eliminate this possibility:

TO_DATE(TRIM('01-may-2017'), 'DD-mon-YYYY')

Start with the explicit date conversion—this fixes the vast majority of ORA-01858 cases involving date parameters.

内容的提问来源于stack exchange，提问作者rich