Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Output of Unix & SQL in same file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Output of Unix & SQL in same file
# 1  
Old 05-04-2010
Output of Unix & SQL in same file
output of Unix & sql in same file

hi all,
am working on shell script, i need to format data in such a way
that both my Unix commands output & my sql output should be in a same file.
I am able to redirect both output in separate files.

for sql output: sqlplus -s $username/$password@$db << EOF >
root_dir/fine_name.txt
for Unix output: test.sh 2>&1 | tee -a trial.txt

i need contents of trial.txt & file_name.txt in one same file.
Thanks in advance
# 2  
Old 05-04-2010
Is the SQL part inside "test.sh" ?
what do you mean by unix output?

you can use the same file name for the whole script.
Append the output the the file respectively.
# 3  
Old 05-04-2010
Thanx for quick reply anchal

yes, test.sh has sql inside it.
i cannot append the unix output to it, By unix output i mean the result tht
i obtained using unix commands. Example: uname -n.

a script performs the task & gives me the result in file_name.txt. i want
my other data tht i extract using unix commands such as hostname on which
the script is executed to be embeded in the same file.

I'm tryin to use variables where in i store the result of sql & then print
it on the file, its der ant better way...??
# 4  
Old 05-04-2010
Can't you do like this?
Code:
uname -n > myfile.log
hostname >> myfile.log
sqlplus -s $username/$password@$db << EOF > root_dir/fine_name.txt
cat root_dir/fine_name.txt >> myfile.log
other unix commands >> myfile.log

# 5  
Old 05-04-2010
Are you using a spool file for the sqlplus output? You can create the file in the script using the append operator (>>) and use the same filename in your sqlplus statements (SPOOL file APPEND) to save your sql output in the same file. Execute the SPOOL OFF command and then you can append more information to the file in your script again with the append operators (>>). It works well, allowing you to append the hostname and date and time stamps as a header in the file.
# 6  
Old 05-04-2010
hey thanks a lot anchal that really worked. My problem has been solved.
That was exactly kinda output i was looking for...
thank again

to dday: I'm not using spool, coz its giving me some error, & my query has
been resolved using anchal's technique above...

thanks a lot guys...
# 7  
Old 05-04-2010
delineation
For parsing later you could add some delineation.

Code:
function stamp() {

    if [ "$1" == "" ]; then
        echo "Usage: stamp (BEGIN | END) [ COMMENT ]"
        echo ""
        exit 1
    fi
    ts=$(date +'%F %R:%S')
    echo "--- $1 $ts --- $2"
}

sqlstatement="select * from table;"
stamp BEGIN "$sqlstatement" >> myfile.log
# SQL command >> myfile.log
stamp END >> myfile.log

Then you could rip the SQL output from the log file with a simple AWK program if you wanted.

Time stamps from the above look like...

Code:
--- BEGIN 2010-05-04 16:40:18 --- SQL: select * from table;
--- END 2010-05-04 16:40:20 ---

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Print Error in Console and both Error & Output in Log file - UNIX

I am writing a shell script with 2 run time arguments. During the execution if i got any error, then it needs to redirected to a error file and in console. Also both error and output to be redirected to a log file. But i am facing the below error. #! /bin/sh errExit () { errMsg=`cat... (1 Reply)
Discussion started by: sarathy_a35
1 Replies

2. Shell Programming and Scripting

SQL output to UNIX variable

I have a sql statement , i need to assign to a variable in Unix sel count(*) AS num_files from TABLE_A; i need to use "num_files" in unix statements. let me know how to assign unix variable to above num_files (1 Reply)
Discussion started by: nani1984
1 Replies

3. Shell Programming and Scripting

SQL query output convert to HTML & send as email body

Hi , I have a sql query in the unix script ,whose output is shown below.I want to convert this output to HTML table format & send email from unix with this table as email body. p_id src_system amount 1 A 100 2 B 200 3 C ... (3 Replies)
Discussion started by: jagadeeshn04
3 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

Redirect output from SQL to unix variable

Hi, I have a requirement to store oracle sqlplus output to some unix variable, count the records and then print the output on the screen. Can you please point me to any sample program for reference purpose. Thanks a lot for your time. (0 Replies)
Discussion started by: bhupinder08
0 Replies

6. Shell Programming and Scripting

Awk script to run a sql and print the output to an output file

Hi All, I have around 900 Select Sql's which I would like to run in an awk script and print the output of those sql's in an txt file. Can you anyone pls let me know how do I do it and execute the awk script? Thanks. (4 Replies)
Discussion started by: adept
4 Replies

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

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

9. UNIX for Dummies Questions & Answers

Aggregate values in a file & compare with sql output

Hi, I have a file containing the following data: junk123junk723itemcode001qty01price10total10junkjunk junk123junk723itemcode002qty02price10total20junkjunk .. .. .. could be 5000+ lines I have an algo and need a code to implement this: 1. Linecount = wc -l (should give 5000) 2. For i... (1 Reply)
Discussion started by: shiroh_1982
1 Replies

10. Shell Programming and Scripting

small script - get sql output & write into txt

Hi, how can I write a small script to run the following statement and output the result into check_result.txt select /*+RULE*/ tapname from typetbl where tapname like 'AA%' and rejectcode=9; Normally, I just type sql and get into SQL> (2 Replies)
Discussion started by: happyv
2 Replies
Login or Register to Ask a Question