Writing data to a text file from Oracle

Using External table we can read the data in the text file and put it in the respective table(s) in the schema.

In a similar fashion, we can also read data from regular tables and write contents of the table into flat file. Only thing to remember is, to write a data into text file, we need to use ORACLE_DATAPUMP access driver.

Up until Oracle 9i, external tables were read only. It means we could not write into external table from the schema.

To load data from flat file Oracle uses ORACLE_LOADER access driver which is based on the SQL*Loader API.

Starting Oracle 10g, Oracle introduced another method to export/import the data, called data pump. Using same data pump access driver, now we can also write into text/external file from database.

To deal with external tables, we need to create Oracle directory object which maps to folder on the operating system. So, we will create directory object which specifies an alias for a folder/directory on a server. Make sure that you have the correct privilege to create the directory object and correct permission on the o/s folder so that there is no error when we write data into text file.

Again, the directory structure we use is defined by user_dump_dest oracle parameter. We can check the value of this parameter using SHOW PARAMETER command. Following is the command to create directory. Path could be different on your system so modify the command before executing it. Let us first create directory object from SQL*Plus.

SQL> CREATE
OR
REPLACE
DIRECTORY DATALOAD AS
'D:\oracle\product\10.2.0\admin\orcl\udump';

Directory created.

Oracle user, who issues the create directory command, automatically gets read and write privilege on the directory. Other oracle users should be given specific grants to read and write content from the directory.

Now we will create an empty table and populate it with some data. Connect to the user using SQL*Plus and execute following sqls.

CREATE TABLE TEST_TABLE
(
TEST_TABLE_ID NUMBER(9) NOT NULL,
CITY VARCHAR(50),
STATE VARCHAR(2),
ZIP VARCHAR(10),
CNTRY VARCHAR(50),
CONSTRAINT PK_TEST_TABLE PRIMARY KEY(TEST_TABLE_ID)
)
/

INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
VALUES(1,'ATLANTA','GA','30318′,'USA')
/
INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
VALUES(2,'CALGARY','AB','T6H-5K9′,'CANADA')
/
INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
VALUES(3,'JOHNS CREEK','GA','30097′,'USA')
/
INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
VALUES(4,'FOREST HILLS','NY','11798′,'USA')
/

This time we will create external tables to write data directly into file from the database. Execute following statement from SQL> prompt.

CREATE TABLE external_city
(
ID,CITY,STATE,ZIP,CNTRY
)
ORGANIZATION EXTERNAL
(
TYPE oracle_datapump
DEFAULT DIRECTORY dataload
LOCATION ('external_city.txt')
)
AS
SELECT TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY
FROM TEST_TABLE
/

Above command will write to an external table i.e. Oracle will push data to the file from the database upon execution of the command. The query (select statement) is shown in simplest form. We can write complex select statement which involves joins, functions etc. as well to create external table. One key thing to note is, access driver (TYPE) is defined as oracle_datapump. Also we haven't specified data type for any column in the CREATE TABLE clause. Oracle will automatically take the data type of columns mentioned in the SELECT clause. We can check it by describing the table.

SQL> desc external_city
Name Null? Type
——————— ——– ————
ID NOT NULL NUMBER(9)
CITY VARCHAR2(50)
STATE VARCHAR2(2)
ZIP VARCHAR2(10)
CNTRY VARCHAR2(50)

We can even create table without specifying any column names. Run following statement from SQL> prompt. If you already have created table earlier, drop the table and delete the file from the o/s folder before executing this statement.

CREATE TABLE external_city
ORGANIZATION EXTERNAL
(
TYPE oracle_datapump
DEFAULT DIRECTORY dataload
LOCATION ('external_city.txt')
)
AS
SELECT TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY
FROM TEST_TABLE
/

Whenever table is created, Oracle always creates at least two files. Text file as specified in LOCATION clause and log file. Every time when SELECT is performed, log file will be updated. Some points to keep in mind about the file are
• Dropping a table will not drop the file. One has to delete it once table is dropped.
• File can be dropped without dropping a table, but later when SELECT is performed, oracle will give an error.
• File cannot be edited manually using any other editor like notepad, wordpad or vi etc.
• Multiple files can be specified in LOCATION clause but it has to follow directory:filename syntax.

Once table is created, we can use SELECT statement against external table in a same way as we use it against regular tables. Table can be used in join condition with other tables as well.

SQL> SELECT ID, City, State, Zip, Cntry FROM External_City;

ID CITY ST ZIP CNTRY
———- ————— — ———- ———-
1 ATLANTA GA 30318 USA
2 CALGARY AB T6H-5K9 CANADA
3 JOHNS CREEK GA 30097 USA
4 FOREST HILLS NY 11798 USA