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; /
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.