load data into text file from oracle database views.

Question

Hi, I want to load data into text file that is generated after executing "views" in Oracle?How can I achieve this in oracle using UNIX.for example- I want the same in Oracle on unix box.Please help me out as it alredy cosume lots of time. your early response is highly appreciated!!

Answer 1

As Thomas asked, we need to know what you are doing with the "flat file". For example, if you're loading it into spreadsheet or doing some other processing that expects a defined format, then you need to use SQL*Plus and spool to a file. If you're looking to save a table (data + table definition) for moving it to another Oracle database then EXP/IMP is the tool to use.

We generally describe the data retrieval process as "selecting" from a table/view, not "executing" a table/view.

Answer 2

If you have access to directories on the database server, and authority to create "Directory" objects in Oracle, then you have lots of options.

For example, you can use the UTL_FILE package (part of the PL/SQL built-ins) to read or write files at the operating system level.

Or use the "external table" functionality to define objects that look like single tables to Oracle but are actually flat files at the OS level. Well documented in the Oracle docs.

Also, for one-time tasks, most of the tools for working SQL and PL/SQL provide facilities for moving data to and from the database. In the Windows environment, Toad's good at that. So is Oracle's free SQL*Developer, which runs on many platforms. You wouldn't want to use those for a process that runs every day, but they're fine for single moves. I've generally found these easier to use than SQL*Plus spooling, but that's a primitive version of the same functionality.

As stated by others, we need to know a bit more about what you're trying to do.