How to store the sql query output into txt file?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to store the sql query output into txt file?
# 1  
Old 05-09-2011
How to store the sql query output into txt file?

Hi
I want ot save SQL query result in one txt file. for that i have written one code line

Code:
sqlplus -s $dbstring @/usr/local/bin/sched/nightly_Cronjob/exec_123.sql >> /usr/local

/bin/sched/nightly_Cronjob/result.txt

but it is not working .
database : Oracle
so please advice me how can i solve this prob.SmilieSmilie

Thanks in advance

Last edited by Franklin52; 05-09-2011 at 10:49 AM.. Reason: Please use code tags
# 2  
Old 05-09-2011
Show us your sql file
# 3  
Old 05-09-2011
You can easily do this b spooling your result to text file from sql codes.
If this could mean what you want, then try it:

spool result.txt
<select ...... from .... >
spool off;
# 4  
Old 05-10-2011
I thnk this might help u out
Code:
sqlplus -s $dbstring @/usr/local/bin/sched/nightly_Cronjob/exec_123.sql 2>&1 >/path/to/save the file


Last edited by Franklin52; 05-10-2011 at 03:25 AM.. Reason: Please use code tags, thank you
# 5  
Old 05-10-2011
if u want to append to file u can use
Quote:
SPOOL <file> APPEND
SPOOL
# 6  
Old 05-10-2011
O/P will be saved in file_name.txt

Code:
sqlplus -s user/password@dbname <<EOF
set feedback off trimspool on 
spool file_name.txt;
select * from table_name;
spool off;
exit;
EOF

# 7  
Old 05-10-2011
Without spool.

Sql:
Code:
set linesize 2000 trims on  pagesize 0 feedback off
select * from global_name;
exit

Call:
Code:
sqlplus -s $dbstring @your.sql 2>&1 >> result.txt

Login or Register to Ask a Question

Previous Thread | Next Thread

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

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

3. Shell Programming and Scripting

need to store query output fields in variables edit them and update the same in tables.

Hi , I have a query like select err_qty,drop_qty,unbld_qty,orig_qty from usage_data; I need to store the values of these fetched fields in variables, Need to edit them and update the new values into the table. Can anyone please help me in writing this piece of code:( (1 Reply)
Discussion started by: Rajesh Putnala
1 Replies

4. Shell Programming and Scripting

Store output of DB Cursor to a txt file

I am writing a cursor to select values from 3 tables. I want to store these values in a txt file which I will be sending via ftp. I am able to store the results of simple select queries to the txt file. but I am not sure how to store the values when using a cursor. I have given the sql query below.... (1 Reply)
Discussion started by: naveensraj
1 Replies

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

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

7. Shell Programming and Scripting

redirecting sql query output to a file

Hi, I am executing sql files in my unix shell script. Now i want to find whether its a success or a failure record and redirect the success or failure to the respective files. meaning. success records to success.log file failure record to failure.log file. As of now i am doing like... (1 Reply)
Discussion started by: sailaja_80
1 Replies

8. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi Yogesh, Lucky that i caught you online. Yeah i read about DBI and the WriteExcel module. But the server is not supporting these modules. It said..."Cannot locate DBI"..."Cannot locate Spreadsheet::WriteExcel" I tried creating a simple text file to get the query output, but the... (1 Reply)
Discussion started by: dolphin123
1 Replies

9. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi , I just found you while surfing for the string 'Redirecting sql select query output from within a shell script to txt file/excel file' Could you find time sending me the code for the above question? It'll be great help for me. I have a perl file that calls the sql file... (1 Reply)
Discussion started by: dolphin123
1 Replies

10. Shell Programming and Scripting

How to store the sql query's output in a variable

Hi, My requirement is : We are calling an sql statement from a UNIX session, and fetching data into some variables from a table .. now we are unable to access these variables from outside the SQL part. Please let me know how can I achieve this. Can you please share a code snippet which... (4 Replies)
Discussion started by: venkatesh_sasi
4 Replies
Login or Register to Ask a Question

Featured Tech Videos