Shell Programming and Scripting

Hi,
I need to display specific columns using select statement and spooled to a file and sending it as e-mail. But i am not seeing column header in my output even i use SET HEADING ON.

Code:

//PREDEFINED LOGIN DETAILS
${ORACLE_HOME}/bin/sqlplus -s ${DB_LOGIN}/${DB_PASSWD} <<EOF
SET FEEDBACK OFF
SET HEADING ON
spool /tmp/OUTPUT.TMP
SELECT column1,column2,column3 FROM table1;
quit
EOF

Later I use cat /tmp/OUTPUT.TMP and send email.

Current Output:

Code:

value1 value2 value3 
value1 value2 value3

Expected O/P:

Code:

column1 column2 column3
value1   value2    value3

Thanks in advance for help...

Can I suggest something first? Either move the credentials to the next line like this:-

Code:

sqlplus -s <<EOF
${DB_LOGIN}/${DB_PASSWD}
SET FEED......

.... or remove the need altogether by creating an account that validates against the OS user (for a local database)

Code:

CREATE USER OPS$FRED IDENTIFIED EXTERNALLY ..........

... and you can then:-

Code:

sqlplus -s / <<EOF
SET FEEDBACK ......

If you don't, then your credentials will be available to anyone running a simple ps command.

Your described problem might actually be the fact that you are both setting feedback off and running with the -s flag for the sqlplus command that means you don't get the column headings. Another thing you might need to set could be the PAGESIZE value to ensure that you only get the headings once, rather than every 10, 30 or 60 rows or whatever your default PAGESIZE is. The maximum I can set is 50000, where zero, one or two turns them off.

I can't quite get the same output issue as you though. I'm using Oracle 11 on RHEL.



Robin