Date format causing filename problem in procedure

I have a problem with the filename field in the following procedure:

 
CREATE OR REPLACE  PROCEDURE "TSAST_ODB"."SCHEDULE" 
         AS
   l_job  number;
 begin
    dbms_job.submit( l_job,
                     'run_dump_csv(JOB);',
                     trunc(sysdate)+1+2/24,
                     'trunc(sysdate)+1+2/24');
    commit;
    insert into
dump_csv_parameters(job_id,query,separator,dir,filename)
    values(l_job,'select * from billing_record where
 insert_time >= trunc(sysdate-2) and insert_time < 
trunc(sysdate-1);',',','/tmp','tas.csv');
    commit;
    TAS;
    end;

 
(to_char((sysdate-2),'YYYYMONDD')||'.csv')

 
CREATE OR REPLACE  PROCEDURE "TSAST_ODB"."TAS" 
  as
     x VARCHAR2(100);
  Begin
  update dump_csv_parameters
  set  FILENAME=
 (to_char((sysdate-2),'YYYYMONDD')||'.csv')
  where job_id = (select max(job_id) from
 dump_csv_parameters);
  commit;
 end;
 

If the INSERT statement is having a problem with the TO_CHAR function, then why not perform this before the INSERT statement? I'd declare a VARCHAR2 variable (we'll call it 'filenm') and use that. Something like the following should work:

filnm:=TO_CHAR(sysdate-2,'YYYYMONDD');
INSERT INTO dump_csv_parameters
(job_id,query,separator,dir,filename)
VALUES(l_job,'select * from billing_record where
insert_time >= trunc(sysdate-2) and insert_time <
trunc(sysdate-1);',',','/tmp',filenm);
COMMIT;

For More Information

• Dozens more answers to tough Oracle questions from Brian Peasland are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.