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


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
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 pqr/Hello123 << EOF
spool on
set linesize 60
spool $FILE_PATH/CC_successful.csv
set head off
set pagesize 9999

select ename,eid,edate from emp where trunc(edate) = trunc(to_Date(sysdate))

EXIT
EOF

print "CC successful Orders" > $FILE_PATH/Dash.csv
cat $FILE_PATH/CC_successful.csv >> $FILE_PATH/Dash.csv
print "*************************" >> $FILE_PATH/Dash.csv

cd $FILE_PATH/
uuencode Dash.csv Dash.csv| mail -s "Dash for `date +%C%y%m%d` " pnareshnaidu@gmail.com"


Can some one please suggest me where i need to make changes in the above script
# 2  
after your 'set linesize' line add
Code:
set colsepchar ','

something like that should work. your column separator is probably defaulting to tab or fixed width.

check out Oracle Commands for more info
# 3  
The ouput expecting is

After running the below sql query

select ename,eid,edate from emp where trunc(edate) = trunc(to_Date(sysdate))


output is

Ename Eid Edate
-----------------
xyz 987 8-11-2009
abc 453 8-11-2009


Expecting output is

Ename Eid Edate
-----------------
xyz 0987 08-11-2009
abc 0453 08-11-2009

Where 0(zero) was missing in the column Eid and Edate after export the results to csv file.

Please suggest me
Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Read input file and used it to SQL query
znesotomayor
Hi All, 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 ...... Shell Programming and Scripting
7
Shell Programming and Scripting
Need help to run sql query from a script..which takes input from a file
rkrish
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...... Shell Programming and Scripting
1
Shell Programming and Scripting
How can I create a CSV file from PL/Sql in UNIX?
Atrap
Can someone help me on creating a script that will manage/create a csv file from Pl/Sql using UNIX?Any advice is welcome,thank you so much,:)... Shell Programming and Scripting
2
Shell Programming and Scripting
how to use data in unix text file as input to an sql query from shell
rdhanek
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...... Shell Programming and Scripting
7
Shell Programming and Scripting
How do I use SQL to query based off file data?
whoknows
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...... UNIX for Dummies Questions & Answers
4
UNIX for Dummies Questions & Answers