exporting data 1

[knitwit]

I'm trying to export all of the tables from an 8 (8.1) oracle database and their data. I have been using sqlplus 3.3 and spooling the data to txt files.

Anyway,
Some tables have buffer overrun or inconsistent data types error messages when I "select * from table". Also, one of the tables has 3 millions rows and 100 columns. I get the buffer overrun message, so I have been trying to get the data in pieces but it will take 14 hours to get it all. I run into a limit of 8 lines and only so many characters per line - so I can't list all of the columns at once.

Is there a way to fix the overrun? This database is a client's so I can't do anything to the data such as dropping indexes. I read elsewhere about "unrecoverable" to turn off the rollback function, but I don't know the syntax. Would this help me?

Windows OS

 

[SantaMufasa]

KnitWit,

When you say you are "exporting data", what is your objective of the export? Is it to make your table data accessible to some non-Oracle repository, or is your purpose to use the "exported" data for an "Oracle" purpose?

If your purpose is non-Oracle, there is a much better method than spooling output via SQL*Plus. You can write Comma-Separated Values (.csv) flat files from your Oracle database. (We can explain how if .csv is your objective.)

If your "export" purpose is for use in an Oracle database, then, by far, a better solution is using Oracle export ("exp" utility from your o/s command prompt). We can explain how to use "exp" of that is your objective.

Please advise so we can offer you more assistance.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:17 (26Feb04) UTC (aka "GMT" and "Zulu&quot, 18:17 (25Feb04) Mountain Time)

 

[knitwit]

I am grabbing the data for conversion to another database. The Oracle server doesn't have office or anything else on it.

Csv is my objective. I was willing to use fixed width and then convert it in Excel on another machine to csv....but directly to csv will be great

 

[sem]

Buffer overflow is normally raised by dbms_output, do you use it? If so, forget about it, as it's right only for simple debugging. Can you provide exact error message and code?

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

KnitWit,

I am posting, below, routines that should create Comma-Separated Value (.csv) files from any tables you wish. I have not stress tested the routines to find the upper limits on total number of columns or total combined column widths. Perhaps you can post your findings for your widest table(s).

The first code set is a generic package that facilitates use of Oracle's "utl_file" flat-file-manipulation package. My actual CSV-generating script relies upon the "dh_file" package, so make sure you save the "dh_file" package to a script and run it from the schema from which you wish to "CSV" tables. Also, if you have not done so already, ensure that your instance's parameter, "utl_file_dir" equals either "*" (able to write to any path on the server's file system) or "<some selected path(s)>" to which you will write output from the second code set.

Code Set 1: DH_FILE "utl_file" package handler.

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any 
REM specific industrial application nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (see address below) when 
REM you have comments, suggestions, and/or difficulties with this
REM packages functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: DH_FILE.SQL - PL/SQL code to create a package (DH_FILE)
REM       providing the following 5 procedures and 1 function:
REM		1) DH_FILE.FILE_OPEN	(<file#>,<path>,<file>,
REM					 <'W','R','A'>)
REM			[Write, Read, Append] respectively.
REM		2) DH_FILE.FILE_CLOSE	(<file#>)
REM			...uses handle from FILE array.
REM		3) DH_FILE.FILE_PRT	(<file#>,<output string>)
REM			...uses handle from FILE array.
REM		4) DH_FILE.FILE_STAGE	(<file#>,<output string>)
REM			...uses handle from FILE array.
REM		5) DH_FILE.FILE_FLUSH	(<file#>)
REM			...uses handle from FILE array.
REM		6) DH_FILE.READ_NEXT	(<file#>)
REM			...RETURNs next line from file_handle
REM			...uses handle from FILE array.
REM
REM	This package facilitates usage of the Oracle UTL_FILE
REM	package.  The UTL_FILE file operations include: 
REM	FOPEN, IS_OPEN, FCLOSE, FCLOSE_ALL, GET_LINE, PUT,
REM	NEW_LINE, PUT_LINE, PUTF, and FFLUSH 
REM
REM	VERY IMPORTANT NOTE!!!: For any write use of UTL_FILE,
REM	the instance must be prepared to write to O/S directories
REM	via the init.ora parameter,
REM	"UTL_FILE_DIR = <path><or "*", meaning "all directories">.
REM
REM AUTHOR: Dave Hunt, Co-Principal, DaSages
REM	    dave@Dasages.com
REM         1-801-TRY-DAVE (1-801-879-3283).
REM
REM **************************************************************
REM Maintenance History:
REM
REM 19-MAR-97: Original Code
REM 22-DEC-98: Enhanced to allow for multiple file handling by number
REM **************************************************************
REM **************************************************************
REM DH_FILE Package Specification
REM **************************************************************
create or replace package dh_file is
   procedure FILE_OPEN 	(which_file in binary_integer,
			path in varchar2,
			filename in varchar2,
			read_write_append in varchar2);
   procedure FILE_CLOSE (which_file in binary_integer);
   procedure FILE_PRT	(which_file in binary_integer,
			str_to_prt in varchar2);
   procedure FILE_STAGE (which_file in binary_integer,
			str_to_prt in varchar2);
   procedure FILE_FLUSH (which_file in binary_integer);
   function  READ_NEXT 	(which_file in binary_integer) return varchar2;
   -- pragma restrict_references(read_next,WNDS);
end;
/
REM **************************************************************
REM DH_FILE Package Body
REM **************************************************************
create or replace package body dh_file is
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_OPEN 
--  **************************************************************
	type file_handle_stencil is table of utl_file.file_type
		index by binary_integer;
	file_handles file_handle_stencil;
	file_handle	utl_file.file_type;
	procedure FILE_OPEN 	(which_file in binary_integer,
				path in varchar2,
				filename in varchar2,
				read_write_append in varchar2) is
	begin
		file_handles(which_file)
				:=	utl_file.fopen(
					location => path,
					filename => filename,
					open_mode => read_write_append);
	end;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_CLOSE 
--  **************************************************************
	procedure FILE_CLOSE (which_file in binary_integer) is
	begin
		utl_file.fclose(file_handles(which_file));
	end FILE_CLOSE;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_PRT 
--  **************************************************************
	procedure FILE_PRT 	(which_file in binary_integer,
				str_to_prt in varchar2) is 
	begin
		utl_file.put_line(file_handles(which_file), str_to_prt);
	end FILE_PRT;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_STAGE
--  **************************************************************
	procedure FILE_STAGE 	(which_file in binary_integer,
				str_to_prt in varchar2) is
	begin
		utl_file.put (file_handles(which_file), str_to_prt);
	end FILE_STAGE;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_FLUSH
--  **************************************************************
	procedure FILE_FLUSH 	(which_file in binary_integer) is
	begin
		utl_file.fflush (file_handles(which_file));
	end FILE_FLUSH;
--  **************************************************************
--  Packaged Global Function Definition: DH_FILE.READ_NEXT 
--  **************************************************************
	function  READ_NEXT 	(which_file in binary_integer)
				return varchar2 is
		hold_text	varchar(2000);
	begin
		utl_file.get_line (file_handles(which_file), hold_text);
		return hold_text;
	exception
		when no_data_found then
			return '<EOF>';
	end READ_NEXT;
end;
/
REM **************************************************************
REM End of Package: DH_FILE
REM **************************************************************

Code Set 2: Script to generate Comma Separated Values from a prompted table:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any 
REM specific industrial application nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (see address below) when 
REM you have comments, suggestions, and/or difficulties with this
REM packages functions.
REM
REM AUTHOR: Dave Hunt, Co-Principal, DaSages
REM	    dave@Dasages.com
REM         1-801-TRY-DAVE (1-801-879-3283)
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM Maintenance History:
REM
REM 26-FEB-2004: Original Code
REM **************************************************************
REM About this script/file (CSVMaker.sql):
REM
REM	This script prompts for a table name in the current schema,
REM	then generates a "temp.sql" PL/SQL script tailored to output
REM	Comma-Separated Values for the prompted table. The script
REM	ensures field-level character-string integrity by enclosing
REM	all data values in double quotes.
REM
REM	This script refers to (and thus depends upon) a "utl_file"
REM	facilitating package named "DH_FILE". That package must
REM	run successfully and reside in the schema that uses this
REM	script.
REM	
REM	You can simulate batch processing by copying and pasting
REM	at the "SQL>" prompt, invocations of the "CSVMaker" script
REM	followed by the table name you wish to CSV, for example:
REM	@CSVMaker
REM	s_region
REM	@CSVMaker
REM	s_dept
REM	et cetera
REM
REM **************************************************************
accept tabname prompt "Provide the tablename for which you want a .csv file: "
declare
	path		varchar2(2000)	:= 'd:\dhunt\sqldba\';
	/* You must change "path", above, to a valid output path for your system */
	filename	varchar2(100)	:= 'temp.sql';
	t		char		:= chr(9);	-- tab character
	q		char(3)		:= '''"''';	-- quoted double quotes for csv
	cq		char(4)		:= ''',"''';	-- comma and double quotes
	con1		char(8)		:= '||'||cq||'||'; -- concat chrs w/comma+quote
	con2		char(5)		:= '||'||q;	-- concat chrs w/quotes
	first_time	boolean		:= true;	-- "first-time" flag
	procedure wrt (x in varchar2) is		-- Output procedure
		begin
			dh_file.file_prt(1,x);
		end;
begin
	dh_file.file_open(1,path,filename,'W');	-- Opens file for tailored code
-- *********************************************************************************
-- Assemble script to output table contents to .csv file
-- 	(The following PL/SQL code writes a PL/SQL block to "temp.sql", which will
--	 contain tailored commands to produce Comma-Separated Values to a flat file
--	 named after the table: "<Tablename>.csv". The last command of this script
--	 executes "temp.sql", thus producing "<Tablename>.csv". Once this scripts
--	 executes, you can review "temp.sql" to understand the CSV generation better.
-- *********************************************************************************
	wrt('declare');
	wrt(t||'procedure wrt (x in varchar2) is');
	wrt(t||t||'begin');
	wrt(t||t||t||'dh_file.file_prt(1,x);');
	wrt(t||t||'end;');
	wrt('begin');
	wrt(t||'dh_file.file_open(1,'''||path||''','''||upper('&tabname')||'.csv'',''W'');');
	wrt(t||'for r in'||t||'(select '||q);
	for r in       (select column_name		-- this loop concatenates names of
			from user_tab_columns		-- table columns, separated by
			where table_name = upper('&tabname') -- double quotes and commas
			order by column_id) loop
		if first_time then
			first_time := false;
			wrt('||'||r.column_name||con2);
		else
			wrt(con1||r.column_name||con2);
		end if;
	end loop;
	wrt(t||t||'x from &tabname) loop');
	wrt(t||t||'wrt(r.x);');
	wrt(t||'end loop;');
	wrt(t||'dh_file.file_close(1);');
	wrt('end;');
	wrt('/');
	dh_file.file_close(1);				-- closes "temp.sql"
end;
/
@temp
REM *********************************************************************************
REM End of "CSVMaker.sql" Script
REM *********************************************************************************

Here are some sample invocations and brief results illustrating proof of concept for "CSVMaker.sql":

SQL> @CSVMaker
Provide the tablename for which you want a .csv file: s_region
SQL> get s_region.csv
  1  "1","North America"
  2  "2","South America"
  3  "3","Africa / Middle East"
  4  "4","Asia"
  5* "5","Europe"
SQL> @csvmaker
Provide the tablename for which you want a .csv file: s_dept
SQL> get s_dept.csv
  1  "10","Finance","1"
  2  "31","Sales","1"
  3  "32","Sales","2"
  4  "33","Sales","3"
  5  "34","Sales","4"
  6  "35","Sales","5"
  7  "41","Operations","1"
  8  "42","Operations","2"
  9  "43","Operations","3"
 10  "44","Operations","4"
 11  "45","Operations","5"
 12  "50","Administration","1"
 13* "51","Information Tech","1"
SQL>

Let us know if this is what you needed.

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:47 (26Feb04) UTC (aka "GMT" and "Zulu&quot, 01:47 (26Feb04) Mountain Time)