Home » RDBMS Server » Server Utilities » EXPDP with query error (oracle 11g, OS-Linux)
EXPDP with query error [message #459174] Thu, 03 June 2010 12:20 Go to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi,

While trying to expdp using Query logics, getting syntax related erros shown below:

expdp system/xxxx SCHEMAS=LOG NETWORK_LINK=DBLINK1 INCLUDE=TABLE:\"IN\(\'DAILY_LOG\'\)\" QUERY=LOG.DAILY_LOG:\"where entry_date\< to_char\(sysdate -1,'yyyymmdd'\)\" DIRECTORY=dump DUMPFILE=log_exp.dmp logfile=log_exp.log

But gives the following error
ORA-31693: Table data object "LOG"."DAILY_LOG" failed to load/unload and is being skipped due to error:
ORA-00904: "YYYYMMDD": invalid identifier

I tried with simple sql with YYYMMDD and it works fine, the entry_date is a char field.

Do you have any idea, in QUERY where i'm doing wrong here?

[Updated on: Thu, 03 June 2010 12:22]

Report message to a moderator

Re: EXPDP with query error [message #459176 is a reply to message #459174] Thu, 03 June 2010 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
where entry_date\< \(sysdate -1\)\" DIRECTORY=dump DUMPFILE=log_exp.dmp logfile=log_exp.log
Re: EXPDP with query error [message #459178 is a reply to message #459176] Thu, 03 June 2010 12:40 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Hi ,
I tried with your syntax, but
The problem is the entry_date is a VARCHAR column. And using sql it gives the following error:
ORA-01861:literal does not match format string

The reason why sql works with
entry_date < to_char (sysdate -1,'yyyymmdd')
but the same doesn't work with expdp!

[Updated on: Thu, 03 June 2010 12:41]

Report message to a moderator

Re: EXPDP with query error [message #459370 is a reply to message #459176] Fri, 04 June 2010 09:27 Go to previous messageGo to next message
nadvi
Messages: 46
Registered: May 2006
Location: Canada
Member

Ok, I found it. the following syntax works.

QUERY=LOG.DAILY_LOG:\"where entry_date\< to_char\(sysdate -1,'yyyymmdd'\)\"

Thanks
Re: EXPDP with query error [message #598751 is a reply to message #459370] Thu, 17 October 2013 06:52 Go to previous message
zovanni
Messages: 1
Registered: October 2013
Location: Italia
Junior Member
nadvi wrote on Fri, 04 June 2010 09:27
Ok, I found it. the following syntax works.

QUERY=LOG.DAILY_LOG:\"where entry_date\< to_char\(sysdate -1,'yyyymmdd'\)\"

Thanks


Thanks, it worked fine for me!
Previous Topic: EXCLUDING DATA OF SOME TABLES in EXPDP
Next Topic: Can data pump utility can do imp while exp?
Goto Forum:
  


Current Time: Fri Nov 28 21:06:08 CST 2014

Total time taken to generate the page: 0.21756 seconds