PL/SQL Export CSV: UTL_FILE Guide

1 year ago
Sophia Anderson
2 minutes

In PL/SQL, data can be exported to a CSV file using the PUT_LINE function in the UTL_FILE package. Here is an example:

  1. Firstly, create a stored procedure to export data to a CSV file.
CREATE OR REPLACE PROCEDURE export_to_csv (file_name IN VARCHAR2) IS
  file_handle UTL_FILE.FILE_TYPE;
BEGIN
  -- 打开文件
  file_handle := UTL_FILE.FOPEN('DIRECTORY_NAME', file_name, 'W');

  -- 查询数据并将每行数据写入文件
  FOR rec IN (SELECT column1, column2, column3 FROM your_table) LOOP
    UTL_FILE.PUT_LINE(file_handle, rec.column1 || ',' || rec.column2 || ',' || rec.column3);
  END LOOP;

  -- 关闭文件
  UTL_FILE.FCLOSE(file_handle);
END;
/

Explain:

  1. Replace ‘DIRECTORY_NAME’ with the specified directory object name, which points to the directory where CSV files are stored.
  2. Replace ‘your_table’ with the name of the table from which you want to export data.
  3. Replace ‘column1’, ‘column2’, and ‘column3’ with the names of the columns you want to export.
  1. Call a stored procedure and pass the name of a CSV file as a parameter.
BEGIN
  export_to_csv('output.csv');
END;
/

After running the above code, the data will be exported to the specified CSV file (output.csv). Each row of data will be separated by commas.

bannerAds