I need for my job one shell script very concrete about connection to oracle databases and writing to text file.
Well I tell you:
1.- I must read a file as parameter from linux interface:
> example.sh fileToRead
2.-I must read line to line in "fileToRead" and take a specific value for each line:
0000000#12516654066#654654654564#xx/xx/xxxx#xx/xx/xxxx
0000000#12901548932#545464654656#//#xx/xx/xxxx
0000000#64654687434#564849849879#//#xx/xx/xxxx
3.- For each value I must call a fileA.sql make a query with this value:
I have a fileA.sql ==>
SELECT * FROM hello
WHERE hello.num = '��???'
4.- And the last is to dump the results in a text file or a .dat
My shell script is this, so I know that it�s wrong and incomplete:
#!/bin/bash
if [ $# -ne 2 ]
then
exit 1
else
for linea in $1
do
telefono=`echo $linea | cut -d"#" -f 3`
server=`echo $STRING |cut -d '#' -f1`
user=`echo $STRING |cut -d '#' -f2`
password=`echo $STRING |cut -d '#' -f3`
sqlplus -s $user/$PASSWORD @$xxxxxxxxxxx/xxxxxxxxxxx.sql >> $xxxxxxxx/xxxxxx.dat
done
fi
Instead of connecting to DB for each entry in the file, you can construct the list of entry to query and then finally query the database.
Here is an example:
qnum="("
while IFS="#" read f1 f2 f3 skip
do
qnum="${qnum}'$f3',"
done < fileToRead
qnum="${qnum})"
qnum=$( echo "$qnum" | sed 's/,)$/)/' ) # remove trailing comma
sqlplus -s $user/$PASSWORD << EOF >> xxxxxx.dat
set echo off head off feed off pagesize 0 trimspool on linesize 1000
select * FROM hello
WHERE hello.num in ${qnum};
exit
EOF
I continue with this today. I was looking yours answers and I built this:
FILE.SH
#!/bin/bash
#If the number of variables is two leaves
if [ $# -ne 2 ]
then
exit 1
else
#read row by row in the parameter file
for linea in $1
do
telefono=`echo $linea | cut -d"#" -f 3`
#declares an array to store all the variables of each of the lines in a file
set -A numeros_telefonos $telefono
done
#Connect the database
==> ����?????
#for each phone number is found in the corresponding table and dumps the contents in its corresponding output file
for i in ${numeros_telefonos
[*]}
do
#name the file. sql and dump the information to an output file
==> ���????
==> ���fich_dat=${i}???
done
#fin del if de entrada
fi
QUERY.SQL
SPOOL tramiT.dat
SELECT * FROM hello WHERE xxx = '&1'
SPOOL OFF;
/
QUIT;
Hi, in your code there are some errors: this code mybe useful for you.
First create a bash
#!/bin/bash
# populate the numeros_telefonos array with all telephon numbers
for linea in $( cat $1 )
do
telefono=`echo $linea | cut -d"#" -f 3`
numeros_telefonos=("${numeros_telefonos[@]}" "$telefono")
done
#loop on array
for i in ${numeros_telefonos[@]}
do
#for each entry in array execute file.sql with telephon number as parameter
sqlplus -L -s user/password@server @ file.sql $i >> tramiT.dat
done
in the sql file:
set ver off
set heading off
set pause off
SET FEEDBACK OFF
SET ECHO OFF
SET TERM ON
SET TRIMOUT ON
SET TRIMSPOOL ON
SET PAGESIZE 0
SET linesize 500
SELECT * FROM hello WHERE xxx = '&1';
QUIT;
then execute the bash, passing as first parameter the file: hope I helped you.
If this works try also the bipinajith solution, that result more fast, but not applicable if the list is very long.
#!/bin/ksh
if [ $# -ne 1 ]
then
echo "SALIDA FORZADA"
exit 1
else
for linea in $(cat $1)
do
telefonos=`echo $linea | cut -d"#" -f 3`
numeros_telefonos[$i]=$telefonos
done
#loop on array
for i in ${numeros_telefonos[@]}
do
#for each entry in array execute file.sql with telephon number as parameter
sqlplus user/pass @file.sql $i >> tramiT.dat
done
fi
==> I tried and the result is not very good for me because my $1 it�s a file about 1.000.000 lines and I need open the database out of the "for".
==> I woould like open my database here:
sqlplus user/pass
#loop on array
for i in ${numeros_telefonos[@]}
do
#for each entry in array execute file.sql with telephon number as parameter
@file.sql $i >> tramiT.dat
done
but this is a problem!!!
THANKS FOR ALL ... and I am sorry for need so helping.
On the Linux filesystem, the files look like this -
$
$
$ cat -n file_to_read
1 00#AA#1#xx
2 00#AA#2#xx
3 00#AA#3#xx
4 00#AA#4#xx
5 00#AA#5#xx
6 00#AA#6#xx
$
$
$ cat -n example.sh
1 #!/usr/bin/bash
2 (
3 echo "select * from hello where x in ("
4 awk -F# '{ x = NR == 1 ? "" : ","; print x, "'\''"$3"'\''" }' file_to_read
5 echo ");"
6 ) > file.sql
7
8 sqlplus -s test/test <<EOF > tramit.dat
9 set feed off timing off
10 @file.sql
11 exit
12 EOF
$
$
When the shell script "example.sh" runs, it first creates "file.sql" which is then used to query the Oracle table and redirect the output to "tramit.dat".