Q

Date format causing filename problem in procedure

This Content Component encountered an error

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;
The problem is that I need the filename to be in the following format: 'YYYYMONDD.csv'. The procedure will not accept the following string:
 
(to_char((sysdate-2),'YYYYMONDD')||'.csv')
I have to call another procedure, TAS, to do this formating:
 
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;
 
This procedure, however, is not giving the correct format date, as it dates the file as sysdate-3 instead of sysdate-2. I would be most grateful for any suggestions.

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;
Notice how I used a variable in the INSERT statement instead.

For More Information


This was first published in March 2003

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close