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

Posted on 2006-05-28
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?

Question by:tomboman
    LVL 45

    Expert Comment

    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!

    Author Comment

    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?

    LVL 45

    Expert Comment

    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
      SELECT 1 as weight, a as "Employee ID", b as "Employee Name", c as "Employee Address"
      VALUES ("Employee ID", "Employee Name", "Employee Address")
      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

    LVL 48

    Accepted Solution

    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

    Author Comment

    ah! sweet. thanks tintin and thanks kent.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    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…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now