Oracle export with Boolean logic variables Oracle Tips by Burleson Consulting

Question: Does the Oracle export (expdp) utility allow for "if" statements and variables?  I want to export specific tables based on a variable value.

Answer: Oracle data pump export allows a you can add a where clause to your export syntax  with the "query" keyword to extract a sub-set of your production rows for export.  In this example, we restrict the export to rows that meet the where clause:

exp scott/tiger tables=tab1 query="where mymonth > sysdate - 31"

exp scott/tiger tables=emp query=\"where deptno = 20"\

exp scott/tiger query="where job='SALESMAN' and sal<800"

Another technique is to run a SQL*Plus query to build the "tables=" list.  This is just a hack, and you must manually remove the final comma from the list:

$ORACLE_HOME/bin/sqlplus -s joe/cool<<EOF
spool /tmp/parms.txt
select table_name||',' from user_tables
exit
EOF
echo "exp rows=y, tables=" > /tmp/parfile.txt
cat /tmp/mypar.txt >> /tmp/parfile.txt

You can also embed your export into a shell script, where you have a lot more power and flexibility.  In this example we check for any "ORA-" errors in our export:

root> exp parfile=mypar.par log=exp.log

if [ `grep EXP- exp.log | wc -l` -gt 0 ] || [ `grep ORA- exp.log | wc
-l` -gt 0 ]; then
   mailx -s "Errors in Export" -c larry@oracle.com < exp.log
   exit 1
fi
 

You can also grep for a positive result and send an e-mail of your export fails:

root> exp parfile=mypar.par > /tmp/myexp.log

 

grep "successfully without warnings" /tmp/myexp.log > /dev/null 2>&1
if [ $? != 0 ]
then
mailx -s "Failure in /tmp/myexp.log" larry@oracle.com
fi
 

Here is an example of a complex SQL statement that is used in a Data Pump export:

expdp / file=$file tables=myexport query\=" where myexport_pidm in (select myexport_pidm from purdue.myexport where myexport_to_extract\=\'Y\' and myexport_export_status\=\'N\')\"

In this case, the subquery is causing this error:

cal_export_tables.sh[7]: 0403-057 Syntax error at line 7 : `"' is not matched.

The solution is to place the parms in a parfile, and this will then work:

expdp / file=$file parfile=myparfile.par