- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- How to export Oracle Data
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Forums
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-11-2003 11:20 PM
тАО03-11-2003 11:20 PM
How can I export oracle table to file which is comma-seperated that can use tools like excel to see data ?
I understand that exp command only work for Oracle imp.
Thank in advance
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 12:05 AM
тАО03-12-2003 12:05 AM
Re: How to export Oracle Data
set head off
set feed off
set pages 0
select field1||','||field2||',' ...||fieldn from table;
Watch out for data that contains commas though!
Regards,
John
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 12:07 AM
тАО03-12-2003 12:07 AM
Re: How to export Oracle Data
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 12:11 AM
тАО03-12-2003 12:11 AM
Re: How to export Oracle Data
You could issue an select statement on the table and spool the output to a text file.
> spool c:\test.txt
select
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 01:19 AM
тАО03-12-2003 01:19 AM
Re: How to export Oracle Data
set termout off
set pagesize 0
set heading off
set feedback off
//your select statements here on the table//
spool off
you can open the .csv file in excel.
Revert on further help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 01:31 AM
тАО03-12-2003 01:31 AM
Re: How to export Oracle Data
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 02:59 AM
тАО03-12-2003 02:59 AM
Re: How to export Oracle Data
You can import the data into MS EXCEL using ODBC..
Configure odbc for oracle specifying the userid and service name(connect string) and then open MS Excel and go to DATA menu--> import data--> new database query and then enter the options u require i.e table, system table for a particular user and the columns u require to be included in the query...
This works fine..have tried it on OFFICE XP...
rgds
Blade..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 01:00 PM
тАО03-12-2003 01:00 PM
Re: How to export Oracle Data
sqlplus user/pass <> /dev/null
set head off
set feed off
set linesize 500
set pagesize 10000
spool filedata.lst
select fielda||','||fieldb||','||fieldc from tableA
spool off;
exit;
!
You might have to remove the sqlstatement automaticlly placed at the top, but this should output in CSV.
brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 04:51 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 07:53 PM
тАО03-12-2003 07:53 PM
Re: How to export Oracle Data
I'm going to suggest UTL_FILE for this task since:
- you have great control over the output file format and you seem to need that here.
- you can easily use dbms_job to schedule this procedure to run automatically every day at a certain time.
Here is a working example. I dump dates in a non-standard format (but one that saves all of the fields). you can modify as you wish.
yd@yddb.ora8i.mu> create or replace procedure dump_table_to_csv( p_tname in varchar2,
2 p_dir in varchar2,
3 p_filename in varchar2 )
4 is
5 l_output utl_file.file_type;
6 l_theCursor integer default dbms_sql.open_cursor;
7 l_columnValue varchar2(4000);
8 l_status integer;
9 l_query varchar2(1000)
10 default 'select * from ' || p_tname;
11 l_colCnt number := 0;
12 l_separator varchar2(1);
13 l_descTbl dbms_sql.desc_tab;
14 begin
15 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
16 execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
17
18 dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
19 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
20
21 for i in 1 .. l_colCnt loop
22 utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
23 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
24 l_separator := ',';
25 end loop;
26 utl_file.new_line( l_output );
27
28 l_status := dbms_sql.execute(l_theCursor);
29
30 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
31 l_separator := '';
32 for i in 1 .. l_colCnt loop
33 dbms_sql.column_value( l_theCursor, i, l_columnValue );
34 utl_file.put( l_output, l_separator || l_columnValue );
35 l_separator := ',';
36 end loop;
37 utl_file.new_line( l_output );
38 end loop;
39 dbms_sql.close_cursor(l_theCursor);
40 utl_file.fclose( l_output );
41
42 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
43 exception
44 when others then
45 execute immediate 'alter session set nls_date_format=''dd-MON-yy''';
46 raise;
47 end;
48 /
Procedure created.
yd@yddb.ora8i.mu> exec dump_table_to_csv( 'emp', '/tmp', 'tkyte.emp' );
PL/SQL procedure successfully completed.
yd@yddb.ora8i.mu> host cat /tmp/tkyte.emp
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,SMITH,CLERK,7902,17-dec-1980 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,20-feb-1981 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,22-feb-1981 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,02-apr-1981 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,28-sep-1981 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,01-may-1981 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,09-jun-1981 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,09-dec-1982 00:00:00,3000,,20
7839,KING,PRESIDENT,,17-nov-1981 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,08-sep-1981 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,12-jan-1983 00:00:00,1100,,20
7900,JAMES,CLERK,7698,03-dec-1981 00:00:00,950,,30
7902,FORD,ANALYST,7566,03-dec-1981 00:00:00,3000,,20
7934,MILLER,CLERK,7782,23-jan-1982 00:00:00,1300,,10
See the Supplied Packages guide for info on utl_file. You must set some init.ora parameters to enable it to work correctly....
This can be done via sqlplus as well -- just not as nicely in my opinion.
HTH
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 08:53 PM
тАО03-12-2003 08:53 PM
Re: How to export Oracle Data
AS every one suggested, you can write the SQL to extract the data in the format you want and spool the out put to a file.
OR
Download a free version of TOAD from http://www.toadsoft.com and the executable is just 3.5MB. Once you downloaded all you need to access the database is an entry in the tnsnames.ora in ORACLE_HOME directory on your PC and connect tot he database. The export function in TOAD has a facility to save the exported data of the table in various formats (excel, txt, dmp...)files. So if your table is large, TOAD will be the best option for you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-12-2003 11:33 PM
тАО03-12-2003 11:33 PM
Re: How to export Oracle Data
i can think of two ways. The first is like others stated a SQL querry. You could set the Colum serperator to ";" like this:
spool
set head off
set feed off
set linesize 500
set pagesize 10000
set COLSEP ';'
select ......
spool off
Something like this should work. Also you could have a look at Reports6i which is a free Report genereator vor Oracle 8i and up. (But its a ~260MB download)
I havn??t had a deep look into it but i assume it can output its reports in a ascii/csv file.
But keep in mind that Excel is not reconmendet for really large tables. AFAIK there is a limitation to max. 64000 lines per table.
Hope this helps
Regards Stefan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-13-2003 12:46 AM
тАО03-13-2003 12:46 AM
Re: How to export Oracle Data
I suppose you can refer to my answer in another thread.
the script is to generate the conttrol file and the SQL to generate the data file to be uploaded to an Oracle table with SQLloader.
I think you can use the mecanism if you want to upload the data into a spreadsheet or MS Access, I suppose you have a very good reason to do so.
Rgds,
Jean-Luc
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xd4e2a848deccd61190050090279cd0f9,00.html