1

I'm using Oracle 11g

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? I'm not seeing a UTL_FILE function to write the records to the file; do I have to iterate through the entire result set and use PUT or can I somehow just push the entire result to a file?

Community
  • 1
  • 1
simplycoding
  • 2,770
  • 9
  • 46
  • 91
  • Maybe read the documentation? https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm You have to iterate through the result set. UTL_FILE knows nothing about your records. It is a low-level package. – OldProgrammer Nov 15 '16 at 18:12
  • I did, but maybe I should be a bit more clear: is that `oracle\admin` directory on the server? How do I create a directory and file on the client machine? If that `oracle\admin` directory actually is on the client machine, where is it? I don't see any folder with that name anywhere – simplycoding Nov 15 '16 at 18:19
  • You have to create the physical directory from the OS (ie, md or mkdir ) if it does not exist on the server. You did read the documenation, where is says, "A directory object specifies an alias for a directory **on the server file system** " You cannot create files on the client machine. Oracle knows nothing about your client machine. – OldProgrammer Nov 15 '16 at 18:23
  • Ah, I thought "on the server file system" meant on the server where Oracle lived. So it looks like even to create files or run a command like `CREATE OR REPLACE DIRECTORY test as 'c:\users\me\testing' that I need privileges from Oracle to even run that – simplycoding Nov 15 '16 at 18:35
  • Yes, it DOES mean on the server where Oracle lives. – OldProgrammer Nov 15 '16 at 20:30

2 Answers2

2

I think using "spool" can do the trick.

Check this out https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9518534700346581975

And more information is here http://www.dba-oracle.com/t_sqlplus_spool.htm

The file will get created in the directory where you launch sqlplus from.

tdc
  • 74
  • 4
  • Yeah, I came across spool too. Are there other alternatives without `spool`? Not sure if sqlplus will be installed on the client machine to allow me to use `spool` – simplycoding Nov 15 '16 at 18:36
0

If you're using SQL Developer you can create a view for your query. Right click view in schema browser and choose export and export as csv.

But personally I would go for spool as previous answer said. SQL Plus is most basic client so I don't believe you won't have it.

Kacper
  • 4,798
  • 2
  • 19
  • 34
  • I need to automate this process eventually. Is there a way to do what you mentioned programatically? Also, I don't think we'll have SQL Plus. Most likely going to be SQL Developer on the machine – simplycoding Nov 15 '16 at 19:38
  • @simplycoding I'd go for SQL*Plus and spool if you need it automated. That's simplest and easiest to maintain. Like here: http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus – Kacper Nov 15 '16 at 20:04
  • Does SQL*Plus come with every Oracle client installation? – simplycoding Nov 15 '16 at 20:11
  • Seems like it does... Just ran the command on my Windows machine's cmd prompt, and it opened – simplycoding Nov 15 '16 at 20:13
  • I think every client contain SQL*Plus. You can also always use basic instantclient. @simplycoding – Kacper Nov 15 '16 at 20:13
  • It is installed by default, but depending on your enterprise security protocols, may be removed, as it is a gateway to the database. – OldProgrammer Nov 15 '16 at 20:30