Skip to Main Content
  • Questions
  • How to create csv file from oracle db

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, sanjai.

Asked: November 16, 2001 - 6:24 pm UTC

Last updated: June 07, 2005 - 8:27 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Here is what I am trying to do.

1. We have two databases here lets say d1 and d2. The structures in both the dababases are different but the data is same.
For example. In d1 we have emp and dept table while in d2 we just have one table emp_dept (having all the columns from emp, dept of d1).

2. I am asked to write PL/sql code that creates csv file one for each table from database d1 on a nightly basis (using dbms_job). The record struture of csv file should match with the table structure of d2. I need to pull the data from emp, dept table in such a way that the csv file has record that matches with emp_dept table of d2 database.

3. I also need to write a PL/SQL code that pulls the data from the csv file and insert it into d2. The PL/SQL should make sure if the record is csv file already exists in d2 then it should update or else it should insert.

4. Once the data is inserted in d2 then I need to send this csv file on a ftp server.

Can you please give me your suggestions to achieve this functionality.

Thank a ton.


and Tom said...

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:235814350980
for the program to dump the data, you'll just give it a query 

select emp.*, dept.*
from emp, dept 
where emp.deptno = dept.deptno;

(just join and pick off whatever fields you want)

See
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:464420312302
for the code to load the table on the other end.  You'll need to modify it to "merge" but basically its there.

As for #4 -- thats upto you -- thats outside of the database.  You could certainly use a java stored procedure to do it, that works.


See
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1615330671789 <code>
for how 9i will obviate the need for steps #2 and #3 with external tables and the new merge command.



Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dump_csv function referenced

Mohammed Ahmed, November 20, 2001 - 7:47 am UTC

Tom ,

The dump_csv function referenced by first link do not work.
it gives error "ORA-06571: Function DUMP_CSV does not guarantee not to update database" when I invoke it this way

select dump_csv( 'select * from all_users where rownum < 5', ',', '/tmp', 'test.dat' ) from dual;

When i invoke it this way:

exec apps.dump_csv( 'select * from all_users where rownum < 5', ',', '/tmp', 'test.dat' );

it gives me error:
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'DUMP_CSV' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


What to do?

Ahmed,

Tom Kyte
November 20, 2001 - 8:48 am UTC

it is a function -- functions in general are NOT TO be called from SQL -- that is a special feature, that if they follow some rules they CAN be called from SQL.

You know, my example shows you exactly how to call this from sqlplus:

ops$tkyte@8i> declare
  2      l_rows    number;
  3  begin
  4      l_rows := dump_csv( 'select *
  5                             from all_users
  6                            where rownum < 5',
  7                          ',',
  8                          '/tmp',
  9                          'test.dat' );
 10      dbms_output.put_line( to_char(l_rows) ||
 11                            ' rows extracted to ascii file' );
 12  end;
 13  /
4 rows extracted to ascii file

PL/SQL procedure successfully completed.



Its a function, use it as such.

If you must put it on a single line, then call:

exec dbms_output.put_line( apps.dump_csv( 'select * from all_users where rownum < 5', ',', '/tmp', 'test.dat' ) )




 

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.


Tom Kyte
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.

Tom Kyte
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

Problem resolved

Tasneem Mia, March 24, 2003 - 3:42 am UTC

Hi, Tom
Thank you so very much for responding to my email.In the interim however, I managed to resolve my problems by doing the following:
CREATE OR REPLACE PROCEDURE RUN_DUMP_CSV
( p_job_id in number )
as
l_cnt number;
begin
update dump_csv_parameters
set filename = (select to_char((sysdate-2,'YYYYMONDD') ||'.csv' from dual)
where job_id = p_job_id;
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;

This gives me the filename in the required format.I was updating the filename in the schedule procedure instead of the run_dump_csv procedure.
Thanks once again.


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''';

Tom Kyte
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.

creating a file

RAM, June 07, 2005 - 7:17 am UTC

Hi Tom,

I have a query, i have two tables lets say A1, A2, differenet for each table. my question is i want create a FILE Using oracle stored procedure, when i run procedure
the data in table automatically writes the stored procedure. please let me know the steps using oracle stored procedure's

Tom Kyte
June 07, 2005 - 8:25 am UTC

sorry -- i did not follow the logic in that question. not sure what you are asking.

SRI, June 07, 2005 - 7:20 am UTC

Hi
I have a query, i have two tables lets say A1, A2, differenet for each table. my
question is i want create a FILE Using oracle stored procedure, when i run
procedure
the data in table automatically writes the FILE please let me know the steps using oracle stored procedure's
For each row make the | separated output line

Tom Kyte
June 07, 2005 - 8:27 am UTC

if you are asking "how do I write a stored procedure to dump the contents of a table to a file" see the original answer above, it has a link to just such a procedure.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library