I would like a general-purpose PL/SQL proc for outputting the contents of a
table or view to a text file, with the field values separated by a
delimiter.

Currently I have a proc for each table that I export, in which I use a
cursor to select the records, construct each line by concatenating the
fields with the delimiter in between, and output to the file with
UTL_FILE.PUT_LINE. Is there a way to generalize this to work for any table?
The thought would be to pass in the table name as a parameter, along with
the file path and file name of the destination file.

Any suggestions would be greatly appreciated!

Query the dictionary to get the names and datatypes of the columns of the
target table. You probably only want to PUT_LINE on simple datatypes and
not LOBS, so you might want to skip over datatypes that you don't want to
deal with. Using this information, build your SELECT statement and then
execute it with NDS (Native Dynamic SQL).

Mike


"Sean" <sgeraghty (AT) yahoo (DOT) com> wrote