db2 EXPORT TO ~/data_export/$TABLE.ixf OF IXF MESSAGES messages.txt SELECT \* FROM schema.$TABLE
2 1502
I'd probably use sqlplus myself (see; http://asktom.oracle.com/~tkyte/flat/index.html
for some tools).
PL/SQL can do it, it might look like: - create or replace function dump_csv( p_query in varchar2,
-
p_separator in varchar2
-
default ',',
-
p_dir in varchar2 ,
-
p_filename in varchar2 )
-
return number
-
AUTHID CURRENT_USER
-
is
-
l_output utl_file.file_type;
-
l_theCursor integer default dbms_sql.open_cursor;
-
l_columnValue varchar2(2000);
-
l_status integer;
-
l_colCnt number default 0;
-
l_separator varchar2(10) default '';
-
l_cnt number default 0;
-
begin
-
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
-
-
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
-
-
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,
-
2000 );
-
-
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 );
-
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;
-
/
-
-
-
You would use that for example like this:
-
-
create or replace procedure test_dump_csv
-
as
-
l_rows number;
-
begin
-
l_rows := dump_csv( 'select *
-
from all_users
-
where rownum < 25',
-
',', '/tmp', 'test.dat' );
-
end;
-
/
Make sure to read about the INIT.ORA parameters (utl_file_dir) you need to setup !!!
Your export command is exporting IXF file, you just need to change it to DEL instead of IXF.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kenneth Koski |
last post by:
Hello All,
I have a comma delimited text file, which I would like to move into a
SQL 2000 table . I created a DTS package in SQL Server and saved it as a
VB.bas . I am writting the code in C#...
|
by: Bernie Yaeger |
last post by:
Is there a way to convert or copy a .xml file to a comma delimited text file
using vb .net?
Thanks for any help.
Bernie Yaeger
|
by: Avi |
last post by:
I need to create a text file that has the data from the 10 tables in
the database. The number of fields in the tables exceeds 255 and so I
cannot make a new table with all the fields and then...
|
by: JustSomeGuy |
last post by:
Hi. I have a comma delimited text file that I want to parse.
I was going to use fscanf from the C library but as my app is written
in C++
I thought I'd use the std io stream library...
My Text...
|
by: gujarsachin2001 |
last post by:
Hi friends,
I m just stuck with extracting excel file data into text file.
So can any body please help me out.
thx,
Sachin.
|
by: MMcCarthy |
last post by:
This is a module that imports information to a Query or a Table from comma separated values file in a text format! It is very helpful for getting information from other applications and from files!
...
|
by: Mohammad Omer |
last post by:
Hi,
I tried to validate file path without calling file creating functions.
Is it possible? How?
Regards,
-aims
|
by: Quentin |
last post by:
I want to take a comma delimited text file and export it to excel
format, using the comma as the delimiter.
Any help on this would be great, thank you.
|
by: rparimi |
last post by:
I am trying to redirect stderr of a process to a temporary file and
then read back the contents of the file, all in the same python
script. As a simple exercise, I launched /bin/ls but this doesn't...
|
by: rizwan6feb |
last post by:
I am trying to extract php code from a php file (php file also contains html, css and javascript code). I am using the following regex for this
<\?*?\?>
but this doesn't cater quotation marks...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |