SQL

How Do You Use The SQL*Loader Utility

Article SQL

EXAMPLE 1

Follow the Below Steps,

Step 1

Below is the sample data. Put this data in a Microsoft Excel sheet and save it as a comma-separated file named emp_detail.csv.

  • Tom 1023424
  • Andrew 1014224
  • Richard 1033422

Step 2

Below is the sample control file (emp.ctl) that will load an external data file containing delimited data of step one.

LOAD DATA
infile 'C:emp_detail.csv'
into table emp_detail
fields terminated by ","
TRAILING NULLCOLS
(
  EMP_ID,
  EMP_NAME,
  CONTACT_NUM
)

Step 3

Now open cmd and write the following Command

sqlldrobiee_user/obiee_user@xe control=emp.ctl log=emp.log

See the output.

Output

How Do You Use The SQL*Loader Utility

Example 2

Below is Another Sample control file with in-line data formatted as fix length records. The trick is to specify “*” as the name of the data file, and use BEGINDATA to start the data section in the control file,

LOAD DATA
infile *
into table product
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
 p_id ,
 p_name,
 emp_id
)
BEGINDATA
2,"nokia",3
3,"LG",4

OUTPUT

How Do You Use The SQL*Loader Utility

EXAMPLE 3

To set the emp_id to 0 where emp_id is found to be 1

LOAD DATA
infile *
into table emp_detail
fields terminated by ","
TRAILING NULLCOLS
(
  EMP_ID INTEGER EXTERNAL DEFAULTif EMP_ID='1',
  EMP_NAME,
  CONTACT_NUM
)
BEGINDATA
1,kavita,2344
2,reshu,12412

EXAMPLE 4

To skip the coloumnemp_id using filler keyword.

Load data
infile *
replace into table emp_detail
fields terminated by ","
(
  EMP_ID filler,
  EMP_NAME,
  CONTACT_NUM
)
BEGINDATA
1,kavita,2344
2,reshu,12412

Example 5

To insert constant value 0 in emp_id

Load data
infile *
into table emp_detail
fields terminated by ","
TRAILING NULLCOLS
(
EMP_ID CONSTANT '0',
EMP_NAME,
CONTACT_NUM
)
BEGINDATA
1,kavita,2344
2,reshu,12412
Aastha Sharma
Latest posts by Aastha Sharma (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *