I am trying to use oracle data pump to export rows of table because the table contains CLOB column and import them to another db. I have linux shell which is used to run the expdp query. The only thing is that for my query i want to select data after a period of time which i get this from db. So i have the RETDATE as a variable which is used to call sqlplus in order to get that value in the form of YYMM and then i will use it inside the data pump query.
My question is that RETDATE is a string can i have variables inside or should they be concatenated like for example in sql we use the || ?? For example i have
RETDATE=`sqlplus $USERNAME/$PASSWORD@$INSTANCE`
Username and Password are used to connect to the database and $INSTANCE is the dname but below i have
SELECT hire_date FROM table@$ANOTHERINSTANCE;
where $ANOTHERINSTANCE is the db link name used to get the value of the date.
The second part of the question is that if the TO_TIMESTAMP('$RETDATE','YYMM') valid inside the query. This is the code i am thinking to use. Will something like this work??
USERNAME=myname
PASSWORD=pass
INSTANCE=dbinstance
ANOTHERINSTANCE=dblinkname
RETDATE=`sqlplus $USERNAME/$PASSWORD@$INSTANCE <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT hire_date FROM table@$ANOTHERINSTANCE;
EXIT;
EOF`
QUERY='employees:"WHERE hire_date > TO_TIMESTAMP('$RETDATE','YYMM')"'
PATH=/tmp/test
expdp $USERNAME/$PASSWORD@$INSTANCE directory=EXPORT_DIR tables=hr.employees query=$QUERY dumpfile=$PATH/employees.unl content=data_only logfile=employees.log
bash?