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

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.
tombomanAsked:
Who is Participating?
 
TintinCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
tombomanAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
tombomanAuthor Commented:
ah! sweet. thanks tintin and thanks kent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.