CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
user1285928
user1285928

Reputation: 1476

Transfer data from Oracle table to text file

I'm interested is it possible with PL/SQL block to transfer the content of a Oracle table into text file on the Hard Drive. I need a PL/SQL block which can download the content of a table witch will be used to store log data into text file.

Regards

Upvotes: 1

Views: 16106

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

pratik garg's answer is a good one.
But, you might want to consider also the use of an EXTERNAL TABLE.
Basically, it's a table which is mapped to a file. So every row inserted to the table is automatically written to a file.
you can see an example here

Upvotes: 4

pratik garg
pratik garg

Reputation: 3342

you can use UTL_file package for this..

you can try below type of block --

declare 
p_file util_file.file_type;
l_table <your_table_name>.ROWTYPE;
l_delimited varchar2(1) := '|';
begin
p_file:= utl_file.fopen('<file_path>','<file_name>','W');
for l_table in (select * from <your_table_name>) loop
utl_file.putline(p_file,l_table.col1||l_delimited||l_table.col2||l_delimited||l_table.col3||l_delimited||l_table.col4||l_delimited <continue with column list .........> ||chr(10));
end loop;    
utl_file.fclose_all();
end;

Upvotes: 8

Related Questions