Spool error ORA-01722: invalid number

[marcon]

I am trying to create a comma delimited file but I get
ORA-01722: invalid number error. Looked on line, in book, in Faq for solution with no luck, suspect syntax.
Can anyone spot my error?
Also loc is a VARCHAR2 field. Do I need to include " around
it?

Any guidance appreciated!



SET LINESIZE 136
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERMOUT OFF
SPOOL C:\BIN\ORANT\SPLTEST2.CSV
select '"' || loc || '",' || TO_CHAR(dmdunit) || ',' || TO_CHAR(startdate)
from hist
where dmdunit = '45AD1'
and
startdate between '01-JAN-02' AND '01-OCT-02'
AND
LOC = 'A082';
SPOOL OFF
SET LINESIZE 80
SET PAGESIZE 24
SET FEEDBACK ON
SET TERMOUT ON
/

 

[rcurva]

From your query, DMDUNIT is already a string, so no need of the TO_CHAR() in the SELECT part.

where dmdunit = '45AD1'


Should be:

select '"' || loc || '",' || dmdunit || ',' || TO_CHAR(startdate)
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[marcon]

Thanks Robbie, nice quote!