EXTERNAL TABLE

INTRODUCTION:

  • In this blog, we are going to create the external table
DESIGNATION:
  • This is easy to make and easy to insert the data quickly.
  • This is much faster than SQL * LOADER.
  • In this type of table, we cannot use a DML statement.
  • Bulk data can be easily inserted into the table.
OVERALL STEPS:

Step 1: Go to make a directory at the OS level

Step 2: After making the directory

Go to the directory, we have to create a text file and add the necessary values.

Step 3: We have to notify the location of the directory.

Step 4: Go to database, conn sys user

We have to permit for creation of a directory in that user by using the sys

Step 5: Create the directory and mention the location of the OS.

Step 6: Conn sys user,

We have to permit reading and writing to create a directory by using sys.

Step 7: Create the table and give the external format

Step 8: View the table

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SQL LOADER

DESIGNATION:

  • SQL * LOADER loads data from external files into tables of an oracle database.
  • We do not insert the data into the table using SQL * LOADER directly.
  • We need a control file for inserting the data.
  • SQL * LOADER is used to just execute the commands of the control file.
  • The flatfile saving process is normally we will save .txt files.
  • The Control file saving process is normally we will save .ctl files.

BAD FILE:

  • A bad file is used to store the rejected records.
  • The rejected records will store in a bad file automatically.
  • If you wrongly type the data into a flat file, you will surely get some errors. So that error during SQL * LOADER process which stores in bad file.

TYPES OF FLAT FILE:

Variable record flatfile

This is with delimeter

eg., 101, abc, 2000
Fixed record flatfile

This is without delimeter

eg., 101abc2000

The following picture illustrates the SQL * LOADER process:

sql loadet

 

SYNTAX:

$sqlldr <space> userid = username / password

control = ‘path of the controlfile’ <space> log = test.log

Creating a controlfile for variable record flatfile:

BY USING INSERT LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

insert / append / truncate / replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

OVERALL STEPS:

To execute the SQL * LOADER tool, you need at least three files.

  • The input data files store delimited data
  • The parameter file stores the location of the input and output files.
  • The control file contains the specification on how data is loaded.

Step 1: Create the table at the database level

View the table :

Step 2: Create the text document by using the vi editor in os level

View the text file

Step 3: Create the control file by using the vi editor in the os level

View the control file 

Step 4: Execute the SQL * LOADER syntax

Step 5: View the  table

 

BY USING APPEND LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

append

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the APPEND output:

BY USING TRUNCATE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

truncate

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the TRUNCATE output:

BY USING REPLACE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the REPLACE output:

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8