Home » RDBMS Server » Server Utilities » Data pump Export with query and date value (Oracle 10gR2, Sun 5.10)
Data pump Export with query and date value [message #487324] Fri, 24 December 2010 23:21 Go to next message
karthickbab
Messages: 16
Registered: November 2009
Location: India
Junior Member
Hi,
If i export data using thw below query it shows the error:

>expdp test1/test1 DIRECTORY=datapump DUMPFILE=expfull.dmp query=auth_test:\"where TXNREQDTTIME\<'20-MAY-10'\" tables=auth_test


bash-3.00$ expdp test1/test1 DIRECTORY=datapump DUMPFILE=expfull-3.dmp query=auth_test:\"where TXNREQDTTIME\<'20-MAY-10'\" tables=auth_test

Export: Release 10.2.0.1.0 - Production on Saturday, 25 December, 2010 5:10:06

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST1"."SYS_EXPORT_TABLE_01": test1/******** DIRECTORY=datapump DUMPFILE=expfull-3.dmp query=auth_test:"where TXNREQDTTIME<20-MAY-10" tables=auth_test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "TEST1"."AUTH_TEST" failed to load/unload and is being skipped due to error:
ORA-00904: "MAY": invalid identifier
Master table "TEST1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST1.SYS_EXPORT_TABLE_01 is:
/App1/oracle/file/expfull-3.dmp
Job "TEST1"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 05:10:08

Anyone please solve this?
Re: Data pump Export with query and date value [message #487327 is a reply to message #487324] Sat, 25 December 2010 00:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post formatted results of the following

SQL> DESC TEST1.AUTH_TEST

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Data pump Export with query and date value [message #487328 is a reply to message #487327] Sat, 25 December 2010 00:08 Go to previous messageGo to next message
karthickbab
Messages: 16
Registered: November 2009
Location: India
Junior Member
SQL> desc test1.auth_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
CARD_NO NUMBER
TXNREQDTTIME DATE
UNIQUEID VARCHAR2(40)
Re: Data pump Export with query and date value [message #487330 is a reply to message #487328] Sat, 25 December 2010 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>query=auth_test:\"where TXNREQDTTIME\<'20-MAY-10'\"
>query=auth_test:"where TXNREQDTTIME<20-MAY-10"

flawed for 1 or more reasons

With Oracle characters between single quote marks are STRINGS!
'This is a string, 2009-12-31, not a date'
When a DATE datatype is desired, then use TO_DATE() function.

Consider using a parameter file to avoid command line parsing of arguments
Re: Data pump Export with query and date value [message #487336 is a reply to message #487330] Sat, 25 December 2010 01:17 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, I think you have to also escape '.

Regards
Michel
Previous Topic: multiple rows
Next Topic: How to import 11g data into 9i
Goto Forum:
  


Current Time: Thu Mar 28 10:29:53 CDT 2024