Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Read input file and used it to SQL query

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Read input file and used it to SQL query
# 1  
Old 09-07-2015
Read input file and used it to SQL query
Hi All,

Seeking for your assistance to read each line $1 and $2 of input file and used it to query.

Code:
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.
Code:
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"

Please advise,

Thanks,
Last edited by znesotomayor; 09-07-2015 at 10:34 AM..
# 2  
Old 09-07-2015
Hello znesotomayor,

You could try below and let me know if this helps you.
Code:
awk 'BEGIN{print "set pagesize 50000;" ORS "set feedback off;" ORS "set linesize 1000;" ORS "set numformat 99999999999999999999;" ORS "set pages 0" ORS "set lines 1000" ORS "set echo off" ORS "set trim on" > sql_input_file.sql} {print "select distinct a_no from ranges_log where a_no between " $1 " and " $2 ";" >> sql_input_file.sql}' Input_file

Above will create sql file named sql_input_file.sql, then you can use sql command to execute it. Just an example as follows:
Code:
 sqlplus -s test/testing@testbed < sql_input_file.sql

Thanks,
R. Singh
Last edited by RavinderSingh13; 09-07-2015 at 10:32 AM..
This User Gave Thanks to RavinderSingh13 For This Post:
znesotomayor
# 3  
Old 09-07-2015
Hi Sir RavinderSingh13,

thank you for your reply.

i got
Code:
awk:                                                                                                                                                                                                                                                                                                        ^ syntax error

Please advise,

Thanks,
Last edited by rbatte1; 09-07-2015 at 11:03 AM.. Reason: Added CODE tags for output/error to preserve the indentation
# 4  
Old 09-07-2015
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:-
Code:
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.



I hope that this helps,
Robin
This User Gave Thanks to rbatte1 For This Post:
znesotomayor
# 5  
Old 09-07-2015
Quote:
Originally Posted by znesotomayor
Hi Sir RavinderSingh13,
thank you for your reply.
i got
awk: ^ syntax error
Please advise,
Thanks,
Hello znesotomayor,

on a Solaris/SunOS system, change awkto /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.

Code:
awk 'BEGIN{print "set pagesize 50000;" ORS "set feedback off;" ORS "set linesize 1000;" ORS "set numformat 99999999999999999999;" ORS "set pages 0" ORS "set lines 1000" ORS "set echo off" ORS "set trim on" > sql_input_file.sql} {print "select distinct a_no from ranges_log where a_no between " $1 " and " $2 ";" >> sql_input_file.sql}' Input_file
sqlplus -s test/testing@testbed < sql_input_file.sql

Thanks,
R. Singh
Last edited by RavinderSingh13; 09-07-2015 at 10:33 AM.. Reason: Corrected output SQL file name now.
This User Gave Thanks to RavinderSingh13 For This Post:
znesotomayor
# 6  
Old 09-07-2015
Hi Sir Ravin,

awk is working on me but i when i tried your command i encountered syntax error. hmm...

Thanks,
-nik

---------- Post updated at 09:48 PM ---------- Previous update was at 09:38 PM ----------

Hi Sir Ravin,

Here's my OS
Code:
Linux VMTRGADV01 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux

yes awk if perfectly working on me.

Thanks,

---------- Post updated at 09:59 PM ---------- Previous update was at 09:48 PM ----------

Hi Sir Ravin,

Error Encountered:
Code:
awk 'BEGIN{print "set pagesize 50000;" ORS "set feedback off;" ORS "set linesize 1000;" ORS "set numformat 99999999999999999999;" ORS "set pages 0" ORS "set lines 1000" ORS "set echo off" ORS "set trim on"} {print "select distinct a_no from ranges_log where a_no between " $1 " and " $2 ";" >> sql_input_file.sql}' Input_file
awk: BEGIN{print "set pagesize 50000;" ORS "set feedback off;" ORS "set linesize 1000;" ORS "set numformat 99999999999999999999;" ORS "set pages 0" ORS "set lines 1000" ORS "set echo off" ORS "set trim on"} {print "select distinct a_no from ranges_log where a_no between " $1 " and " $2 ";" >> sql_input_file.sql}
awk:                                                                                                                                                                                                                                                                                                                ^ syntax error


Moderator's Comments:
Mod Comment
Please wrap all code, files, input & output in CODE tags.
It makes it far easier to read and preserves multiple spaces for indenting or fixed width data, or in this case the pointer to the error.
Last edited by rbatte1; 09-07-2015 at 11:05 AM.. Reason: Added CODE tags
# 7  
Old 09-07-2015
Hello znesotomayor,

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.
Code:
awk 'BEGIN{print "set pagesize 50000;" ORS "set feedback off;" ORS "set linesize 1000;" ORS "set numformat 99999999999999999999;" ORS "set pages 0" ORS "set lines 1000" ORS "set echo off" ORS "set trim on" > "sql_input_file.sql"}{print "select distinct a_no from ranges_log where a_no between " $1 " and " $2 ";" >> "sql_input_file.sql"}' Input_file

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
znesotomayor
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How do I read sql query into shell script?

Hello All, I'm trying to put together a shell script that will: 1. connect to an oracle database 2. execute a query 3. save the output to a csv file I know that I can execute the sqlplus -s user/pass @dbsid and get logged in. What I would like to do is have my query in a separate text... (9 Replies)
Discussion started by: bbbngowc
9 Replies

2. Shell Programming and Scripting

Read input files and merge them in given order and write them to input one param or one file

Dear Friends, I am looking for a shell script to merge input files into one file .. here is my idea: 1st paramter would be outfile file (all input files content) read all input files and merge them to input param 1 ex: if I pass 6 file names to the script then 1st file name as output file... (4 Replies)
Discussion started by: hyd1234
4 Replies

3. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

4. Shell Programming and Scripting

Need help to run sql query from a script..which takes input from a file

I need to run sql script from shell script which takes the input from a file and contents of file will be like : 12345 34567 78657 and query will be like : select seq_nbr from bus_event where event_nbr='12345'; select seq_nbr from bus_event where event_nbr='34567'; select seq_nbr... (1 Reply)
Discussion started by: rkrish
1 Replies

5. Shell Programming and Scripting

Read value from user and use it in Oracle SQL query

Guys can anyone just tell me whether i can pass a value(from UNIX SCRIPT) as an ARGUMENT in Oracle Query? e.g. echo "enter value" read value insert into tablename where col=$value /*something like this*/ (1 Reply)
Discussion started by: subodh.thakar
1 Replies

6. UNIX for Advanced & Expert Users

Output the SQL Query result to a File

Hello Guys, This message is somewhat relates with last thread. But I need to re-write thing. I start over a little. I am stuck now and need your help. Here is my script- #! /bin/ksh export ORACLE_HOME=/opt/oracle/app/oracle/product/9.2 /opt/oracle/app/oracle/product/9.2/bin/sqlplus -s... (5 Replies)
Discussion started by: thepurple
5 Replies

7. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

8. Shell Programming and Scripting

how to use data in unix text file as input to an sql query from shell

Hi, I have data in my text file something like this. adams robert ahmed gibbs I want to use this data line by line as input to an sql query which i run by connecting to an oracle database from shell. If you have code for similar scenario , please ehlp. I want the output of the sql query... (7 Replies)
Discussion started by: rdhanek
7 Replies

9. UNIX for Dummies Questions & Answers

How do I use SQL to query based off file data?

This is basically what I want to do: I have a file that contains single lines of IDs. I want to query the oracle database using these IDs to get a count of which ones match a certain condition. the basic idea is: cat myfile | while read id do $id in select count(PC.ptcpnt_id) from... (4 Replies)
Discussion started by: whoknows
4 Replies

10. Shell Programming and Scripting

unloading sql query to file

In unload to "/usr/home/data.012202" I wish to use a date variable as in unload to "/usr/home/data.`date`" for the file is that possible in a query to do, or will i need to add a mv command after the query to do it. I tried backquotes, , and () on `date` but didn't seem to work Thanks... (2 Replies)
Discussion started by: Link_02
2 Replies
Login or Register to Ask a Question