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;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
- 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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments