Home » SQL & PL/SQL » SQL & PL/SQL » How can i covert/ generate a file(txt,xls,doc) from table data in oracle
icon7.gif  How can i covert/ generate a file(txt,xls,doc) from table data in oracle [message #199988] Fri, 27 October 2006 05:33 Go to next message
sundarsoft
Messages: 15
Registered: October 2006
Junior Member
Hi
Plz tell me how to convert the table data into the file

using external tables we can do file data into Oracle table
but is it possible to do vice versa?

Thanks in advance
Sundar
Re: How can i covert/ generate a file(txt,xls,doc) from table data in oracle [message #199989 is a reply to message #199988] Fri, 27 October 2006 05:35 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 search the forum for utl_file.



regards,
Re: How can i covert/ generate a file(txt,xls,doc) from table data in oracle [message #200111 is a reply to message #199988] Sat, 28 October 2006 01:11 Go to previous messageGo to next message
pankyz
Messages: 11
Registered: October 2006
Junior Member

Yes,
If you are using Oracle 10g
Here is the solution
Unloading data into an external file...

Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver. This flat file is in an Oracle-proprietary format that can be read by DataPump. The syntax is similar to the CREATE TABLE... ORGANIZATION EXTERNAL above, but simpler -- since you can't specify the data format, you can specify very few access_parameters. The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.


SQL> create table export_empl_info
2 organization external
3 ( type oracle_datapump
4 default directory xtern_data_dir
5 location ('empl_info_rpt.dmp')
6* ) as select * from empl_info
SQL> /

Table created.

SQL> select * from export_empl_info ;

EMPL_ID LAST_NAME FIRST_NAME SSN BIRTH_DT
------- --------------- --------------- --------- ----------
001 Hutt Jabba 896743856 01/01/1979
002 Simpson Homer 382947382 01/01/1979
003 Kent Clark 082736194 01/01/1979
004 Kid Billy 928743627 01/01/1979
005 Stranger Perfect 389209831 01/01/1979
006 Zoidberg Dr 094510283 01/01/1979

6 rows selected.

... and back in again

You can now move the file you just created, empl_info_rpt.dmp, to another system and create an external table to read the data:


SQL> connect joe/some.where.else
Connected.
SQL> create table import_empl_info
2 ( empl_id varchar2(3),
3 last_name varchar2(50),
4 first_name varchar2(50),
5 ssn varchar2(9),
6 birth_dt date
7 )
8 organization external
9 ( type oracle_datapump
10 default directory xtern_data_dir
11 location ('empl_info_rpt.dmp')
12 ) ;

Table created.

SQL> select * from import_empl_info ;

EMPL_ID LAST_NAME FIRST_NAME SSN BIRTH_DT
------- --------------- --------------- --------- ----------
001 Hutt Jabba 896743856 01/01/1979
002 Simpson Homer 382947382 01/01/1979
003 Kent Clark 082736194 01/01/1979
004 Kid Billy 928743627 01/01/1979
005 Stranger Perfect 389209831 01/01/1979
006 Zoidberg Dr 094510283 01/01/1979

6 rows selected.

Conclusion

We've seen an introduction to loading and unloading data with external tables. External tables in 9i and 10g provide a convenient, seamless way to move data in and out of the database, integrating SQL*Loader and Data Pump functionality with the power, scriptability and ease of SQL statements. It's definitely worth considering external tables the next time you have a daily upload or download to arrange

Thanks
Re: How can i covert/ generate a file(txt,xls,doc) from table data in oracle [message #200112 is a reply to message #199988] Sat, 28 October 2006 01:14 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Try to used "spool" option.

sql>set heading off
sql>set verify off
sql>set feedback off
sql>spool on
sql>spool temp.sql---file name
sql>You're query goes here.
sql>spool off.

and Check ur temp.sql file.
Previous Topic: EXCHANGE PARTITION PROBLEM IN A PARTITIONED TABLE
Next Topic: it keep coming up missing expression in line 1
Goto Forum:
  


Current Time: Mon Dec 05 21:31:04 CST 2016

Total time taken to generate the page: 0.12103 seconds