Export table to csv file by using procedure (csv name with timestamp)

Question

I want to export one table to each csv file and name the csv file with timestamp. For example, if I have a table t1, after export, a csv file name t1.YYYYMMDDHHMISS.csv is generated. Here is my code:

create or replace procedure 
T_to_CSV(Tname varchar2,Directory varchar2)

BEGIN

set colsep ,     -- separate columns with a comma
set pagesize 0   -- No header rows
set trimspool on -- remove trailing blanks
set headsep off  -- this may or may not be useful...depends on your 

spool timestamp.csv --don't know how to write in directory

select *
  from Tname

end

Here are my questions:

(1) I don't know how to output the csv file into the requried directory, how to fix the spool code please?

Should I use spool D:\path\filename.csv please?

(2) In the spool line, how to change the name of the csv file using the timestamp now() please?

Spool is for sqlplus and can only write to a file on your computer or a mapped drive. Directories are for writing files to the server the database is on or to a UNC path. Spool will not work inside a procedure but you can use DBMS_OUTPUT which the spool command will pick up the output of. — kevinskio, Apr 01 '15 at 19:55
Maybe this will help: [How to create a oracle sql scrpt spool file](http://stackoverflow.com/questions/8590826/how-to-create-a-oracle-sql-scrpt-spool-file) — Ponder Stibbons, Apr 01 '15 at 20:24
Thanks ponder for this, it is very helpful. Do you know how run the script in link please? — user4441082, Apr 01 '15 at 20:45
No, sorry. But question looks very similar to yours - (directories, timestamps). — Ponder Stibbons, Apr 01 '15 at 20:57

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

6

There are a few steps:

create a directory using `CREATE DIRECTORY my_dir as 'C:\dir';
make sure that Oracle has read,write on the folder on the computer (best accomplished by creating it in the Oracle install folder)
grant the user executing the procedure GRANT read,write on DIRECTORY my_dir to the_user;
download and compile the handy procedure here

I have used this and it works really nicely.

Usage

data_dump ( 'Select emp_name from emp',
             CURRENT_TIMESTAMP||'filename.csv',
             my_dir);

(vastly simplified sample!)

After creating the directory verify your work by running this:

Select * from ALL_DIRECTORIES;
you should see your directory
logon to the machine where the database is located and verify the folder path exists and the oracle user has permissions on it. Networked drives are only possible if the user running the oracle service has permissions on that folder

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 01 '15 at 19:46

kevinskio

4,431
1
22
36

WOW, thanks kevin for the link. The sql code is much too complicated. Maybe I just need a simple version to achieve my simple goal. Can I fix my code a bit to get that please? – user4441082 Apr 01 '15 at 19:51
Hi, kevin. I have tried your method. What to input in my_dir please? I use D: or D:/, but return error. Thanks. – user4441082 Apr 01 '15 at 20:05
You need to use the name of the directory you created in the first step of my answer – kevinskio Apr 01 '15 at 20:07
Thanks for your patience. There are two issues (1) v_fh UTL_FILE.FILE_TYPE; doesn't pass compilation. (2) create directory should be in another command line please? – user4441082 Apr 01 '15 at 20:10
1) you need to create a directory first, you can do this from sqlplus. the user must have CREATE DIRECTORY privilege 2) please provide the exact oracle error – kevinskio Apr 01 '15 at 20:15
Hello, (1) is solved. (2) when i run CREATE DIRECTORY my_dir as 'D:\dir', I don't see a new folder dir show up. But oracle said directory created. Why is that please? – user4441082 Apr 01 '15 at 20:32
Thanks for answer. But how do we use it for Linux? Is there any different settings for linux directories? – postgresnewbie Nov 25 '21 at 13:24
1

@postgresnewbie no difference for file paths in Linux, permissions must be granted to the oracle process so putting the destination folder in the install folder works or you can put it elsewhere and mirror the permissions the oracle process has on it's own folders – kevinskio Nov 25 '21 at 18:32
Thanks a lot! I manage to send it. But the problem is some characters are corrupted because of different characters. My db uses we8iso8859p9 characterset but it seems script exports in different one. How can i change character set in the script? Or is there anything else to do? – postgresnewbie Nov 26 '21 at 07:49
1

@postgresnewbie character conversions are tricky, Please ask this as a new question with more details – kevinskio Nov 26 '21 at 12:30
Will surely do that. And the last question, what if we want the column names at the beginning of csv? Thanks in advance... – postgresnewbie Nov 26 '21 at 13:34
@postgresnewbie you need to select the column names from the USER_TAB_COLUMNS first then concatenate with your data select – kevinskio Nov 26 '21 at 13:55

score 0 · Answer 2 · answered Jun 21 '18 at 06:37

Thanks kevin for sharing the procedure and it was very useful for me. I have customized the code:

To Add the column names in the output csv file which was not working earlier.
When i was passing the delimiter as parameter then it was adding the comma in the starting of every row(,1,2,3) which has been corrected.

I am also sharing the customized code which might help others. Customized code can be downloaded here.

Customized Code to add column names

 FOR i IN t_describe.FIRST .. t_describe.LAST LOOP    
               IF i <> t_describe.LAST THEN          put('UTL_FILE.PUT(v_fh,'''||t_describe(i).col_name||'''||'''||v_delimiter||''');');
       ELSE    
          put('            UTL_FILE.PUT(v_fh,'''||t_describe(i).col_name||''');');    
       END IF;    
       END LOOP;    
       put('            UTL_FILE.NEW_LINE(v_fh);');

Customized Code for delimiter

IF i <> t_describe.LAST THEN
put(' UTL_FILE.PUT(v_fh,"'||t_describe(i).col_name||'"(i) ||'''||v_delimiter||''');');
ELSE
put(' UTL_FILE.PUT(v_fh,"'||t_describe(i).col_name||'"(i));');
END IF;

And the correct way to call the procedure is to bind the variable with values

data_dump(query_in => 'Select 1 from dual',file_in => 'file.csv',directory_in => 'MY_DIR', delimiter_in => '|' );

Thanks

Naveen

You could also use `csv.write_file()` from [www.williamrobertson.net/documents/refcursor-to-csv.shtml](http://www.williamrobertson.net/documents/refcursor-to-csv.shtml) for a more flexible solution. — William Robertson, Jun 21 '18 at 18:21

score 0 · Answer 3 · 2018-10-29T09:39:58.810

Save and copy file in a directory {file need not be executable}
Export ORACLE_HOME, PATH and SID of database instance
Navigate to that directory and execute sqlplus

Spool file will be created in the same directory as the .sql file

SET COLSEP ,
SET HEADSEP OFF       -- character used when defining a two-line column heading
SET TRIMSPOOL ON      -- trim trailing spaces from each line
SET LINESIZE 32000    -- number of characters to be printed on one line
SET WRAP OFF          -- truncates lines longer than LINESIZE
SET NUMWIDTH 5        -- default width while displaying numbers

COLUMN tm new_value iso8601 noprint
SELECT to_char(sysdate, 'YYYY-MM-DD') tm FROM dual;

spool output_file_&iso8601..csv
-- Append new data to spool file: "spool output_file_&iso8601..csv append"

SELECT
  * 
FROM
  table_name

spool OFF

Export table to csv file by using procedure (csv name with timestamp)

3 Answers3