I need to consume the following file into an Oracle 11 table:
ID|9876431
DESCRIPTION|This is a really cool one
PRODUCT|Game
AVAILABLE|Y
OPTIONAL1|Non-Violent
REC
ID|988224
DESCRIPTION|Another cool one
PRODUCT|Blender
AVAILABLE|Y
NAME|Bobby-Sue
OPTIONAL1|2 Quart
OPTIONAL2|Green
HOT|N
REC
ID|3811231
DESCRIPTION|Yet another fancy one
PRODUCT|Brick
AVAILABLE|N
NAME|George
HOT|N
REC
Each record is delimited by the key REC. The first line for a record is always the ID, the other key-value pairs can be in any order except that the REC must be the last. Some key-value pairs are optional.
The table to put the three records in is:
CREATE TABLE ITEMS
(
EXTERNAL_ID NUMBER (9, 0),
DESCRIPTION VARCHAR2 (140 BYTE),
NAME VARCHAR2 (100 BYTE),
PRODUCT VARCHAR2 (100 BYTE),
AVAILABLE VARCHAR2 (1 BYTE),
HOT VARCHAR2 (1 BYTE),
OPT_VAR1 VARCHAR2 (100 BYTE),
OPT_VAR2 VARCHAR2 (100 BYTE),
);
sqlldr seems to require that each record is in a single line and entries are in a fixed order. I can't figure out how to create a control file that maps the values to the appropriate columns.
Do I need to pre-process the file to put it in the format that sqlldr requires? If so, that I might as well just write my own loader.
Has someone else implemented a key-value pair loader?
Thank you,
Alex