oracle date and nls settings

Question

I get an ORA-01858 exception. I think it is due to NLS settings and date:

declare
    currentDate DATE := sysdate;
    queryString VARCHAR2(300) := '... where blablabla = :bind_timestamp ...';
    sql_cursor integer;
begin
    sql_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(sql_cursor, queryString, dbms_sql.native);
    dbms_sql.bind_variable(sql_cursor, ':bind_timestamp', currentDate);
    rowsProcessed := dbms_sql.execute(sql_cursor);
    dbms_sql.close_cursor(sql_cursor);
end;

Is dbms_sql.bind_variable(sql_cursor, ':bind_timestamp', currentDate); NLS dependent?

I mean with some NLS settings it works normally and others it fails?

Answer 1

When dealing with dates, NLS settings are not relevant if you provide your own format string:

WHERE arrival=TO_DATE('2011-09-05', 'yyyy-mm-dd')

My guess is that you are relying on automatic type juggling, i.e., you are providing a string and letting Oracle cast it to date as required:

WHERE arrival='05/09/2011'

In that case, Oracle uses the default format as specified in your NLS settings.

Update: BTW, the ORA-01858 code means:

a non-numeric character found where a digit was expected