topic Re: How to export Oracle Data in Operating System - HP-UX

How to export Oracle Data

Printaporn_1 — Wed, 12 Mar 2003 07:20:29 GMT

Hi,

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

Re: How to export Oracle Data

John Palmer — Wed, 12 Mar 2003 08:05:22 GMT

One simple way is to write a SQL query, something like...

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

Re: How to export Oracle Data

Printaporn_1 — Wed, 12 Mar 2003 08:07:55 GMT

Thanks John but I have huge table that my requirement is I want the output as a file.

Re: How to export Oracle Data

BLADE_1 — Wed, 12 Mar 2003 08:11:33 GMT

hi,

You could issue an select statement on the table and spool the output to a text file.

> spool c:\test.txt
select from ;
spool off

you could also properly format the output by setting format parameters in sqlplus or inside the select statement.

rgds
Blade

Re: How to export Oracle Data

T G Manikandan — Wed, 12 Mar 2003 09:19:25 GMT

spool file-name.csv
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

Re: How to export Oracle Data

T G Manikandan — Wed, 12 Mar 2003 09:31:25 GMT

Also check this attachment

Re: How to export Oracle Data

BLADE_1 — Wed, 12 Mar 2003 10:59:40 GMT

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

Re: How to export Oracle Data

Brian Crabtree — Wed, 12 Mar 2003 21:00:39 GMT

John is correct in how to unload that data from the system. The best way to do it would be something like the following:

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

Re: How to export Oracle Data

Stan_17 — Thu, 13 Mar 2003 00:51:34 GMT

Hi,

Apart from what others have suggested...

free version of TOAD has a built-in utility of extracting data in csv format. you might want to look at that if you want to extract more and more tables.

hth,
Stan

Re: How to export Oracle Data

Yogeeraj_1 — Thu, 13 Mar 2003 03:53:58 GMT

hello,

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

Re: How to export Oracle Data

Indira Aramandla — Thu, 13 Mar 2003 04:53:11 GMT

Hi,

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.

Re: How to export Oracle Data

Stefan Schulz — Thu, 13 Mar 2003 07:33:39 GMT

Hi,

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

Re: How to export Oracle Data

Jean-Luc Oudart — Thu, 13 Mar 2003 08:46:29 GMT

Hi
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