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

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.

Answers


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.


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.


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.

Need Your Help

Multiple requests to the google translator API make website TOO slow

php google-api google-translator-toolkit

I am using Google translator API to translate my website in some of the websites:

Is the Stack Overflow source code available?

discussion stack-overflow source-code open-source

Do the SO admins have any plans to release the underlying code for SO at all? I appreciate that the code may be part of their competitive advantage, but it strikes me that the underlying system would