How to create csv file from oracle db
sanjai, November 21, 2001 - 12:44 am UTC
Thanks Tom.
How can I use dbms_job for executing the function dump_csv so that it creates a csv file for certain tables everyday.
November 21, 2001 - 9:47 am UTC
Easy, just schedule it!
Here is what I would do (so we can maximize shared sql reuse -- a certain habit of mine).
create table dump_csv_parameters
( job_id int primary key,
query clob,
separator varchar2(5),
dir varchar2(255),
filename varchar2(255),
last_time date,
last_row_count number
);
we will use this table to store the parameters for a dump_csv call. Also, for ease, we'll stuff the last time run and the last count of rows in here as well -- so we can easily verify row counts and times and such...
Next, we'll create a small interface routine to read the parameters for a given job and run dump_csv:
create or replace procedure run_dump_csv( p_job_id in number )
as
l_cnt number;
begin
for x in ( select * from dump_csv_parameters where job_id = p_job_id )
loop
l_cnt := dump_csv(dbms_lob.substr(x.query,32000,1), x.separator, x.dir, x.filename);
update dump_csv_parameters
set last_time = sysdate, last_row_count = l_cnt
where job_id = p_job_id;
end loop;
end;
/
And now we can actually schedule the job itself. Here we schedule every night at midnight. Note how we pass the VARIABLE job -- not a VALUE for a job -- into the procedure (if you are interested in how this all works -- I have a chapter on it in my book showing how the job queues actually do their stuff). When the job is run, it'll be passed its unique job ID. If we schedual all jobs in our system this way -- we'll have exactly one query in the shared pool -- "begin run_dump_csv( JOB ); end;" -- instead of
begin dump_csv( 'select * from t1', '|', '/tmp/', 't1.dat' ); end;
begin dump_csv( 'select * from t2', '|', '/tmp/', 't2.dat' ); end;
....
declare
l_job number;
begin
dbms_job.submit( l_job,
'run_dump_csv( JOB );',
trunc(sysdate)+1,
'trunc(sysdate)+1' );
insert into dump_csv_parameters
(job_id, query,separator,dir,filename)
values
( l_job, 'select * from emp', '|', '/tmp/', 'emp.dat' );
commit;
end;
/
and when this job runs, it'll create the file and update the table for us -- every night at midnight....
dump_csv_parameters
Tasneem Mia, March 17, 2003 - 3:28 am UTC
Thanks for the procedures and function. It is quite useful. However, I have 2 problems with the dump_csv_table:
insert into dump_csv_parameters(job_id,query,separator,dir,filename)
values(l_job,'SELECT pin,to_char(start_time,'yyyymmdd hh:mi:ss'),duration,service_code from billing_record','/tmp','tas.csv');
1) it does not accept the to_char(start_time,'yyyymmdd hh:mi:ss') formatting in the query part. I have to select just start_time which does not give me all the information which I need.How can I fix this?
2)filename: I need the filename in the format (sysdate-2)||.csv eg 2003MAR13.csv. I have to update the table again to change the date format to what I want. This is not very effective as the filename appears as (sysdate-3) and not (sysdate-2). here is what I add:
update dump_csv_parameters
set FILENAME= (to_char((sysdate-2),'YYYYMONDD')||'.csv')
where job_id = (select max(job_id) from dump_csv_parameters);
Your assistance will be most highly appreciated as I cannot get assistance from any other sources.
Thanking you kindly.
March 17, 2003 - 9:08 am UTC
1) you do know that in order to put a quote in a character string literal -- one needs to use ''
values(l_job,'SELECT pin,to_char(start_time,''yyyymmdd hh:mi:ss''), duration, service_code from b
2) sounds like you might want a custom DUMP_CSV routine then eh?
Don't know why you cannot get help from other sources as this is somewhat "programming 101" stuff? #1 is trivial to fix. #2 -- i would suggest you modify the dump_csv routine to be:
13 l_separator varchar2(10) default '';
14 l_cnt number default 0;
l_filename varchar2(255);
15 begin
execute immediate 'select ' || p_filename || ' from dual'
INTO l_filename;
16 l_output := utl_file.fopen( p_dir, l_filename, 'w' );
that way, if you insert:
... from billing_record','/tmp','to_char(sysdate,''yyyymondd'')||''.csv''');
then as runtime, we'll dynamically execute a query that looks like:
select to_char(sysdate,'yyyymondd')||'.csv' from dual;
into your l_filename variable. If you wanted a fixed filename, you would insert:
... from billing_record','/tmp','''tas.csv''');
so the query would be:
select 'tas.csv' from dual;
instead.
17
Does this fail for long queries?
Ajums TT, March 23, 2005 - 6:24 am UTC
I have a very long query consisting of replace, nvl function calls and I am trying my best to get it parsed, but it is not working.
l_sqlquery := 'SELECT CD.COMM_DETAIL_ID , CD.QUEUE_ID , REPLACE(Tl.TRANSACTION_CODE,'','','' '') , REPLACE(Tl2.TRANSACTION_DESC,'','','' '') ,REPLACE(TS.TRANSACTION_SOURCE_DESC,'','','' '') , TO_CHAR(PPL.PAYROLL_DATE,''MM/DD/YYYY HH24:MI:SS'') , CD.NEW_COMPANY_CODE , CD.PERSON_NUMBER ' ;
l_sqlquery := l_sqlquery ||'nvl(TRIM(OEC.LAST_NAME),'' '') ||'' '' || nvl(TRIM(OEC.first_name),'' ''), REPLACE(OEC.JOB_TITLE,'','','' '')';
l_sqlquery := l_sqlquery || 'REPLACE(OEC.ORGANIZATION,'','','' '') , CD.COMM_SPLIT_PERCENT , CD.UNIT_NUMBER , CD.DEAL_BASE_YEAR , CD.BASE_COMM_AMOUNT , CD.ADDON_COMM_AMOUNT , CD.TOTAL_COMM_AMOUNT , CD.FIN_ORGUNIT_CODE ,';
l_sqlquery := l_sqlquery || 'REPLACE(CD.BRANCH_NAME,'','','' '') , CD.CUSTOMER_NUMBER , REPLACE(CD.CUSTOMER_NAME,'','', '' '') , ';
l_sqlquery := l_sqlquery || 'CD.AGREEMENT_NUMBER , TO_CHAR(CD.SALE_CONCLUSION_DATE,''MM/DD/YYYY HH24:MI:SS'') , CD.SALES_BASE_PRICE , ';
l_sqlquery := l_sqlquery || 'CD.SALES_PRICE , CD.MARGIN_PERCENT , CD.SCHEDULE_NO , CD.SCHEDULE_STATUS , ';
l_sqlquery := l_sqlquery || 'TO_CHAR(CD.SCHEDULE_COMMENCE_DT,''MM/DD/YYYY HH24:MI:SS'') , TO_CHAR(CD.SIGNED_DATE,''MM/DD/YYYY HH24:MI:SS'') , ';
l_sqlquery := l_sqlquery || 'CD.DEAL_NUMBER , CD.SCHEDULE_TERM , CD.SCHEDULE_PERIOD , CD.GUARANTEED_SCHED_TERM , CD.GUARANTEED_SCHED_PERIOD , ';
l_sqlquery := l_sqlquery || 'CD.CONTRACT_TYPE , CD.DEAL_QUANTITY , REPLACE(CD.UNIT_DESCRIPTION,'','','' '') , CD.LEASE_RATE , CD.VPP_RATE , ';
l_sqlquery := l_sqlquery || 'CD.DEAL_ROE , TO_CHAR(CD.SCHEDULE_COMMISSION_DATE,''MM/DD/YYYY HH24:MI:SS'') , CD.GUARANTEED_REVENUE , ';
l_sqlquery := l_sqlquery || 'CD.SCHEDULE_REVENUE , CD.VPP_REVENUE , CD.CON_BASE_COMM_PCT , CD.CON_MULT_COMM_PCT , ';
l_sqlquery := l_sqlquery || 'TO_CHAR(CD.PAID_FROM_DATE,''MM/DD/YYYY HH24:MI:SS'') , TO_CHAR(CD.PAID_TILL_DATE,''MM/DD/YYYY HH24:MI:SS'') , ';
l_sqlquery := l_sqlquery || 'REPLACE(CD.REMARKS,'','','' '') , CD.DELETED_FLAG , REPLACE(CD.CREATED_BY,'','','' '') ,';
l_sqlquery := l_sqlquery || 'TO_CHAR(CD.CREATION_DATE,''MM/DD/YYYY HH24:MI:SS'') , REPLACE(CD.LAST_UPDATED_BY,'','','' '') , ';
l_sqlquery := l_sqlquery || 'TO_CHAR(CD.LAST_UPDATE_DATE,''MM/DD/YYYY HH24:MI:SS'')';
l_sqlquery := l_sqlquery || 'FROM COMM_DETAIL CD, OHR_EMPLOYEE_CUR OEC, ';
l_sqlquery := l_sqlquery || 'COMM_TRANSACTION_LOOKUP Tl, COMM_TRANSACTION_LOOKUP Tl2, COMM_TRANSACTION_SOURCE TS, ';
l_sqlquery := l_sqlquery || 'COMM_PAY_PERIOD_LOOKUP PPL WHERE CD.PERSON_NUMBER = OEC.PERSON_NUMBER AND Tl.TRANSACTION_ID(+) = CD.TRANSACTION_ID ';
l_sqlquery := l_sqlquery || ' AND Tl2.TRANSACTION_ID(+) = CD.SUB_TRANSACTION_ID AND Tl2.PARENT_TRANSACTION_ID(+) = CD.TRANSACTION_ID AND ';
l_sqlquery := l_sqlquery || 'TS.TRANSACTION_SOURCE_ID(+) = CD.TRANSACTION_SOURCE_ID AND PPL.PAY_PERIOD_ID (+) = CD.PAY_PERIOD_ID ';
l_sqlquery := l_sqlquery || 'AND Tl.ACTIVE_FLAG = ''Y'' AND Tl2.ACTIVE_FLAG = ''Y'' AND TS.ACTIVE_FLAG = ''Y''';
March 23, 2005 - 8:56 am UTC
my car won't start either.
TO_CHAR(PPL.PAYROLL_DATE,''MM/DD/YYYY HH24:MI:SS'') , CD.NEW_COMPANY_CODE ,
CD.PERSON_NUMBER ' ;
l_sqlquery := l_sqlquery ||'nvl(TRIM(OEC.LAST_NAME),'' '') ||'' '' ||
seems to be missing a comma after person_number
and again:
REPLACE(OEC.JOB_TITLE,'','','' '')';
l_sqlquery := l_sqlquery || 'REPLACE(OEC.ORGANIZATION,'','','' '') ,
you gotta debug these - print them out, see what you see.