?
Solved

How to echo text into a file before the sql spool command?

Posted on 2006-05-28
5
Medium Priority
?
1,726 Views
Last Modified: 2013-11-18
I have a sql script within a shell script that querys an oracle database and saves the result to a text file using spool function. this work fine.

but before spooling the result, I want to give it the names of each column as follows using the following command:

echo "emplyee_id | employee_name | employee_address  "> employee_detail.txt

I can't include the above text in the employee_detail.txt (just the result of sql spool command). is it because the spool automatically empties a file before spooling the result? how do I include the above text?

thanks.
0
Comment
Question by:tomboman
  • 2
  • 2
5 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 16781299
Hi tomboman,

If you want to do it from the shell, spool the output to a temp file, then echo the line you've shown and append the spooled output.

Or you can do it from the SQL.


Good Luck!
Kent
0
 

Author Comment

by:tomboman
ID: 16781342
Hi Kent,

For first option: it will append at the end of the file right? this is not ideal as these text are the header.

Second option: using SQL. can you show me how? may be paste the script here?

thanks.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 16781443
Hi tomboman,

Method 1.  :)

1)  Run Oracle and spool the file employee_detail.txt
2)  From within the script run:
     echo "emplyee_id | employee_name | employee_address  " > employee_detail.lst
     cat employee_detail.txt >> employee_detail.lst


Method 2.  Note that recasting of the employee_id may be necessary

SELECT employee_id, employee_name, employee_address
FROM
(
  SELECT 1 as weight, a as "Employee ID", b as "Employee Name", c as "Employee Address"
  VALUES ("Employee ID", "Employee Name", "Employee Address")
  UNION ALL
  SELECT 2 as weight, employee_id as "Employee ID",
              employee_name as "Employee Name", employee_address as "Employee Address"
  FROM {table}
  WHERE {condition}
} as a
ORDER BY weight, employee_id, employee_name, employee_address


Kent
0
 
LVL 48

Accepted Solution

by:
Tintin earned 120 total points
ID: 16781671
Or don't use SQL to spool and just do something like:

echo "emplyee_id | employee_name | employee_address  "> employee_detail.txt
sqlplus -s / >>employee_detail.txt <<EOF
...
EOF
0
 

Author Comment

by:tomboman
ID: 16781759
ah! sweet. thanks tintin and thanks kent.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question