Search code examples
sqloracle-databaseetlpentahokettle

Pentaho Kettle yells SQL Script error that Oracle doesn't

I have an ETL process which executes dozens of SQL query tasks.

One specifically keeps throwing an error: "ORA-01843: not a valid month" to one of my queries, however the very same query runs normally on PL/SQL. I have even created a procedure with a version of the same query, it also runs smoothly, but calling this procedure from pentaho keeps throwing the same error. Please, anyone have any idea why is pentaho presenting a query error that PL/SQL is not? Thanks in advance!

Solution

  • If you do:

    SELECT TO_DATE('01-FEB-03', 'DD-MON-RR') FROM DUAL;

    Then, on an English database with the default settings, the query works

    Then you change your session settings:

    ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';

    And run the same query:

    SELECT TO_DATE('01-FEB-03', 'DD-MON-RR') FROM DUAL;

    You will get the error:

    ORA-01843: not a valid month

    There are three solutions to this:

    1. Use the same settings for both queries - this is not a robust solution as you may specify the settings for some services and then it works for a while but then a new developer comes along and connects a new service and does not know of the required settings and suddenly everything is breaking.

    2. Specify the settings to use in the query.

      SELECT TO_DATE(
         '01-FEB-03',
         'DD-MON-RR',                -- Specify the format
         'NLS_DATE_LANGUAGE=English' -- Specify the language
       )
FROM DUAL;

    3. Use a date literal (which is agnostic of the settings):

      SELECT DATE '2003-02-01' FROM DUAL;

    db<>fiddle here