Home arrow Oracle arrow Page 5 - Writing to Text Files in Oracle PL/SQL

How to copy from a table into the text file from PL/SQL - Oracle

This is the second article in a series focusing on file input/output using Oracle PL/SQL packages.

TABLE OF CONTENTS:
  1. Writing to Text Files in Oracle PL/SQL
  2. How to count the number of lines from the text file
  3. How to copy the information from a text file into a table using PL/SQL
  4. How to write into a text file from PL/SQL
  5. How to copy from a table into the text file from PL/SQL
  6. Other ways to write the information into a text file from PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 139
April 18, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

We have already seen how to write to files from the previous section.  Now, we shall see how to copy information from a table into the text file. 

Let us go through the following code first:

declare
    f utl_file.file_type;
begin
    f := utl_file.fopen('SAMPLEDATA','sample2.txt','W');
    for s in (select remarks from sampletable)
    loop
        utl_file.put_line(f,s.remarks);
    end loop;
    utl_file.fclose(f);
end;

For the above code, I am using the same table defined in the previous sections.  Let us go through the most important bits of the above program.

for s in (select remarks from sampletable)

The above statement goes through every row in the table “sampletable” and retrieves the value of the column “remarks” and places it in the variable “s.”  The variable “s” would contain a new value for every iteration of the “for” loop.  Further proceeding, we have the following:

utl_file.put_line(f,s.remarks);

The above statement is a bit different from any of the previous ones.  You must consider that “s” is no longer a simple “varchar2” variable.  It is being managed by PL/SQL automatically.  And “s” alone cannot maintain a single value.  In fact, it maintains a RECORD type of value.  That means the variable “s” may contain more than one column based on the SELECT statement we provide.  And thus to retrieve any value from “s,” we need to specify the column value also along with the variable “s.”



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: