My Oracle Support Banner

Expdp With Case-sensitive Columns In Query Clause May Have ORA-01858 (Doc ID 2401356.1)

Last updated on APRIL 03, 2020

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Linux x86-64

Goal

While trying to export a table with case sensitive columns with query parameter you may encounter below errors:

 

ORA-31693: Table data object "<SCHEMA>"."<TABLE>" failed to load/unload and is being skipped due to error:
ORA-01858: a non-numeric character was found where a numeric was expected

 


parfile.par

directory=Data_Pump
dumpfile=test.dmp
cluster=n
logfile=test.log
tables=OWNER.TABLE
query="WHERE "sys_created_on" > to_date('01/05/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')"

The following syntax were not successful either in the case above:

query='WHERE \"sys_created_on\" > to_date('01/05/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')'
query="WHERE '\"sys_created_on\"' > to_date('01/05/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')"

QUERY="WHERE 'sys_created_on' > to_date('01_05_2018 00_00_00','DD_MM_YYYY HH24_MI_SS')"
query="WHERE '\"sys_created_on\"' > to_date('01_05_2018 00_00_00','DD/MM/YYYY HH24_MI_SS')"
query="WHERE \"sys_created_on\" > to_date("01/05/2018 00:00:00","DD/MM/YYYY HH24:MI:SS")"
query='WHERE "sys_created_on" > to_date("01/05/2018 00:00:00","DD/MM/YYYY HH24:MI:SS")'
QUERY=\"WHERE \'sys_created_on\' > to_date\(\'01_05_2018 00_00_00\',\'DD_MM_YYYY HH24_MI_SS\'\)\"
QUERY="WHERE \'sys_created_on\' > to_date\(\'01_05_2018 00_00_00\',\'DD_MM_YYYY HH24_MI_SS\'\)"

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.