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
Posting Permissions
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
Forum Rules
Click Here to Expand Forum to Full Width