Need ideas on outputting table data to a CSV using PL/SQL in a dynamic fashion

Question

The task is to take a list of tables which is changeable.

Write a piece of PL/SQL that when executed outputs every tables rows into individual csv files.

So if 5 tables. You will get 5 CSV files with the relevant table data in it.

The CSV should be | delimited and have " around each value (for easy import to excel)

All I know is the list of tables.

So load the list into an array at the top of the procedure, loop through this list and use UTL_FILE to output each row on a line by line basis.

I'm stuffed wondering if I need a cursor per table or if the cursor can be used dynamically to store the results from each table.

p.s. each file must also contain the column headings as the first row.

Is it even possible ? There is a list of over 30 tables, some of the tables have over 200 columns.

So ideas please :).

I'm slowly thinking this isn't possible. as i need some dynamic SQL that can gather all the column names etc. I'm getting bogged down!

It can't be a SQL script and simply spooling the output. All we ever want to do is add or remove tables from the array declaration.

Answer 1

First Oracle stores all of that data in views that you have access to.

SELECT * FROM ALL_TAB_COLUMNS

will get you a list of the columns for a table. That will make creating the column headers for the file simple.

The rest is just unloading data into a flat file. You can find recipes for that here.

Here's a link directly to the code.

Answer 2

Yes, it is possible. Look at this Ask Tom question that shows how to do it for 1 table at a time. All you need to do is loop through your array of table names and call Tom's procedure for each.

Answer 3

Ther are several options.

1. You can use UTL_FILE do dump preformatted text strings into files. Just make a cursor that outputs all data columns concatenated with delimiters.

Something like this:

DECLARE
  TYPE IDCurTyp IS REF CURSOR;
  fo            UTL_FILE.FILE_TYPE;
  varRow        VARCHAR2(4000);
  cur_output    IDCurTyp;
BEGIN
  fo := UTL_FILE.FOPEN('BILLING_DIR','BillingFile1.csv', 'W', 2000)
  OPEN cur_output FOR
    'SELECT ''"'' || t1.col1 || ''",'' || t1.col2 || ''","'' || t1.col2 || ''"'' FROM t1'
  LOOP
    FETCH cur_output INTO varRow;
    EXIT WHEN cur_output%NOTFOUND;
    UTL_FILE.putf( fo, '%s\n', varRow );
  END LOOP;

  CLOSE cur_output;

  UTL_FILE.FCLOSE( fo );
END:

1. Instead of preformatted text create a package that accepts SQL query and then uses DBMS_SQL package to parse it and extract column names, etc and create text strings to dump using UTL_FILE once again.