OraFAQ Forum: Server Utilities » EXPDP with query error

Home » RDBMS Server » Server Utilities » EXPDP with query error (oracle 11g, OS-Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

EXPDP with query error [message #459174]

Thu, 03 June 2010 12:20

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

where entry_date\< \(sysdate -1\)\" DIRECTORY=dump DUMPFILE=log_exp.dmp logfile=log_exp.log

Report message to a moderator

Re: EXPDP with query error [message #459178 is a reply to message #459176]

Thu, 03 June 2010 12:40

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