I'm using Oracle 11g (I think)

I'm trying to create a flat file (CSV or TXT) from a result set but am struggling on where to even start. It seems like I have to create a stored proc and use UTL_FILE. After doing some research, I have two questions:

  1. Where does the file get created? According to this question I need to get access to the Oracle user directory, but where is that on a Windows and Linux environment? I have to test on Windows , and the script will eventually be on a Linux environment.

  2. What would be the basic format of a SQL script to create the aforementioned file, and load data into it from a fairly basic SELECT query?


There's two routes to go through.

Firstly, Server-side. This is where a process on the machine hosting the DB will write the data to its file system using UTL_FILE. A DIRECTORY entry needs to be created in the database by a DBA pointing to a directory on the file system which can be written to by the OS process running the database (generally 'oracle')

Secondly, client-side. This is where a client program (eg SQL*Plus, Java, Python, Excel...) connects to the database, pulls the required data across to the client and writes it to a file on the client's file system.

You can have the client and server on the same machine, or a shared directory/file system that is accessible to both a client and server (one side needs to be able to read/write, the other can just be read).

You need to decide which best fits your situation, including

  • where you want the file to end up
  • whether the task will be invoked manually or through a scheduler/automation
  • how to manage and deploy changes to the script/code that generates the file
| improve this answer | |
  • 1
    If you go for the client side, the new JAVA based SQLcl might be an option. It can generate CSV, JSON and other output directly. – jmk Nov 16 '16 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.