How to pass parameter from file to sqlplus in UNIX?


 
Thread Tools Search this Thread
Top Forums Programming How to pass parameter from file to sqlplus in UNIX?
# 1  
Old 11-03-2014
How to pass parameter from file to sqlplus in UNIX?

i have file in which i have employee id are there and every time number of employee id are different in file means number of count of employee id in file are every time different.
Code:
 
343535435 
365765767
343534543 
343543543

i want to pass this file to sqlplus
and sql command is


Code:
 

SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN (ALL RECORDS FROM ABOVE FILE)

and i am calling this .sql file from unix


sqlplus -s user/password@servername @.sql
please let us know how i can pass above mentioned filename in unix to sqlplus
# 2  
Old 11-03-2014
Why new/same posting again?

Note: following code is partly involving RudiC's code from https://www.unix.com/302923560-post4.html

Code:
$ echo "SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( $(sed '/^\s*$/d;s/ *//g' file | paste -sd,) )" > acc.sql
$ 
$ cat acc.sql
SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( 343535435,365765767,343534543,343543543 )
$

or the same task divided in two pieces:
Code:
$ accnbrs="$(sed '/^\s*$/d;s/ *//g' file | paste -sd,)"
$ echo "SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( "$accnbrs" )" > acc.sql
$ cat acc.sql
SELECT * FROM TABLENAME WHERE EMPLYOE_ID IN ( 343535435,365765767,343534543,343543543 )
$

# 3  
Old 11-04-2014
HI Junior,

Thanks, i pasted here because they peoiple told me to put in sql forum, that is why i pasted same question here again.

appreciate if you let me know how what sed command is doing here

Code:
sed '/^\s*$/d;s/ *//g' file

---------- Post updated at 06:28 AM ---------- Previous update was at 06:05 AM ----------

this is removing space in start of every line
Code:
s/ *//g

but what this is doing, i know d for delete and ^ means starting of line and \s is space character but what this command is doing, please let me know.
Code:
/^\s*$/d;

# 4  
Old 11-04-2014
Actually, I used s/ *//g to remove the trailing whitespaces from the records you posted. In fact it should also delete whitespaces in the beginning of the line, but it will not delete a line which has whitespaces.

/^\s*$/d deletes "real" empty lines, as well as empty lines which contain whitespaces or tabs, such as the one above the records you have posted.
# 5  
Old 11-04-2014
Hi Junior,

thanks a lot,

can you also help me how i can put single quotes with the help of sed command in each record

like this
Code:
'3423432432432'
'2343243243243'
'3243243242343'

if my file is without single quote

Code:
sed  's/^*/\'/  filenname

above command is not working
# 6  
Old 11-04-2014
Just substitute sed '/^\s*$/d;s/ *//g' file by sed '/^\s*$/d;s/ *//g;s/.*/\x27&\x27/' file
# 7  
Old 11-04-2014
Hi Junior,

I have modified my script like this

and file has below content
Code:
'324324324'
'232423434'
'232424444'
'234324444'

UNIX SCRIPT
Code:
d=`paste -sd, file.txt`
sqlplus -s username/password@servername @file.sql $d >> file1.txt

file.sql
Code:
define id=&1
select * from acct_table where acct in ($id)
exit

But i am not getting any o/p, seems like variable is not extended in sql, please let me know where i did mistake.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How pass the input parameter to a file in the script ?

OS version: RHEL 6.7 myTextFile.txt file is referred within Script1.sh script, I only execute Script1.sh and I want the input variable to be passed inside myTextFile.txt . Any idea how I can do this ? $ cat script1.sh cat myTextFile.txt $ cat myTextFile.txt $1 Requirement1.... (4 Replies)
Discussion started by: kraljic
4 Replies

2. Shell Programming and Scripting

How to pass the parameter in xml file in UNIX shell script?

Hi, I have an XML file like the following... <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ONDEMAND_JOB VERSION="5.1" LOCALE="en_US"> <IMPORT_JOBSET TC_CONNECTION_NAME="default" ENVIRONMENT="PRD" USERNAME="Administrator" PASSWORD="AdminPassword" CALENDAR="Main Monthly Calendar"... (3 Replies)
Discussion started by: Debalina Roy
3 Replies

3. Shell Programming and Scripting

How to pass the parameter in xml file in UNIX shell script?

Hi, I have an XML file like the following... <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ONDEMAND_JOB VERSION="5.1" LOCALE="en_US"> <IMPORT_JOBSET TC_CONNECTION_NAME="default" ENVIRONMENT="PRD" USERNAME="Administrator" PASSWORD="AdminPassword" CALENDAR="Main Monthly Calendar"... (2 Replies)
Discussion started by: Debalina Roy
2 Replies

4. UNIX for Dummies Questions & Answers

SQLPLUS Password Parameter file being used when logging in

Good day to everyone. This is my first time posting and just barely above basic Unix training. I think i have search thoroughly to ensure my question hasn't already been posted. But on the off chance the answer has been posted, please be nice as I am not 100% sure I know what I am looking for. I... (1 Reply)
Discussion started by: Mrjester
1 Replies

5. UNIX for Dummies Questions & Answers

Passing a Unix parameter to SQLPlus login command

hi All, i m trying to pass a user choice paramter from unix to sqlplus connect command here i want the user to enter the username and password he wants to connect in sql plus through read in unix and then automatically connect to that instance. sqlplus -s $1/$2 where $ 1 and $2 will b... (2 Replies)
Discussion started by: Jcpratap
2 Replies

6. Shell Programming and Scripting

Pass value from file to parameter

Hi Guys, I have a file in the format Parmater=value. I want to read the value and pass it to corresponding Variable. The Parameter file is as follows Number=23 Text1=mango Text2=yup 'Number' value needs to be read and passed to ID variable. Also, 'Text1' value needs to be passed to... (9 Replies)
Discussion started by: mac4rfree
9 Replies

7. Shell Programming and Scripting

How to pass parameter from sqlplus(procedure completed) to your shell script

if then # mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com echo "FILE does not exist" exit 1 fi echo "FILE EXIST" size=-1 set $(du /export/home/oracle/nas/scott21.dmp.gz) while do echo "Inside the loop" size=$1 set $(du... (1 Reply)
Discussion started by: sanora600
1 Replies

8. Shell Programming and Scripting

To pass the .sql file as a paramter to sqlplus through shell programming

Hi, Currently i have a .sql file 1.sql. I need to pass that as a parameter through a shell script to the sqlplus inside the same shell script. How I should I do.can anyone help me pls. I have an req where I need to send the .sql file and the place where the script has to create a .csv... (9 Replies)
Discussion started by: Hemamalini
9 Replies

9. Shell Programming and Scripting

Pass tablename as a parameter in the Control File -- sqlldr

Just wanted to know if there is a way to pass the table name as a parameter in the control file. I have two tables...Table A and Table B. LOAD DATA append INTO TABLE TABLE_B FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"' AND '"' TRAILING NULLCOLS Rather than hard coding... (1 Reply)
Discussion started by: madhunk
1 Replies

10. UNIX for Advanced & Expert Users

How to pass unix variable to SQLPLUS

hi fellows, can any body tell me how to pass unix variables to oracle code is... #! /bin/ksh echo ENTER DATE VALUE's read START_DATE END_DATE sqlplus xyx/abc@oracle select * from table1 where coloumn1 between $START_DATE and $END_DATE; is this is correct way........... Thanks in... (1 Reply)
Discussion started by: chiru
1 Replies
Login or Register to Ask a Question