Home » RDBMS Server » Server Utilities » Export subsets of data with date query
Export subsets of data with date query [message #70074] Mon, 08 April 2002 22:16 Go to next message
Jaco
Messages: 23
Registered: August 2000
Junior Member
Hello

Can anyone pls tell me how to use the exp utility but with the query from a date for ex:
exp usr/pw tables=abc_tbl query="where start_date > to_date('01-JAN-2002','DD-MON-YYYY')

The above script does not work as well as various versions of it. Can you use queries with date values with this function and if not how can you get a subset of data for export with dates > x?

Thanks, Jaco
Re: Export subsets of data with date query [message #70079 is a reply to message #70074] Tue, 09 April 2002 07:56 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
Jaco,

For every quote be it single or double you have to escape it. First question is the start_date a date column? If so:

query="where start_date > '01-JAN-2002'"

or did you try:

query="where start_date > to_date('01-JAN-2002','DD-MON-YYYY')"

Another option is to:

create table mytemp as select * from abc_tbl where start_date > to_date('01-JAN-2002','DD-MON-YYYY')

and then export mytemp.
Re: Export subsets of data with date query [message #70082 is a reply to message #70074] Tue, 09 April 2002 10:18 Go to previous messageGo to next message
Cindy
Messages: 88
Registered: November 1999
Member
Try this:

$ cat exp.par
query="where period_date=200203"


$ exp login/password tables=my_table parfile=exp.par

Export: Release 8.1.7.2.0 - Production on Tue Apr 9 13:57:50 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.2.0 - 64bit Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table MY_TABLE 10 rows exported
Export terminated successfully without warnings.

$ ls *dmp
expdat.dmp
Re: Export subsets of data with date query [message #70084 is a reply to message #70082] Tue, 09 April 2002 11:21 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
Very good to know. Never tried putting in the parameter file. Thanks.
Previous Topic: Database Migration w/Oracle 8i
Next Topic: SQL*Loader-524 Error.. need help
Goto Forum:
  


Current Time: Mon Dec 05 08:43:11 CST 2022