Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Unanswered: Ingest file with key-value pairs

    I need to ingest a file that has multiple lines per database row and a somewhat random order to the values. Each 'Record' is delimited by a single line containing the value 'REC'. No attribute key begins with 'REC'.
    How do I set up the control file?
    Or do I need to pre-process the file to put it into the standard format?

    Table:
    CREATE TABLE MY_ITEMS
    (
    EXTERNAL_ID NUMBER (9, 0),
    DESCRIPTION VARCHAR2 (140 BYTE),
    NAME VARCHAR2 (100 BYTE),
    ATTRIBUTE_FLAG VARCHAR2 (1 BYTE),
    COUNT NUMBER (9, 0),
    BUILDING VARCHAR2 (130 BYTE),
    EMERGENCY VARCHAR2 (1 BYTE),
    OPTIONAL_FIELD_1 VARCHAR2 (100 BYTE),
    OPTIONAL_FIELD_2 VARCHAR2 (100 BYTE),
    );
    Sample File:
    ID|987532
    DESCRIPTION|First record
    NAME|First
    ATTRIBUTE|Y
    COUNT|71
    BUILDING|The one next to yours
    EMERGENCY|N
    OPTIONAL_FIELD_1|Opt1
    REC
    ID|987688
    NAME|Second
    DESCRIPTION|Second record
    COUNT|79
    BUILDING|The one below yours
    ATTRIBUTE|Y
    EMERGENCY|N
    OPTIONAL_FIELD_1|
    OPTIONAL_FIELD_2|Another opt
    REC

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    yes, but the first "record" does NOT start with "REC"

    I don't think you can use SQLLDR.

    Consider defining an EXTERNAL TABLE which has a single VARCHAR2(4096) column
    You can then write PL/SQL to INSERT the data into requisite static table
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by AlexInSeattle View Post
    I need to ingest . . .
    Ingest?
    Careful you may get heartburn and perhaps diarrhea of the brain.

    A solution may be to create an external table on the source data and code a PL/SQL to load the table using dynamic sql.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •