RDBPROD: Use of Function TO_DATE in SQL Gives SYSTEM-F-ACCVIO

(Doc ID 423508.1)

Last updated on AUGUST 03, 2016

Applies to:

Oracle SQL/Services for Rdb on OpenVMS - Version 7.1.5.9.1 to 7.3.1.1 [Release 7.1 to 7.3]
Oracle Rdb Server on OpenVMS - Version 7.0 and later
Information in this document applies to any platform.

Symptoms

The following query results in an error when using SQL/Services 7.2.x:

SQL>select * from table1
cont> where keyfld1 = '890914000315' and
cont> begin_date between TO_DATE('2007-01-01 10:00:00:00','yyyy-mm-dd hh24:mi:ss:ff','') and
cont>                    TO_DATE('2007-03-31 10:00:00:00','yyyy-mm-dd hh24:mi:ss:ff','');
%RDB-E-EXTFUN_FAIL, external routine failed to compile or execute successfully
-RDMS-E-RTN_ERROR, routine "ORA_CVT_TO_DATE" generated an error during execution
-SYSTEM-F-ACCVIO, access violation, reason mask=00, virtual address=0000000000000030,
PC=0000000001B4DA68, PS=0000001B

When using SQL/Services 7.3.x the following error is returned:

The column begin_date is a DATE VMS field in the table table1.

The SQLSTATE "A1821" stands for the error ORA-01821 which means "Date format not recognised"

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms