Variable record format

Variable record format:-
-------------------------

Variable Record Format would like below where the data fields are separated by a delimiter. Note: The Delimiter can be anything you like. In this case it is "|"

NAME1|0001|9999|08-JAN-08|10

NAME1112|0033|989|08-FEB-08|20

NAME1231|0021|9999|08-SEP-08|30

NAME45601|0041|9999|08-AUG-08|40

NAME23451|0051|9999|08-JUL-08|30

NAME56541|0023|9999|08-JAN-08|40

Structure of a Control file:

Sample CONTROL/CTL file for loading a variable record data file.

OPTIONS (SKIP = 2) --The first 2 rows will be skipped in data file.

LOAD DATA -- It’s kind of the BEGIN statement in CTL file.

INFILE '$FILE' -- Specify the data file path and name

APPEND -- Type of loading (INSERT, APPEND, REPLACE, TRUNCATE

INTO TABLE ‘Your Table Name’ -- the table to be loaded into

FIELDS TERMINATED BY '|' -- Symbol (|) which used to separate the col’s

OPTIONALLY ENCLOSED BY '"' --Values of the data fields may be enclosed (")

TRAILING NULLCOLS -- columns that are not present in the record treated as null

(COL1 “TRIM (:COL1)", -- Can use all SQL functions on columns

COL2 DECIMAL EXTERNAL,

COL3 DECIMAL EXTERNAL,

COL4 "TO_CHAR(:COL,'DD-MON-YY')", --Date format can be given

COL5 CONSTANT "AOT"

-- Can specify constant value instead of getting value from data-file

)

OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n -- Number of logical records to skip (Default 0)

LOAD = n -- Number of logical records to load (Default all)

ERRORS = n -- Number of errors to allow (Default 50)

ROWS = n -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, direct path all)

BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- Suppress messages during run (header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} --Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} -- Perform parallel load (Default FALSE)

LOAD DATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location and name of the data-file.

INFILE * specifies that the data is found in the control file and not in an external file. You will find the data in the same CONTROL file.

INFILE '$FILE', can be used to send the file path and filename as a parameter when registered as a concurrent program in the Oracle Application.

INFILE '/home/demo/phani/file01.txt' specifies the file-path and the filename.

Example: - For the data-file is an external file.

LOAD DATA

INFILE ' /home/demo/phani/file01.txt'

INTO TABLE XX_EMP

FIELDS TERMINATED BY ","

(NAME, ENUM, SAL, JDATE, DEPTNO)

Example: - For the data-file is in the Control file.

LOAD DATA

INFILE *

INTO TABLE xx_emp

FIELDS TERMINATED BY "|"

( NAME, ENUM, SAL, JDATE,DEPTNO )

BEGINDATA

PHANI|0001|9999|08-JAN-08|10

REDDY|0033|989|08-FEB-08|20

RAHUL|0021|9999|08-SEP-08|30

MANOJ|0041|9999|08-AUG-08|40

RANA|0051|9999|08-JUL-08|30

Example: - For the file name and path is sent as a parameter when registered as a concurrent program

LOAD DATA

INFILE '$FILE'

INTO TABLE SCOTT.XX_EMP

FIELDS TERMINATED BY ","

(NAME, ENUM, SAL, JDATE, DEPTNO)

TYPE OF LOADING:

INSERT -- If the table you are loading is empty, INSERT can be used.

APPEND -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.

REPLACE -- All rows in the table are deleted and the new data is loaded

TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.

INTO TABLE is required to identify the table to be loaded and in the above example INTO TABLE "SCOTT"."XX_EMP", SCOTT refers to the Schema and XX_EMP is the Table name.

Note: - The Schema name is not the mandatory. Since, when we run the command to run the CTL file, we will provide the login information. So, what ever the username we give in the command It will look for the object in that schema. (Schema name is nothing but the Username you are using to login into the database).

FIELDS TERMINATED BY specifies how the data fields are terminated in the data-file. (If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.

TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

Command to run the SQL*Loader:-

sqlldr USERID='User_name'/'Password' CONTROL='Your control filename' LOG='Log file name'

Example:- sqlldr USERID=scott/tiger CONTROL=aottest.ctl

1) You provide the Username and password to connect to the database.
2) Control file name should be given which you want to run. In the above Example aottest.ctl control file name.
3) Log file name can be given. So that when log file is create by the command would create with same name as given. But this is not the default parameter. If log name is not given then by default name would be given by the system.

PREVIOUS                                    SQLLOADER-HOME                               NEXT