SQL*Loader with mixed ASCII and binary data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: SQL*Loader with mixed ASCII and binary data

  1. #1
    Join Date
    Apr 2004
    Location
    Sydney
    Posts
    1

    SQL*Loader with mixed ASCII and binary data

    I am attemting to load mixed ASCII and binary data using SQL*Loader.

    My table (as described in the .SQL file) uses a record type of BLOB for the orig_record field.

    My SQL*Loader control (.ctl) file uses positional descriptions and a RAW datatype for the orig_record BLOB. When run using the data in the data (.dat - here renamed to .bmp in order to satisfy website requirements) file, the data appears to be loaded, but only up to the first '0A' (carriage return). How can I load the whole record? I suspect that SQL*Loader always expects an ASCII data file.

    The data file will normally be of the order of hundreds of thousands of records per day, so a file for each BLOB is out of the question.

    I also nead to extract these fileds once loaded and create a file of records. I would rather not have to use PL/SQL and the DBMS_LOB package. Any suggestions?

    .sql:
    create table DMS_ERROR
    (Hex_Id VARCHAR2(2)
    ,Structure_Code VARCHAR2(5)
    ,Call_Code VARCHAR2(3)
    ,Sensor_Type VARCHAR2(4)
    ,Sensor_Id VARCHAR2(7)
    ,Recording_office_type VARCHAR2(4)
    ,Recording_office_id VARCHAR2(7)
    ,Connect_Date DATE
    ,Timing_Indicator VARCHAR2(5)
    ,Study_Indicator VARCHAR2(7)
    ,Answer_status INTEGER
    ,Service_observed INTEGER
    ,Operator_action INTEGER
    ,Service_feature INTEGER
    ,Orig_Significant_Digits INTEGER
    ,Originating_Number VARCHAR2(11)
    ,Originating_Number_2 VARCHAR2(10)
    ,Originating_Charge_Info VARCHAR2(4)
    ,Domestic_International_Ind INTEGER
    ,Term_Significant_Digits INTEGER
    ,Terminating_Number VARCHAR2(11)
    ,Terminating_Number_2 VARCHAR2(10)
    ,Connect_time DATE
    ,Elapsed_time INTEGER
    ,A80_orig_pcid VARCHAR2(5)
    ,A80_outgoing_pcid VARCHAR2(5)
    ,A80_sent_digits INTEGER
    ,A80_overflow_dialled_digits VARCHAR2(13)
    ,A80_sent_service_digits VARCHAR2(5)
    ,A80_orig_scid VARCHAR2(5)
    ,A80_term_scid VARCHAR2(5)
    ,A80_term_digits_prefix VARCHAR2(11)
    ,M025_Circuit_date_time DATE
    ,M070_Bearer_Cap VARCHAR2(3)
    ,M070_Nwk_Interworking INTEGER
    ,M070_Signalling VARCHAR2(15)
    ,M070_RCI INTEGER
    ,M104_I_trk_grp VARCHAR2(4)
    ,M104_I_trk_mbr VARCHAR2(4)
    ,M104_O_trk_grp VARCHAR2(4)
    ,M104_O_trk_mbr VARCHAR2(4)
    ,ERR_REASON INTEGER
    ,ERR_CYCLE_COUNT INTEGER
    ,Modules VARCHAR2(256)
    ,ORIG_RECORD BLOB
    )
    storage (initial 128M next 50M maxextents unlimited)
    ;


    .ctl:
    OPTIONS (ERRORS=100,SKIP=0,ROWS=1000,BINDSIZE=1000000)
    LOAD DATA
    INFILE 'DMS_ERRORS.txt'
    BADFILE 'DMS_ERRORS.bad'
    DISCARDFILE 'DMS_ERRORS.dsc'
    REPLACE INTO TABLE DMS_ERROR
    (Hex_Id position( 1: 2) CHAR
    ,Structure_Code position( 3: 7) CHAR
    ,Call_Code position( 8: 10) CHAR
    ,Sensor_Type position( 11: 13) CHAR
    ,Sensor_Id position( 14: 20) CHAR
    ,Recording_office_type position( 21: 23) CHAR
    ,Recording_office_id position( 24: 30) CHAR
    ,Connect_Date position( 31: 35) DATE "YMMDD"
    ,Timing_Indicator position( 36: 40) CHAR
    ,Study_Indicator position( 41: 47) CHAR
    ,Answer_status position( 48: 48) INTEGER EXTERNAL
    ,Service_observed position( 49: 49) INTEGER EXTERNAL
    ,Operator_action position( 50: 50) INTEGER EXTERNAL
    ,Service_feature position( 51: 53) INTEGER EXTERNAL
    ,Orig_Significant_Digits position( 54: 56) INTEGER EXTERNAL
    ,Originating_Number position( 57: 67) CHAR
    ,Originating_Number_2 position( 68: 77) CHAR
    ,Originating_Charge_Info position( 78: 81) CHAR
    ,Domestic_International_Ind position( 82: 82) INTEGER EXTERNAL
    ,Term_Significant_Digits position( 83: 85) INTEGER EXTERNAL
    ,Terminating_Number position( 86: 96) CHAR
    ,Terminating_Number_2 position( 97:105) CHAR
    ,Connect_time position(106:112) TIMESTAMP "HH24:MI:SS.FF1"
    ,Elapsed_time position(113:121) INTEGER EXTERNAL
    ,A80_orig_pcid position(122:126) CHAR
    ,A80_outgoing_pcid position(127:131) CHAR
    ,A80_sent_digits position(132:134) INTEGER EXTERNAL
    ,A80_overflow_dialled_digits position(135:147) CHAR
    ,A80_sent_service_digits position(148:152) CHAR
    ,A80_orig_scid position(153:157) CHAR
    ,A80_term_scid position(158:162) CHAR
    ,M025_Circuit_date_time position(163:174) TIMESTAMP "YMMDDHH24:MI:SS.FF1"
    ,M070_Bearer_Cap position(175:177) CHAR
    ,M070_Nwk_Interworking position(178:178) CHAR
    ,M070_Signalling position(179:190) CHAR
    ,M070_RCI position(191:196) INTEGER EXTERNAL
    ,M104_I_trk_grp position(197:200) CHAR
    ,M104_I_trk_mbr position(201:204) CHAR
    ,M104_O_trk_grp position(205:208) CHAR
    ,M104_O_trk_mbr position(209:212) CHAR
    ,ERR_REASON position(213:214) INTEGER EXTERNAL
    ,ERR_CYCLE_COUNT position(215:216) INTEGER EXTERNAL
    ,ORIG_RECORD position(217:368) RAW
    )
    Attached Images Attached Images

Posting Permissions

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


Click Here to Expand Forum to Full Width