Login or Register to Ask a Question and Join Our Community


Programming

How to pass parameter from file to sqlplus in UNIX?

Login to Discuss or Reply to this Discussion in Our Community

 
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