load data from flatfile into oracle

[zakriyamhd]

hi all,

I am trying to load data from a flatfile .dat file into oracle table.

my concern is, the flatfile has spaces in it and there is no separation between column fields.

sample data from the flatfile is:

8200110021701626305013425 S JEFFERSON ST FREDERICK MD21701628721701 01000000 027000000000AANAD060219 000000 8200110010465191ZZ0000000000000000 0000000000000000 XC0123 060219180617RD1FNFR 00 000 000 000 000 U A 1100001011000010000100C 11000010N030325N0000000AX01 0001 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 PROPERTY OWNER MUST RUN PATH THRU BLACKTOP 21701626305013.

I have the flatfile layout each column name and the lenght of the each field

Now can i use the sqlldr to load into oracle from this flatfile if yes how do i make the flatfile fields separated

flatfile is coming from mainframe
oracle version :10.1.0.4
os:AIX

any help is greatly appreciated.

[slimdave]

Is it fixed width?

[zakriyamhd]

yes i know the sequence of the feilds and its length, i mean i have the layout of it.

like :

first field is :system number : lenght -5: Alphanumeric ...so on.

[slimdave]

What you have there then is a fixed-width file, and SQL*Loader's syntax accomodates that by supplying either the length of the column or the position of the column.

there are examples here: http://download-west.oracle.com/docs...06.htm#1006566

[tamilselvan]

Why can't you use fields terminated by ' '?

Example: t1.ctl looks like

load data
append into table tamil.t1
fields terminated by ' ' trailing nullcols
( fname
  ,lname
)

Tamil

[davey23uk]

because it looks like there are values in there which are separated by space, e.g addresses

[zakriyamhd]

After some hints from people around,

I came to know that we can make use of the UTL_FILE package, So i tried using it and wrote the procedure but it seems iam missing something in here.It is giving me errors while compiling the procedure can anybody help in this.

I am getting error at insert into statement ---line no 210
here is the procedure
1 create or replace procedure sp_test1
2 (hse_file_date date) is
3 v_directory varchar2(100);
4 v_file_name varchar2(40);
5 fhndlo utl_file.file_type;
6 hse_lin varchar2(8192);
7 INVALID_PATH EXCEPTION;
8 INVALID_MODE EXCEPTION;
9 INVALID_FILEHANDLE EXCEPTION;
10 INVALID_OPERATION EXCEPTION;
11 WRITE_ERROR EXCEPTION;
12 INTERNAL_ERROR EXCEPTION;
13 t_SYSTEM VARCHAR2(4 );
14 t_PRIN VARCHAR2(4 );
15 t_HOUSE_KEY NUMBER(14);
16 t_ADDRESS_1 VARCHAR2(26 );
17 t_ADDRESS_2 VARCHAR2(26 );
18 t_CITY VARCHAR2(18 );
19 t_STATE VARCHAR2(2 );
20 t_ZIP_CODE NUMBER(9);
21 t_ZIP_5 NUMBER(5);
22 t_SALES_AREA VARCHAR2(3 );
23 t_SERVICE_AREA VARCHAR2(2 );
24 t_MAP_CODE VARCHAR2(5 );
25 t_MANAGEMENT_AREA VARCHAR2(2 );
26 t_CENSUS_TRACT NUMBER(6);
27 t_BLOCK_GROUP NUMBER(1);
28 t_CLUSTER_CODE VARCHAR2(2 );
29 t_VERTEX_GEOCODE NUMBER(9);
30 t_DWELLING_TYPE VARCHAR2(2 );
31 t_RIGHT_OF_ENTRY VARCHAR2(1 );
32 t_HOUSE_STATUS VARCHAR2(2 );
33 t_HOUSE_STATUS_DATE NUMBER(6);
34 t_LAST_CALL_STATUS VARCHAR2(1 );
35 t_LAST_CALL_DATE NUMBER(6);
36 t_RATE_CENTER VARCHAR2(10 );
37 t_CURRENT_SUBSCRIBER NUMBER(16);
38 t_CURRENT_SUB_STATUS VARCHAR2(2 );
39 t_PREV_SUB_1 NUMBER(16);
40 t_PREV_SUB_1_STATUS VARCHAR2(2 );
41 t_PREV_SUB_2 NUMBER(16);
42 t_PREV_SUB_2_STATUS VARCHAR2(2 );
43 t_CANDADIAN_FORCED_ADDR VARCHAR2(1 );
44 t_BRIDGER_ADDRESS VARCHAR2(8 );
45 t_DELETE_SWITCH VARCHAR2(1 );
|truncated some of the columns due to space
|
88 t_TIME_ZONE VARCHAR2(2 );
89 t_LOB_SEGMENT VARCHAR2(657 );
90 t_LOB_INDICATOR VARCHAR2(1 );
91 t_LOB_SUBTYPE VARCHAR2(2 );
92 t_LOB_PRIN NUMBER(4);
93 t_AGENT NUMBER(4);
94 t_LOB_STATUS VARCHAR2(1 );
95 t_LOB_STATUS_DATE NUMBER(6);
96 t_LOB_CONNECT_CODE VARCHAR2(1 );
97 t_LOB_TAP_STATUS NUMBER(1);
98 t_LOB_PRJ_SVC_DATE NUMBER(6);
99 t_LOB_NODE VARCHAR2(6 );
100 t_LOB_OUTLETS NUMBER(2);
101 t_LOB_HEADEND VARCHAR2(2 );
102 t_LOB_HSD_ROUTER VARCHAR2(10 );
103 t_LOB_TELPH_NIU VARCHAR2(2 );
104 t_LOB_SPEC_INFO VARCHAR2(25 );
105 t_HOUSE_MEMO1_4 VARCHAR2(240 );
106 t_HOUSE_MEMO VARCHAR2(60 );
107 t_VIDEO_HKEY NUMBER(14);
108 BEGIN
109 v_directory:='/u01/deltas';
110 v_file_name:='csg_ho_'||to_char(HSE_FILE_DATE,'mmddyyyy')||'.dat';
111 fhndlo:=UTL_FILE.FOPEN(v_directory,v_file_name,'r',8192);
112 loop
113 begin
114 UTL_FILE.GET_LINE(FHNDLO,hse_lin);
115 t_SYSTEM:=substr(hse_lin,1,4);
116 t_PRIN:=substr(hse_lin,5,4);
117 t_HOUSE_KEY:=substr(hse_lin,9,14);
118 t_ADDRESS_1:=substr(hse_lin,23,26);
119 t_ADDRESS_2:=substr(hse_lin,49,26);
120 t_CITY:= substr(hse_lin,75,18);
121 t_STATE:=substr(hse_lin,93,2);
290,9);

|
| truncated some of the columns due to space
158 t_CUSTOM_FILED6 :=substr(hse_lin,299,9);
159 t_CUSTOM_FILED7 :=substr(hse_lin,308,9);
160 t_CUSTOM_FILED8 :=substr(hse_lin,317,9);
161 t_CUSTOM_FILED9 :=substr(hse_lin,326,9);
162 t_CUSTOM_FILED10 :=substr(hse_lin,335,9);
163 t_CUSTOM_FILED11 :=substr(hse_lin,344,9);
164 t_CUSTOM_FILED12 :=substr(hse_lin,353,9);
165 t_NO_OF_OFFON_PREMISE :=substr(hse_lin,362,2);
166 t_ONOFF_PREMISE_GROUP :=substr(hse_lin,364,68);
167 t_ONOFF_PREMISE_ID :=substr(hse_lin,432,13);
168 t_ONOFF_PREMISE_TYPE :=substr(hse_lin,445,1);
169 t_ONOFF_PREMISE_PORT :=substr(hse_lin,446,3);
170 t_DROP_SEGM :=substr(hse_lin,449,99);
171 t_DROP_TYPE :=substr(hse_lin,548,5);
172 t_DROP_LOCATION :=substr(hse_lin,553,1);
173 t_DROP_LENGTH :=substr(hse_lin,554,5);
174 t_HOOKUP_TYPE :=substr(hse_lin,559,1);
175 t_LINE_TAG1 :=substr(hse_lin,560,9);
176 t_LINE_TAG2 :=substr(hse_lin,569,9);
177 t_LINE_TAG3 :=substr(hse_lin,578,9);
178 t_POLE_NUMBER :=substr(hse_lin,587,9);
179 t_POLE_OWNER :=substr(hse_lin,596,1);
180 t_PEDESTAL :=substr(hse_lin,597,5);
181 t_AMPLIFIER :=substr(hse_lin,562,5);
182 t_CELL :=substr(hse_lin,567,5);
183 t_HUB :=substr(hse_lin,582,5);
184 t_POWER_SUPPLY :=substr(hse_lin,587,5);
185 t_GROUND :=substr(hse_lin,592,5);
186 t_OUTAGE_PRINAGENT :=substr(hse_lin,597,8);
187 t_OUTAGE_PRIN :=substr(hse_lin,605,4);
188 t_OUTAGE_AGENT :=substr(hse_lin,609,4);
189 t_DROP_ID :=substr(hse_lin,613,4);
190 t_TIME_ZONE :=substr(hse_lin,617,2);
191 t_LOB_SEGMENT :=substr(hse_lin,619,657);
192 t_LOB_INDICATOR :=substr(hse_lin,1276,1);
193 t_LOB_SUBTYPE :=substr(hse_lin,1277,2);
194 t_LOB_PRIN :=substr(hse_lin,1279,4);
195 t_AGENT :=substr(hse_lin,1283,4);
196 t_LOB_STATUS :=substr(hse_lin,1287,1 );
197 t_LOB_STATUS_DATE :=substr(hse_lin,1288,6);
198 t_LOB_CONNECT_CODE :=substr(hse_lin,1294,1);
199 t_LOB_TAP_STATUS :=substr(hse_lin,1295,1);
200 t_LOB_PRJ_SVC_DATE :=substr(hse_lin,1296,6);
201 t_LOB_NODE :=substr(hse_lin,1302,6);
202 t_LOB_OUTLETS :=substr(hse_lin,1308,2);
203 t_LOB_HEADEND :=substr(hse_lin,1310,2);
204 t_LOB_HSD_ROUTER :=substr(hse_lin,1312,10);
205 t_LOB_TELPH_NIU :=substr(hse_lin,1322,2);
206 t_LOB_SPEC_INFO :=substr(hse_lin,1324,25);
207 t_HOUSE_MEMO1_4 :=substr(hse_lin,1349,240);
208 t_HOUSE_MEMO :=substr(hse_lin,1589,60);
209 t_VIDEO_HKEY:=substr(hse_lin,1649,14);
210 insert into csg_house_master (PRIN,HOUSE_KEY,ADDRESS_1,ADDRESS_2,CITY,STATE,ZIP_CODE,
211 ZIP_5,SALES_AREA,SERVICE_AREA,MAP_CODE ,MANAGEMENT_AREA ,
212 CENSUS_TRACT,BLOCK_GROUP,
213 CLUSTER_CODE,
214 VERTEX_GEOCODE,
215 DWELLING_TYPE,
216 RIGHT_OF_ENTRY,
217 HOUSE_STATUS,
218 HOUSE_STATUS_DATE,
219 LAST_CALL_STATUS,
220 LAST_CALL_DATE,
|
|truncated some of the columns due to space
|,
249 ONOFF_PREMISE_GROUP,
250 ONOFF_PREMISE_ID,
251 ONOFF_PREMISE_TYPE,
252 ONOFF_PREMISE_PORT,
253 DROP_SEGM,
254 DROP_TYPE,
255 DROP_LOCATION,
256 DROP_LENGTH,
257 HOOKUP_TYPE,
258 LINE_TAG1,
259 LINE_TAG2,
260 LINE_TAG3,
261 POLE_NUMBER,
262 POLE_OWNER,
263 PEDESTAL,
264 AMPLIFIER,
265 CELL,
266 HUB,
267 POWER_SUPPLY,
268 GROUND,
269 OUTAGE_PRINAGENT,
270 OUTAGE_PRIN,
271 OUTAGE_AGENT,
272 DROP_ID,
273 TIME_ZONE,
274 LOB_SEGMENT,
275 LOB_INDICATOR,
276 LOB_SUBTYPE,
277 LOB_PRIN,
278 AGENT,
279 LOB_STATUS,
280 LOB_STATUS_DATE,
281 LOB_CONNECT_CODE,
282 LOB_TAP_STATUS,
283 LOB_PRJ_SVC_DATE,
284 LOB_NODE,
285 LOB_OUTLETS,
286 LOB_HEADEND ,
287 LOB_HSD_ROUTER,
288 LOB_TELPH_NIU,
289 LOB_SPEC_INFO,
290 HOUSE_MEMO1_4,
291 HOUSE_MEMO ,
292 VIDEO_HKEY)
293 VALUES(t_PRIN,t_HOUSE_KEY,t_ADDRESS_1,t_ADDRESS_2,t_CITY,t_STATE,t_ZIP_CODE,
294 t_ZIP_5,t_SALES_AREA,t_SERVICE_AREA,t_MAP_CODE ,t_MANAGEMENT_AREA ,
357 t_LOB_INDICATOR,
358 t_LOB_SUBTYPE,
359 t_LOB_PRIN,
360 t_AGENT,
361 t_LOB_STATUS,
362 t_LOB_STATUS_DATE,
363 t_LOB_CONNECT_CODE,
364 t_LOB_TAP_STATUS,
365 t_LOB_PRJ_SVC_DATE,
366 t_LOB_NODE,
367 t_LOB_OUTLETS,
368 t_LOB_HEADEND ,
369 t_LOB_HSD_ROUTER,
370 t_LOB_TELPH_NIU,
371 t_LOB_SPEC_INFO,
372 t_HOUSE_MEMO1_4,
373 t_HOUSE_MEMO ,
374 t_VIDEO_HKEY);
375 commit;
376 exception when no_data_found then exit;
377 end;
378 end loop;
379 UTL_FILE.FCLOSE(fhndlo);
380 EXCEPTION WHEN INVALID_PATH THEN NULL;
381 WHEN INVALID_MODE THEN NULL;
382 WHEN INVALID_FILEHANDLE THEN NULL;
383 WHEN INVALID_OPERATION THEN NULL;
384 WHEN WRITE_ERROR THEN NULL;
385 WHEN INTERNAL_ERROR THEN NULL;
386 WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20109,hse_lin);
387* END
SQL> /

Warning: Procedure created with compilation errors.

Line # = 210 Column # = 5 Error Text = PL/SQL: SQL Statement ignored
Line # = 293 Column # = 2 Error Text = PL/SQL: ORA-00947: not enough values

[slimdave]

What's the problem with using the more efficient and flexible (hint: external tables) SQL*Loader?

[zakriyamhd]

FIELD NAME LENGTH FORMAT
SYSTEM 4 AN
PRIN 4 AN
HOUSE KEY 14 N
ADDRESS 1 26 AN

AN-alphanumeric N-numeric
Inside flatfile :

8200110010034419MB0000000000000000 0000000000000000 XC0123 060219180556RD1FNFR 00 000 000 000 000 U AKR04338 87044 1100001011000010000100C 11000010N981221D2000000 0101 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 00000000 000000 0000000 00 CARVER APTS---PROPER ADDR IS 201 MADISON ST APT 28 21701000011427.


how do I differentiate the fields from the flatfile for the sqlldr? they don’t have comma, or a pipe and may or may not have spaces after each field
I created the table with the given fieldname and the length from the layout.

[davey23uk]

did you read the docs about how to load a fixed width file?