How to use Oracle_loader Datapump utility.

oracle database external table population with datapump utility.

We can load data from non oracle file to oracle database using oracle loader utility.

• First Create Datapump Directories.

  Sql > Create Directory data as '/u01/data/';

 hosted with ❤ by GitHub

• Check Directory.

     Sql > Desc Dba_Directories

  Sql > set line 120 pages 150

  Sql > col owner format a12

  Sql > col Directory_path for a75

  Sql > Select * form dba_directories;

hosted with ❤ by GitHub

• Create Text file in Directory Folder.

File Name T2.txt gedit /u01/data/T2.txt

10,Allen

20,Ram 30,Shyam

Save This File

view raw hosted with ❤ by GitHub

• Login to Oracle.

Sqlplus / as sysdba Show user

 hosted with ❤ by GitHub

• Create Table.

Create Table Ext(Id Number, Name varchar2(20))

organisation external

(type oracle_loader default directory dump access parameters

(records delimited by newline

  fields terminated by ','

)

location('T2.txt'));

Table Created.

 hosted with ❤ by GitHub

• Execute Sql Statement.

Sql > Select * from Ext; Id Name --- ---------------- 10 Allen 20 Ram 30 Shyam

 hosted with ❤ by GitHub

• In this way we can easily use sql loader utility for load  data from non  Oracle file to Oracle database.
• I will show you another thing.

Goto T2.txt text file and open it.

Id Name

--- ---------------
10 Allen
20 Ram
30 Shyam
40 Parag
50 Piyush

 hosted with ❤ by GitHub

• Add New Records in this file.
• Goto SQl Prompt and Run Command.

Sql > Select * from Ext;

Id Name

--- ------------------
10 Allen
20 Ram
30 Shyam
40 Parag
50 Piyush

 hosted with ❤ by GitHub

• Using Oracle Loader we can add record in Text file and Record will reflect to Oracle Table.
• Using Oracle Loader If we Modify Text file these modification is reflect on oracle database.