Home » RDBMS Server » Server Utilities » expdp query
expdp query [message #552178] Tue, 24 April 2012 03:06 Go to next message
lerry
Messages: 16
Registered: December 2011
Junior Member
hi:

expdp test/test directory=dmpdir dumpfile=wip.dmp logfile=wip.log tables=wip_runcard query=wip_runcard:'"where org_id=51 and time<to_date ('2011-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and ship_time is not null and wip_route=999 and used_status=5"'

but error:
ORA-31693: Table data object "TEST"."WIP_RUNCARD" failed to load/unload and is being skipped due to error:
ORA-00907: missing right parenthesis

how to use expdp query paramter with time??
Re: expdp query [message #552180 is a reply to message #552178] Tue, 24 April 2012 03:24 Go to previous messageGo to next message
John Watson
Messages: 8135
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Here's an example:
expdp system/manager full=y directory=exp_oxtest 
dumpfile=oxtest05full%U filesize=8g logfile=exp_oxtest:oxtest05.log
flashback_time=\"TO_TIMESTAMP\(\'13/08/2011 05:20:00\',\'DD/MM/YYYY HH24:MI:SS\'\)\"
Re: expdp query [message #552181 is a reply to message #552178] Tue, 24 April 2012 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Your query parameter value is incorrect, inner quote must be doubled (not use double quotes but double the single quotes). The first single quote should be before the table name not after the colon.

Regards
Michel
Re: expdp query [message #552193 is a reply to message #552181] Tue, 24 April 2012 04:35 Go to previous messageGo to next message
lerry
Messages: 16
Registered: December 2011
Junior Member
hi Michel:
could you give me an example?
thank you !
Re: expdp query [message #552194 is a reply to message #552193] Tue, 24 April 2012 04:39 Go to previous messageGo to next message
lerry
Messages: 16
Registered: December 2011
Junior Member
I have alread resloved this problem!

expdp test/test directory=dmpdir dumpfile=wip.dmp logfile=wip.log tables=wip_runcard query=wip_runcard:\"where org_id=51 and time\<to_date\(\'2011-01-01\',\'yyyy-mm-dd\'\) and ship_time is not null and wip_route=999 and used_status=5\"
Re: expdp query [message #552195 is a reply to message #552194] Tue, 24 April 2012 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66800
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Re: expdp query [message #552197 is a reply to message #552194] Tue, 24 April 2012 04:48 Go to previous message
John Watson
Messages: 8135
Registered: January 2010
Location: Global Village
Senior Member
Actually, I think Michel's method would probably be better: escaping all the awkward characters is very Unix specific, I think one would have to sort out the quotes properly for Windows.

But thanks for providing the solution.
Previous Topic: Execute package using sqlldr and input csv
Next Topic: To_number sqlldr control file
Goto Forum:
  


Current Time: Mon Jan 27 07:35:08 CST 2020