CODEWITHSUNDEEP
oracle-databaseodbcoledbqlikview
Toby
Toby

Reputation: 145

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

Upvotes: 0

Views: 1494

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

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.

Upvotes: 2

Sud
Sud

Reputation: 153

Ideally both should work. Please refer Post

  • ODBC provides access only to relational databases
  • OLE DB provides the following features
    • Access to data regardless of its format or location
    • Full access to ODBC data sources and ODBC drivers

So it would seem that OLE DB interacts with SQL-based datasources THRU the ODBC driver layer.

alt text

Upvotes: 0

Related Questions