I have a problem with the filename field in the following procedure:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.