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?

Answer 1

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