export script with query option

[Nago]

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.

[Brian Crabtree]

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]

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]

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