Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

export script with query option

Nago
Advisor

export script with query option

I run Oracle 8.1.6 on Hp-UX 11.0
kindly help me with an export script that would use the query option.
i want to backup: select * from actb where trn_dat between '01-jun-2003' and '10-jun-2003'.
Thanks aot.
3 REPLIES
Brian Crabtree
Honored Contributor

Re: export script with query option

You aren't going to be able to do this with 8i. This feature was added in 9i. You could do something like the following (as a shell script):

#!/bin/sh
sqlplus / <> logfile.log
create table actb_temp as select * from actb where trn_dat between ...;
exit;
!

exp userid=/ file=file.dmp tables=actb_temp

sqlplus / <> logfile.log
drop table actb_temp;
exit;
!

Hope this helps,

Brian
Massimo Bianchi
Honored Contributor

Re: export script with query option

Hi, you can also with 8.1.6



exp user/password tables=actb query="'select \* from actb where trn_dat between \'01-jun-2003\' and \'10-jun-2003\''"

You will have to play a little because the query must be enclose in single quotes ', but S.O. will try to expand, so you must wrap with duoble quotes and escape the wildchar * .

Massimo
Yogeeraj_1
Honored Contributor

Re: export script with query option

hi,

instead of using:

exp userid=useryd/passyd tables=actb query=\"where trn_dat between '01-jun-2003' and '10-jun-2003'\"

i would prefer to use a parameter file, exp.par containing the following argument:

query="trn_dat between '01-jun-2003' and '10-jun-2003'"

and now use the command:

exp userid=useryd/passyd tables=actb parfile=exp.par

(this is easier especially when you can have cases when you will have to escape the QUERY strings)

hope this helps!

regards
Yogeeraj

PS. Review your query! You should always compare dates to dates, strings to strings, numbers to numbers and never anything other combination
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)