5

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?

Improve this question
4
  • 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. Commented Apr 1, 2015 at 19:55
  • 1
    Maybe this will help: How to create a oracle sql scrpt spool file Commented Apr 1, 2015 at 20:24
  • Thanks ponder for this, it is very helpful. Do you know how run the script in link please? Commented Apr 1, 2015 at 20:45
  • No, sorry. But question looks very similar to yours - (directories, timestamps). Commented Apr 1, 2015 at 20:57

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

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?
Hi, kevin. I have tried your method. What to input in my_dir please? I use D: or D:/, but return error. Thanks.
You need to use the name of the directory you created in the first step of my answer
@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
@postgresnewbie character conversions are tricky, Please ask this as a new question with more details
|
0

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

  1. To Add the column names in the output csv file which was not working earlier.
  2. 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.

  1. 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);');

  1. 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

Improve this answer

1 Comment

You could also use csv.write_file() from www.williamrobertson.net/documents/refcursor-to-csv.shtml for a more flexible solution.
0
  • 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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.