oracle spool query

Question

Hi All, I have written the sql by name cashload.txt on unix box and kept it on the following location on unix box:

exit |sqlplus -s batch/password@SW_TEST @/soft/checkfree/AccurateBXG/scripts/cashload.txt

In the cashload.txt the below code is written:

spool /Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv
select accountnumber||','||accountname||','||X from HSBC_Cash_OIbyAccount_v;
spool off

But it is not spooling the result set on the above mentioned path.However,when I am giving the path where the script is kept,It is spooling at that location.I don't understand why?It is spooling at the below path where the cashload.txt(sql script) is kept:

**spool /soft/checkfree/AccurateNXG/scripts/Rep_OIbyAccount.csv**
select accountnumber||','||accountname||','||X from HSBC_Cash_OIbyAccount_v;
spool off

Please look into the above query and help me out. Thanks in advance!!!

Answer 1

Can you echo the value of "/Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv" ?

Only reason I can think of are a) The above location is not a valid path b) You do not have permissions to write to that location.

Do you get an error in the first case, or does nothing happen at all ?

Answer 2

Does running without the silent (-s) option give you any more detail?

Answer 3

Almost certainly it is a file permissions problem. Remember that when we interact with the OS from inside the database we are using the oracle account, not the account we connected as.

So does the oracle account have write permissions on /Detail/reports/inner ?

Answer 4

If I understand this correctly, DATE_FILE is a shell variable that you want to substitute into the spool file name. This might be part or all of your problem. I don't think you can reference this directly from SQLPlus. You would need to pass that in as a parameter to the script then reference it as a SQLPlus substitution variable.

So the command line would be:

sqlplus -s batch/password@SW_TEST @/soft/checkfree/AccurateBXG/scripts/cashload.txt $DATE_FILE

And the spool command in the script would be:

spool /Detail/reports/inner/SW/Rep_OIbyAccount_&1..csv

(The extra period is necessary because it serves as a terminator to the substition variable name.)

Answer 5

hi in the path mentioned by you has "$" symbol which is causing the problem ...

to avoid that just give the path and file name in " " as follows that can solve the problem

SPOOL "/Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv "