Hi experts,

are there tools to make a fast dump of complete databases to CSV?
Without needing any additional licenses from Oracle...?

TIA Tellie

Tellie wrote:


Dump complete database to CSV??? I am not sure about that, but I have a
script that dumps a table or a result of a query to a CSV file. It is
written in Perl and requires the corresponding Perl modules.
--
http://mgogala.freehostia.com

Tellie wrote:


Dump complete database to CSV??? I am not sure about that, but I have a
script that dumps a table or a result of a query to a CSV file. It is
written in Perl and requires the corresponding Perl modules.
--
http://mgogala.freehostia.com

Tellie wrote:


Dump complete database to CSV??? I am not sure about that, but I have a
script that dumps a table or a result of a query to a CSV file. It is
written in Perl and requires the corresponding Perl modules.
--
http://mgogala.freehostia.com

Tellie wrote:


Dump complete database to CSV??? I am not sure about that, but I have a
script that dumps a table or a result of a query to a CSV file. It is
written in Perl and requires the corresponding Perl modules.
--
http://mgogala.freehostia.com

In article <ge70m2$3jc$1 (AT) registered (DOT) motzarella.org>,
gogala.mladen (AT) gmail (DOT) com says...>
Here is a function for any query. It is based on a Tom Kyte example:

-----------------------------------------------------------------------
-- DUMP_CSV
-----------------------------------------------------------------------
function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_filename in varchar2 )
return number
--
-- this code is courtesy of Tom Kyte of Oracle, modified by jeremy0505
--
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;

l_descTbl dbms_sql.desc_tab;
begin

begin
l_output := utl_file.fopen( 'UTFD_EXTRACT',
p_filename, 'w',32000 );
exception
when others then
dbms_output.put_line('Error opening file with utl_file');
raise;
end;


--
-- column headings first
--
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor,
l_colCnt, l_descTbl );
for i in 1..l_colCnt
loop
if i = 1 then
utl_file.put( l_output, l_descTbl(i).col_name);
else
utl_file.put( l_output, p_separator ||
l_descTbl(i).col_name );
end if;
end loop;
utl_file.new_line( l_output );


--
-- now to the meat
--
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;

exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,
4000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
l_columnValue := replace(l_columnValue,'&apos;','''');
l_columnValue := replace(l_columnValue,'&amp;','&');
l_columnValue := replace(l_columnValue,'"','''');
l_columnValue := replace(l_columnValue,chr(13),null);
l_columnValue := replace(l_columnValue,chr(10),' ');
l_columnValue := '"'||l_columnValue||'"';

utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;

end dump_csv;





--
jeremy

In article <ge70m2$3jc$1 (AT) registered (DOT) motzarella.org>,
gogala.mladen (AT) gmail (DOT) com says...>
Here is a function for any query. It is based on a Tom Kyte example:

-----------------------------------------------------------------------
-- DUMP_CSV
-----------------------------------------------------------------------
function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_filename in varchar2 )
return number
--
-- this code is courtesy of Tom Kyte of Oracle, modified by jeremy0505
--
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;

l_descTbl dbms_sql.desc_tab;
begin

begin
l_output := utl_file.fopen( 'UTFD_EXTRACT',
p_filename, 'w',32000 );
exception
when others then
dbms_output.put_line('Error opening file with utl_file');
raise;
end;


--
-- column headings first
--
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor,
l_colCnt, l_descTbl );
for i in 1..l_colCnt
loop
if i = 1 then
utl_file.put( l_output, l_descTbl(i).col_name);
else
utl_file.put( l_output, p_separator ||
l_descTbl(i).col_name );
end if;
end loop;
utl_file.new_line( l_output );


--
-- now to the meat
--
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;

exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,
4000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
l_columnValue := replace(l_columnValue,'&apos;','''');
l_columnValue := replace(l_columnValue,'&amp;','&');
l_columnValue := replace(l_columnValue,'"','''');
l_columnValue := replace(l_columnValue,chr(13),null);
l_columnValue := replace(l_columnValue,chr(10),' ');
l_columnValue := '"'||l_columnValue||'"';

utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;

end dump_csv;





--
jeremy

In article <ge70m2$3jc$1 (AT) registered (DOT) motzarella.org>,
gogala.mladen (AT) gmail (DOT) com says...>
Here is a function for any query. It is based on a Tom Kyte example:

-----------------------------------------------------------------------
-- DUMP_CSV
-----------------------------------------------------------------------
function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_filename in varchar2 )
return number
--
-- this code is courtesy of Tom Kyte of Oracle, modified by jeremy0505
--
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;

l_descTbl dbms_sql.desc_tab;
begin

begin
l_output := utl_file.fopen( 'UTFD_EXTRACT',
p_filename, 'w',32000 );
exception
when others then
dbms_output.put_line('Error opening file with utl_file');
raise;
end;


--
-- column headings first
--
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor,
l_colCnt, l_descTbl );
for i in 1..l_colCnt
loop
if i = 1 then
utl_file.put( l_output, l_descTbl(i).col_name);
else
utl_file.put( l_output, p_separator ||
l_descTbl(i).col_name );
end if;
end loop;
utl_file.new_line( l_output );


--
-- now to the meat
--
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;

exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,
4000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
l_columnValue := replace(l_columnValue,'&apos;','''');
l_columnValue := replace(l_columnValue,'&amp;','&');
l_columnValue := replace(l_columnValue,'"','''');
l_columnValue := replace(l_columnValue,chr(13),null);
l_columnValue := replace(l_columnValue,chr(10),' ');
l_columnValue := '"'||l_columnValue||'"';

utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;

end dump_csv;





--
jeremy

In article <ge70m2$3jc$1 (AT) registered (DOT) motzarella.org>,
gogala.mladen (AT) gmail (DOT) com says...>
Here is a function for any query. It is based on a Tom Kyte example:

-----------------------------------------------------------------------
-- DUMP_CSV
-----------------------------------------------------------------------
function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_filename in varchar2 )
return number
--
-- this code is courtesy of Tom Kyte of Oracle, modified by jeremy0505
--
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;

l_descTbl dbms_sql.desc_tab;
begin

begin
l_output := utl_file.fopen( 'UTFD_EXTRACT',
p_filename, 'w',32000 );
exception
when others then
dbms_output.put_line('Error opening file with utl_file');
raise;
end;


--
-- column headings first
--
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

dbms_sql.describe_columns( l_theCursor,
l_colCnt, l_descTbl );
for i in 1..l_colCnt
loop
if i = 1 then
utl_file.put( l_output, l_descTbl(i).col_name);
else
utl_file.put( l_output, p_separator ||
l_descTbl(i).col_name );
end if;
end loop;
utl_file.new_line( l_output );


--
-- now to the meat
--
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;

exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,
4000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
l_columnValue := replace(l_columnValue,'&apos;','''');
l_columnValue := replace(l_columnValue,'&amp;','&');
l_columnValue := replace(l_columnValue,'"','''');
l_columnValue := replace(l_columnValue,chr(13),null);
l_columnValue := replace(l_columnValue,chr(10),' ');
l_columnValue := '"'||l_columnValue||'"';

utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;

end dump_csv;





--
jeremy

On Oct 28, 5:23*am, tel... (AT) gmx (DOT) de (Tellie) wrote:
Here's a few: http://asktom.oracle.com/tkyte/flat/index.html

jg
--
@home.com is bogus.
http://www.theregister.co.uk/2008/10...udent_charged/