OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » expdp query

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

expdp query [message #552178]

Tue, 24 April 2012 03:06

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??

Report message to a moderator

Re: expdp query [message #552180 is a reply to message #552178]

Tue, 24 April 2012 03:24

John Watson
Messages: 8966
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\'\)\"

Report message to a moderator

Re: expdp query [message #552181 is a reply to message #552178]

Tue, 24 April 2012 03:27

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: expdp query [message #552193 is a reply to message #552181]

Tue, 24 April 2012 04:35

lerry
Messages: 16
Registered: December 2011

Junior Member

hi Michel:
could you give me an example?
thank you !

Report message to a moderator

Re: expdp query [message #552194 is a reply to message #552193]

Tue, 24 April 2012 04:39

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\"

Report message to a moderator

Re: expdp query [message #552195 is a reply to message #552194]

Tue, 24 April 2012 04:47

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel

Report message to a moderator

Re: expdp query [message #552197 is a reply to message #552194]

Tue, 24 April 2012 04:48

John Watson
Messages: 8966
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.

Report message to a moderator

Previous Topic:	Execute package using sqlldr and input csv
Next Topic:	To_number sqlldr control file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:41:20 CST 2025