I have a problem with the filename field in the following procedure:
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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;
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.
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.