Search code examples
oracleodbcoledbqlikview

OLEDB error ORA-01861

I'm connecting throught Qlikview to pl/sql (oracle) and for the same script I use an ODBC connection and the script runs with no issues but when I connect using an OLEDB I receive an error ORA-01861: literal does not match format string. My question is mainly why would my script work using an ODBC connection but not with the OLEDB?

Adding code as I would like to make my query compatabile with the OLE DB Connection...The below is where the hang up is occuring...

SELECT DISTINCT B.CLT_CLT_PGMID,TO_CHAR(B.FIRST_PHONE_CONTACT,'MM/DD/YYYY') AS FIRST_PHONE_CONTACT,
                        ABS(TO_DATE(B.FIRST_ASSGN_DT, 'DD-MM-YYYY') - TO_DATE(B.FIRST_PHONE_CONTACT, 'DD-MM-YYYY')) AS FIRST_Contacted_By_Phone_Days

         FROM (
                SELECT DISTINCT MIN(SERVICE_DATE) AS FIRST_PHONE_CONTACT
                                   ,a.FIRST_ASSGN_DT, a.FIRST_CONTACT_DT, d.is_visit, d.attempt_or_contact,a.PERS_GEN_KEY,a.CLIENT_PROGRAM,a.clt_clt_pgmid

                  FROM  A
                    LEFT JOIN   D ON A.CLT_CLT_PGMID = D.CLT_CLT_PGMID AND A.ID_CARD = D.ID_CARD

                    WHERE 1=1
                   rownum <10

                      GROUP BY  a.FIRST_ASSGN_DT, a.FIRST_CONTACT_DT, d.is_visit, d.attempt_or_contact,a.PERS_GEN_KEY,a.CLIENT_PROGRAM,a.clt_clt_pgmid
                                                                                                                                                         ) B

the below is where the error is occurring ABS(TO_DATE(B.FIRST_ASSGN_DT, 'DD-MM-YYYY') - TO_DATE(B.FIRST_PHONE_CONTACT, 'DD-MM-YYYY')) AS FIRST_Contacted_By_Phone_Days

Solution

  • Have a look at this on your PC:

    For OLE DB it says:

    The date format for the Oracle session cannot be set using the ALTER SESSION SET NLS_DATE_FORMAT command. In Visual Basic, date formats are controlled by the Regional Settings properties in Windows Control Panel. For more information on Visual Basic date formats, refer to your Visual Basic documentation.

    For Oracle Provider for OLE DB, if the Connection property UseSessionFormat is FALSE, which is a default value, then NLS_DATE_FORMAT is fixed for the session to 'YYYY-MM-DD HH24:MI:SS' by the provider. If you pass the date to Oracle Database as a string, the date must be in the 'YYYY-MM-DD HH24:MI:SS' format. If UseSessionFormat is TRUE, then NLS_DATE_FORMAT is not fixed by Oracle Provider for OLE DB and the default session NLS_DATE_FORMAT is used.

    The behavior at ODBC might be different.