Generate flat files from a data warehouse

You can generate flat files, one for each table from a given schema, by executing this single Oracle procedure. A few months ago, I was faced with a challenge of loading data from an Oracle database to a Sybase database. I needed to generate flat files (data file to load) for each table from various schema ids with tab delimiter. When I wrote my sqlplus script with a simple query with tab delimiter, I could not avoid the delimiter at the end of the last column. The table had lots of LONG, LOB, & BLOB data types, and so it became extremely complicated to design a query. Also, I had to wait for the query to complete before running the next one since I needed to input a different table name. Since it was a data warehouse database, the tables were huge and had up to several GB of data each.

I wrote the following procedure and kicked it off sending the schema name and the directory name (under Unix) to keep these files and went home. When I came back in the morning, the procedure created all the flat files that I needed with their respective table name extention. All I did was to copy it to a CD and sent it to the client site. Here is the procedure, which has been tested on Oracle v9.0:

- Description : This procedure takes in the schema name and directory path
--               name, grabs all the tables owned by that schema and generates
--               a tab delimited flat files with tablename.dat.
-- Author      : Rama Balaji
-- Date Written: May 31st 2002
-- Features used
-- dbms_sql for dynamically generate the query
-- utl_file for writing to the operating system and for generating
-- flat files
-- Instructions to run the procedure
-- 1. This procedure runs under system account.
-- 2. Make sure to add the init parameter utl_file_dir=pathname and
--    bounce the database to take effect
-- 3. Make sure you grant explicit select privileges on the
--    dba_tables since procedure expects explicit privilege not
--    granted thru roles.
-- 4. Make sure the directory you speicified has all the read,write
--    privileges.
-- 5. run the procedure as execute generate_files(schema_name,path_name)
-- 6. Make sure you have enough space under the path specified
-- 7. This procedure assumes the above all done. It does not track 
--    all the exceptions. Please tweak it as you desired. It can
--    also be used using database links connecting to other databases
--    with the minor code change.
-- PROCEDURE BEGINS
create or replace procedure generate_files(p_owner in varchar2,p_dir in varchar2 ) 
as 
    l_theCursor integer;
    l_columnValue varchar2(4000);
    l_status integer;
    l_colCnt number default 0;
    l_descTbl dbms_sql.desc_tab;
    l_output utl_file.file_type;
    l_line long;
    i integer;
    p_tname varchar2(30);
    Cursor tname_cur(temp_owner varchar2) is 
        select table_name from dba_tables where owner = upper(temp_owner) 
begin
/* If you need to debug add server output */
    dbms_output.enable(1000000);
    
/* Opens the cursor  */
    open tname_cur(p_owner);
    loop
     /* fetching the table name and storing into p_tname */
       fetch tname_cur into p_tname;
       exit when tname_cur%notfound;

/* opens the dynamic sql cursor */
     l_theCursor := dbms_sql.open_cursor;
     begin
/* parse the statement */
       dbms_sql.parse(l_theCursor,
                'select * from '||p_owner||'.'||p_tname,
                 dbms_sql.native);
/* describe the columns to discover the no of outputs */
       dbms_sql.describe_columns(l_theCursor,l_colCnt,l_descTbl);
       for i in 1..l_colCnt
       loop
/* Define the columns. Bind every single column to a varchar2(4000)
   We don't care if we are fetching a number or a date or whatever.
   Everything can be a string */

       dbms_sql.define_column(l_theCursor,
                              i,
                              l_columnValue,
                              4000);
       end loop;
/* Execute the query */
       l_status  :=  dbms_sql.execute(l_theCursor);
/* Open the file to write output to and the write the delimited data
   to it */      
      l_output := utl_file.fopen(p_dir,lower(p_tname) || '.dat', 'w',32760);

       while (dbms_sql.fetch_rows(c => l_theCursor) > 0 )
       loop
          l_line :=null;
          for i in 1..l_colCnt 
          loop
            dbms_sql.column_value(l_theCursor,
                                  i,
                                  l_columnValue);
/* The following code is used to avoide tab delimiter at the end of
     last column */
            if (i <> l_colCnt)
            then 
              l_line := l_line || l_columnValue || chr(9);
            else
              l_line := l_line || l_columnValue;
            end if;
          end loop;
          utl_file.put_line(l_output, l_line);
        end loop;
     utl_file.fclose(l_output);
       dbms_sql.close_cursor( c => l_theCursor);
   exception
/* Capture the errors */
      when others then
         dbms_output.put_line(sqlerrm);
         dbms_sql.close_cursor(c => l_theCursor);
      end;
   end loop;
   close tname_cur;
  end;
/

Reader feedback:

Richard T. writes: This procedure is invalid and failed re-validation with compiler error:
create or replace procedure generate_files(p_owner in varchar2, p_dir in varchar2)
* ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 9 ORA-04098: trigger 'SYS.TRG_SECURITY_REFRESH'

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.