UNIX for Dummies Questions & Answers

Ahhh okay, I thought you weren't getting output to the spoolfile at all.

Well, you'll have to change your select statement and not use ' select * '. You'll have to name each field individually in the select statement and use the concatination mechanism to add commas after each field like so ...

Code:

spool outputfile.csv
select field1 || "," || field2 || "," || field3
from dba_tablespaces
where tablespace_name not in ("TEMP");
spool off
exit

Thanx for your reply...

Do you mean sumthin like diz?

select st.description|| "," ||fr.businessname|| "," ||fr.comments|| "," || fr.customernumber|| "," || fr.progress|| "," || fr.reportdate|| ",
" || fd.description|| "," || ft.description|| "," || cd.contactperson|| "," || cd.fname|| "," ||cd.sname|| "," || cm.contactmethods|| "," ||c
ond.cellularnumber|| "," ||cond.email


I have tried it but its not working.

> I have tried it but its not working.

Can you post the exact select statement like that, that is not working?

Thnx for your quick responce...this is what I have

SET TERMOUT OFF
SPOOL /app/oracle/dummy.csv

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF

select st.description|| "," ||fr.businessname|| "," ||fr.comments|| "," || fr.customernumber|| "," || fr.progress|| "," || fr.reportdate|| "," || fd.description|| "," || ft.description|| "," || cd.contactperson|| "," || cd.fname|| "," ||cd.sname|| "," || cm.contactmethods|| "," ||cond.cellularnumber|| "," ||cond.email
from olf_faultreport fr,
olf_servicetype st,
olf_contactmethods cm,
olf_contactdetails cond,
olf_faultdesc fd,
olf_customerdetails cd,
olf_faulttype ft
where FR.FDID=FD.FDID
and FT.FTID=FD.FDID
and CD.CUSTOMERNUMBER=FR.CUSTOMERNUMBER
and cm.cmid=cd.CMID
and COND.CUSTOMERNUMBER=FR.CUSTOMERNUMBER
and FR.STID=ST.STID
order by ST.DESCRIPTION, reportdate desc;



exit
SPOOL OFF

Syntax-wise the use of the ' || "," || ' is correct, and should not be the problem here.

However, that is one big select statement. w/o knowing the table structures and data, I couldn't begin to tell you what might be wrong with the statement, but if your not getting data, I can say that it's most likely because your select statement isn't behaving as you expect it to.

When I get a select statement like this, and it's not doing what I expect it to, I start to simply things. In your case, I'd narrow it down as follows:

Start by selecting only the fields from the ' fr ' and ' fd ' tables joined by the ' FDID ' column. If it doesn't work, you may have found the source of the problem, depending upon what if any errors get reported. If it does work, then add the next table ' ft ' and it's columns and join conditions to the statement, and keep doing this until your statement fails.

When it fails, if you get an error message that might help solve the problem. If no error message, than it's possible that the join condition for the table just added is wrong, or the data it contains is not what you expect. You'll have to play with it to figure it out.

That's the best I can do for you on this.

thanx...I will play around with the select statement and let know.

Hi! peolpe

I m stuck again.Can somebody help me out.I need right a script that attaches a csv file to a mail then e-mail it.

I have tried the mailx option but I m not gettin any mail on my mailbox