Seeking for your assistance to read each line $1 and $2 of input file and used it to query.
Ex. file1.txt(number range)
9064500000 9064599999
9064600000 9064699999
9064700000 9064799999
Database name: ranges_log
a_no message
9064500001 test
9064700000 test1
1000000000 test2
2000000000 test3
Output: Since the number is in the number ranges(file1.txt)
9064500001 test
9064700000 test1
What i did was while read line but there's no output on the log file, but when i tried to manually put the number ranges it will output the exact query.
YYYYMMDD=`date +%Y%m%d`
YYYYMMDDHHMMSS=`date +%Y%m%d%H%M%S`
LOGFILE_PATH="/logpath"
LOG_NAME="${LOGFILE_PATH}${BASENAME_SCRIPT}_${YYYYMMDDHHMMSS}.txt"
while read line
do
msisdn_fr=$(echo "$line" | awk '{print $1}')
msisdn_to=$(echo "$line" | awk '{print $2}')
sqlplus -s test/testing@testbed << EOFEOF 1> ${LOG_NAME}
set pagesize 50000;
set feedback off;
set linesize 1000;
set numformat 99999999999999999999;
set pages 0
set lines 1000
set echo off
set trim on
select distinct a_no from ranges_log where a_no between ${msisdn_fr} and ${msisdn_to};
exit;
EOFEOF
done <"file1.txt"
I would suggest removing the credentials from the command line, else you publish them to anyone who can run a simple ps on your server. At the same time, you could actually stack up simple commands and decks of SQL if needed:-
sqlplus -s << EOSQL
test/testing@testbed
set feedback off
set linesize 999
set pages 2000
whenever sqlerror exit rollback
select cound from user_tables ;
@sql_deck1
@sql_deck2
EOSQL
You could even have a standard setup deck of SQL for much of the top bit and call that in first as a standard process.
on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk .
If you doesn't have Solaris/SunOS then please let us know which os you have with complete error details, I
have bash and it works for me.
Could you please enclose output file with "sql_input_file.sql" . It should work then. You can hit thank you button present at left corner if you want to thank anyone here.