Good Morning..!!

Q: I've come up with a requirement where I will need to do table export where status ='YES'. My requirement is to get this WHERE clause from input table into variable and put it at expdp command in shell script

Ex: SELECT * FROM T1 WHERE STATUS ='YES'

UPDATE INPUT_TABLE SET SQL_STATEMENT='SCOTT.T1:"WHERE STATUS ='YES'"'

I've tried it with three options.

Option #1.. shell script has below code and run it on Linux server

#!/bin/ksh
V_QUERY=`sqlplus -s ""${DB_STRING}"" <<-EOS
set timing off head off feed off
SELECT SQL_STATEMENT
FROM INPUT_TABLE;
exit;
EOS
`
nohup expdp $DBUSERNAME/$DBPASSWORD@$ORACLE_SID tables=SCOTT.T1 query=$V_QUERY DIRECTORY=exp_dir
JOB_NAME=EXPORT_2018 DUMPFILE=expdp_PROD_2018.dmp LOGFILE=EXPLOG_2018 2>&1 &

I ran the script and got the below error..

ORA-31693: Table data object "SCOTT"."T1" failed to load/unload and is being skipped due to error:
ORA-00904: "YES": invalid identifier

PLEASE SUGGEST IS THERE ANY SYNTAX ERRORS ?

Option #2:

$ expdp UNAME/PWD@DBNAME query=SCOTT.T1:"WHERE STATUS ='YES'" tables=SCOTT.T1

I ran the expdp command and got below error.
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

Option #3:

It is through .par file

directory=exp_dir
dumpfile=T1.dmp
logfile=T1.log
tables=SCOTT.T1
QUERY=SCOTT.T1:"WHERE STATUS = 'YES'"

$expdp UNAME/PWD@DBNAME parfile=input_par.par

I ran the above command and it works fine as expected.

Finally, I wanted to get subquery from input table but not from the input file. Please suggest and let me know If you need any details.

Thanks
Hima