Visit Our UNIX and Linux User Community


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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to use sql data file in unix csv file as input to an sql query from shell
# 1  
Old 11-04-2009
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  
Old 11-05-2009
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  
Old 11-09-2009
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

Previous Thread | Next Thread
Test Your Knowledge in Computers #398
Difficulty: Medium
HP-UX offers operating system-level virtualization features such as hardware partitions, isolated OS virtual partitions on cell-based servers, and HP Integrity Virtual Machines (HPVM) on all Integrity servers.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Not able to write SQL query output in to .csv file with shell script.

I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line. Main Code shell script: this is my code: #!/bin/bash file="db_detail.txt" . $file rm /batch/corpplan/bin/dan.csv... (6 Replies)
Discussion started by: sandeepgoli53
6 Replies

2. Shell Programming and Scripting

Run sql query in shell script and output data save as delimited text

I want to run sql query in shell script and output data save as delimited text (delimited text would be comma) Code: SPOOL_FILE=/pgedw/dan.txt SQL=/pgedw/dan.sql sqlplus -s username/password@myhost:port/servicename <<EOF set head on set COLSEP , set linesize 32767 SET TRIMSPOOL ON SET... (8 Replies)
Discussion started by: Jaganjag
8 Replies

3. Shell Programming and Scripting

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. Ex. file1.txt(number range) 9064500000 9064599999 9064600000 9064699999 9064700000 9064799999 Database name: ranges_log a_no message 9064500001 test 9064700000 ... (7 Replies)
Discussion started by: znesotomayor
7 Replies

4. Shell Programming and Scripting

SQL query in UNIX script - output in flat file

Hi, I never did this before... what I want to do is execute a SQL query from a unix script and redirect sql query's output to a flat file (comma separated one) without the header info (no column names). I would also want not to print the query's output to the screen. snapshot of my script:... (13 Replies)
Discussion started by: juzz4fun
13 Replies

5. 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

6. Shell Programming and Scripting

How can I create a CSV file from PL/Sql in UNIX?

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,:) (2 Replies)
Discussion started by: Atrap
2 Replies

7. 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

8. 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

9. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

10. Shell Programming and Scripting

TO execute .sql 2005 query file in shell script

Hi, I know in oracle a .sql file is called by @ <path> /<filename>. But how to call in sql 2005, I am opening the sql sessionwith sqsh, is there any command to execute there a .sql file (query in sql 2005) in K shell script. (0 Replies)
Discussion started by: n2ekhil
0 Replies

Featured Tech Videos