Generating a file from SQL spool content 1

[tokerago13]

Hi All,
I'm trying to do the following:
Generate an output file from an SQL statement in a unix script.
The name of the file should be dependent on one of the parameters that is used to call the UNIX script. I have enclosed what I have so far.

-- This statement should give me an output, but its not.
I have declared unix variable thus:
PAYROLL_ID is a declare variable

PAY_GROUP="";export PAY_GROUP

sqlplus -s $ORAUSR_PWD << EOF > $PAY_GROUP

set head off

SELECT substr(payroll_name,-3,length(payroll_name))pay_group
FROM pay_payrolls_f
WHERE payroll_id = $PAYROLL_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
EXIT;
EOF

Nothing is generated in PAY_GROUP when I echo it.

If anything should be generated for PAY_GROUP, I would like to concatenate the name and use it in another
sql statement for spooling thus:

sqlplus -s $ORAUSR_PWD << EOF
WHENEVER SQLERROR EXIT 1;
SET SERVEROUTPUT ON

SPOOL `$FILENAME`

SELECT sysdate
from DUAL;

spool off
EXIT;
EOF

File name has been define as a directory, but I would like to append PAY_GROIP to this and create a spooled file.
I then need to manipulate the generated file.
I'm having difficulty generating the file name.
Any help would be much appreciated.

Thanks

 

[hoinz]

Hello,

there seem to be (at least) two problems here:

PAY_GROUP="";export PAY_GROUP
How is an empty string supposed to be be used as a file name?
Shouldn't there be a name between the "" ?

SPOOL `$FILENAME`
backticks won't do what you want here.
Try ' or "", or try without.

hope this helps

 

[tokerago13]

Thanks for this I've managed to get it working thus:
PAY_GROUP=`sqlplus -s $ORAUSR_PWD << EOF
set heading off
set feedback off
SELECT substr(payroll_name,-3,length(payroll_name))
FROM pay_payrolls_f
WHERE payroll_id = $PAYROLL_ID
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
EOF`
The outcome is used to create a file name.
Thanks

 

[hoinz]

ah ...
So I completely misunderstood what your PAY_GROUP variable was for.
But thanks for the star!