Tuesday, April 17, 2012

Oracle export with query

If you need to produce an Oracle export with a filter/query, you may encounter syntax errors because you need to escape certain characters. This is the format I had to use:
exp james/pass tables=earth.my_table query=\'where date \> 
to_date\(\'25\-01\-2012\'\, \'dd\-mm\-yyyy'\)\' file=export.dmp

An even simpler solution which will work on all platforms is to put the query in a parfile:
[oracle@moon /]$ cat export.par
query="where date > to_date('25-01-2012', 'dd-mm-yyyy')"

You can then call export as follows:
exp james/pass tables=earth.my_table parfile=export.par file=export.dmp
Post a Comment