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

How to copy the information from a text file into a table using 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

In the previous section, we simply counted the number of lines available in a text file.  Now, let us further extend the same program by transferring the information available in the text file into an Oracle database using PL/SQL.

Before going to the PL/SQL program, we need to create a simple table named “sampletable” as follows:

create table SampleTable
(
    remarks varchar2(200)
)

You can understand that I created a table “SampleTable” with only one column, “remarks” (max. up to 200 chars).  Now, we need to insert rows into that table using PL/SQL.

The PL/SQL code to achieve the desired is the following:

declare
    f utl_file.file_type;
    s varchar2(200);
    c number := 0;
begin
    f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
    loop
        utl_file.get_line(f,s);
        insert into sampletable values (s);
        c := c + 1;
    end loop;
exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
        dbms_output.put_line('No. of rows inserted : ' || c);
end;

The only extra statement I used from the above statement is the “INSERT” statement.  You may also use the statement “COMMIT” either at the INSERT statement or after you close the file handle (in the exception section).  I just left it without committing.  But it is recommended that you commit once you finish the work. 



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