More SQL Loader basics - using STR



Another random SQL Loader demo - here i'm assuming that the input file is just one long string of numbers separated by commas - i just want to load this into a single column table so i can do 'stuff' with it.

So i create a single column table

SQL> create table demo(col1 number);

Table created.

Here is my input file

# cat input.csv
1,2,3,4,5,66,77,888,99991,2,3,4,5,66,77,888,99991,2,3,4,5,66,77,888,99991,2,3,4,5,66,77,888,etc etc

And here is my controlfile

LOAD DATA
INFILE "input.csv" "STR ','"
INTO TABLE DEMO
TRUNCATE
FIELDS TERMINATED by ','
TRAILING NULLCOLS
(COL1)

The 'trick' is the use of the STR command to tell sqlloader that comma is my end or record marker (as well as being the end of field marker)

Now run that in

sqlldr demo/demo control=loader.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Wed Dec 17 14:00:12 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 64
Commit point reached - logical record count 65

Table DEMO:
  65 Rows successfully loaded.

Check the log file:
  loader.log
for more information about the load.

And there we have it

SQL> select * from demo.demo;

      COL1
----------
         1
         2
         3
         4
         5
        66
        77
       888
     99991
         2
   etc

Kind of pointless but the demo of the STR feature is useful



3 comments: