[SOLVED] Bash script with user interaction to create an Oracle export

tiny.deluxe · 04-26-2017, 06:52 AM

Hi,
I'm not really good in programming (just an Oracle DBA

) so I need your help.
Now I'm working on a shell script that should do the following:

It has to be called with 3 parameters. The last one is the one that I need.
This parameter leads to an if condition:

TARGET=$1
SCHEMA=$2

if [ $3 = "Y" ];then
echo "Which tables should be excluded? (Input not case-sensitiv like: tab1, tab2)"
read INPUT_VARIABLE
fi

The content of the INPUT_VARIABLE (which may be now tab1, tab2) should be part of the following command:

expdp system/passw@TARGET directory=exports schemas=$SCHEMA exclude=TABLE:"IN $\'tab1\',\'tab2\'$"

If you can see, the content of INPUT_VARIABLE has to be split in tab1 and tab2 and included in commas and brackets. I know that I need a loop but I have no clue how to do it. So please help me before I go insane.

Thanks in advance
Regards
Elke

astrogeek · 04-26-2017, 01:31 PM

Welcome to LQ!

Please place your code snippets inside [CODE]...[/CODE] tags for better readability. You may type those yourself or click the "#" button in the edit controls.

You should quote the variable in the test, otherwise it will produce an error if it is empty...

Code:

if [ "$3" = "Y" ];then
...
fi

You may do better to read the table names into an array, then format the IN(...) string from those. Here is a working example to give you some ideas of how to do that:

Code:

#!/bin/bash

prompt="Enter tables to exclude: "
while read -p "$prompt" -a rdin
do
        for key in "${!rdin[@]}"
                {
                IN="${IN}'${rdin[$key]}',"
                }
        echo "exclude=TABLE:\"IN(${IN%,})\""
        break;
done

See how far you can get with that and let us know if you need more help with it!

If you need more help, please try to be complete in your description of just what you need. For example, is there always a fixed number of excluded table names? One or more? Zero or more?

The better you describe your problem, the better we can try to help.

Good luck!

Laserbeak · 04-26-2017, 01:35 PM

I wrote a script, it seems to work. Most shell programmers would use sed/awk, but I used perl so the whole thing probably should have been written in it, but I kept it a shell script

Code:

#!/bin/bash

TARGET=$1
SCHEMA=$2

if [ "${3}" = "Y" ]; then
   echo -n "Which tables should be excluded? (Input not case-sensitive like: tab1, tab2): "
   read INPUT_VARIABLE
fi

cmd="\$_=(<>);s/([\w\d]+)/\\\'\$1\\\'/g;print;";
QUOTED_INPUT=`echo ${INPUT_VARIABLE} | perl -e "${cmd}"`;

EXPDP_COMMAND="expdp system/passw@${TARGET} directory=exports schemas=${SCHEMA} exclude=TABLE:\"IN \(${QUOTED_INPUT}\)\""

echo "Executing ${EXPDP_COMMAND}"

exec ${EXPDP_COMMAND}

Oh, and did you really want all those backslashes to be in command in the part "TABLE:"IN $\'tab1\',\'tab2\'$""?

grail · 04-27-2017, 06:12 AM

A heredoc might also help to not have to go through the escaping process:

Code:

read -p "Which tables should be excluded? (Input not case-sensitive like: tab1, tab2): " -a tables

for table in "${tables[@]}"
do
	[[ "$excludes" ]] && excludes="$excludes,'$table'" || excludes="'$table'"
done

echo expdp <<EOF
	system/passw@$TARGET directory=exports schemas=$SCHEMA exclude=TABLE:"IN ($excludes)"
EOF

I can try it tomorrow for you at work and see how it goes, but as it is not damaging in any way, give it a whirl

(remember to pick a small schema for testing)

tiny.deluxe · 05-02-2017, 06:11 AM

Hi all,
thanks a lot for your help and sorry for my belated answer. I've been sick last week so I couldn't reply.
I've tried all of your solutions and found them very helpful.

@astrogeek:
thanks for the nice welcome and also for the hints how to write in this forum as well as how to improve my code. As soon as I've included one of the solutions in my script successfully, I'll mark this thread as solved. But perhaps there will appear one or the other new question. ;-)

@Laserbeak:
Yes I really do need those backslashes because Oracle Data Pump requires it if you call the exclude parameter on command line. Normally I would do excludes within a parfile but in this special case I can't use one.

@grail:
echo with heredoc didn't work as expected. That's the output:
Which tables should be excluded? (Input not case-sensitive like: tab1, tab2): all_views_clob, all_mview_clob
expdp
But you helped me a lot with the loop.

Best Regards
Elke

tiny.deluxe · 05-02-2017, 08:34 AM

Finally the script is ready:

Code:

if [ "$3" = "Y" -o "$3" = "y" ];then

prompt="Enter tables to exclude (e.g.: tab1 tab2): "
while read -p "$prompt" -a rdin
do
        for key in "${!rdin[@]}"
                {
                IN="${IN}\'${rdin[$key]}\',"
                IN=`echo $IN | tr 'a-z' 'A-Z'`
                }
        expdp system/pw@$1 directory=exports schemas=$2 dumpfile=elke_test.dmpdp logfile=elke_test.log exclude=TABLE:\"IN\(${IN%,}\)\"
        break;
done
else
        expdp system/pw@$1 directory=exports schemas=$2 dumpfile=elke_test.dmpdp logfile=elke_test.log
fi

once again, thanks a lot for your help

Best Regards
Elke

grail · 05-02-2017, 09:27 AM

ummmm .... Does that actually work??

Here are some problems I see:

1. For every loop of the while loop you will be prompted for more data ... do you really need this loop??

2. For loop has neither do or done to advise start or end of the loop, Is this not in bash?

3. What is the purpose of getting the index of items in your array instead of just the items in the array? You do not appear to use the index anywhere else??

4. The break goes further to not seeing the need for a while loop

I get the echo probably didn't work, but what about the heredoc without it?

Another option to would be to feed all piece of the command into an array and then only have to call it once at the end

Also, if you used my method from within the loop you would not need to remove the extra comma from the end