A Common scenario is to have data file that contains the name of the file to load, instead of having lob data mix with structured data. we can tell sqlldr how to parse the LOB data from its structured data using LOBFILE in sqlldr.
scott@10G> desc T;
Name Null? Type
----------------- -------- -------------
CREATED_BY VARCHAR2(30)
T_DATA CLOB
load data
infile *
truncate
into table t
(
created_by position(48:62),
x filler position(63:80),
t_data lobfile(x) terminated by eof)
begindata
09/15/2009 11:30 AM 544 BUILTIN\Administrators process_3121.log
09/15/2009 12:40 PM 550 BUILTIN\Administrators process_3122.log
09/15/2009 01:23 PM 550 BUILTIN\Administrators process_3123.log
09/15/2009 01:36 PM 550 BUILTIN\Administrators process_3124.log
09/15/2009 01:50 PM 550 BUILTIN\Administrators process_3125.log
09/15/2009 01:53 PM 550 BUILTIN\Administrators process_3126.log
09/15/2009 01:58 PM 550 BUILTIN\Administrators process_3127.log
09/15/2009 01:59 PM 550 BUILTIN\Administrators process_3128.log
09/15/2009 02:47 PM 550 BUILTIN\Administrators process_3129.log
09/15/2009 02:49 PM 550 BUILTIN\Administrators process_3130.log
09/15/2009 02:51 PM 553 BUILTIN\Administrators process_3131.log
09/15/2009 03:00 PM 553 BUILTIN\Administrators process_3132.log
09/15/2009 03:06 PM 553 BUILTIN\Administrators process_3133.log
09/15/2009 03:24 PM 553 BUILTIN\Administrators process_3134.log
09/15/2009 03:30 PM 553 BUILTIN\Administrators process_3135.log
09/15/2009 03:35 PM 553 BUILTIN\Administrators process_3136.log
09/15/2009 03:42 PM 553 BUILTIN\Administrators process_3137.log
09/15/2009 03:49 PM 553 BUILTIN\Administrators process_3138.log
09/15/2009 03:54 PM 553 BUILTIN\Administrators process_3139.log
09/15/2009 04:09 PM 553 BUILTIN\Administrators process_3140.log
09/15/2009 04:35 PM 553 BUILTIN\Administrators process_3141.log
09/15/2009 04:44 PM 553 BUILTIN\Administrators process_3142.log
09/15/2009 04:49 PM 553 BUILTIN\Administrators process_3143.log
09/15/2009 04:49 PM 553 BUILTIN\Administrators process_3144.log
09/15/2009 06:18 PM 553 BUILTIN\Administrators process_3161.log
09/15/2009 06:26 PM 553 BUILTIN\Administrators process_3162.log
09/15/2009 06:34 PM 553 BUILTIN\Administrators process_3163.log
11/18/2009 01:13 PM 67 BUILTIN\Administrators process_3184.log
11/18/2009 01:21 PM 67 BUILTIN\Administrators process_3185.log
11/18/2009 01:43 PM 67 BUILTIN\Administrators process_3186.log
11/18/2009 02:48 PM 67 BUILTIN\Administrators process_3187.log
11/18/2009 03:51 PM 67 BUILTIN\Administrators process_3188.log
11/18/2009 05:01 PM 67 BUILTIN\Administrators process_3189.log
11/18/2009 05:18 PM 67 BUILTIN\Administrators process_3190.log
11/18/2009 05:34 PM 67 BUILTIN\Administrators process_3191.log
11/18/2009 05:42 PM 67 BUILTIN\Administrators process_3192.log
11/18/2009 07:33 PM 67 BUILTIN\Administrators process_3193.log
11/18/2009 08:11 PM 67 BUILTIN\Administrators process_3194.log
11/23/2009 10:31 AM 366 BUILTIN\Administrators process_3204.log
11/23/2009 10:37 AM 469 BUILTIN\Administrators process_3205.log
11/30/2009 04:36 PM 67 BUILTIN\Administrators process_3224.log
12/08/2009 10:25 PM 761 BUILTIN\Administrators process_3245.log
12/11/2009 06:23 PM 67 BUILTIN\Administrators process_3246.log
12/11/2009 06:27 PM 67 BUILTIN\Administrators process_3247.log
12/11/2009 06:41 PM 67 BUILTIN\Administrators process_3248.log
12/11/2009 06:55 PM 67 BUILTIN\Administrators process_3249.log
12/11/2009 06:56 PM 67 BUILTIN\Administrators process_3250.log
12/17/2009 08:06 PM 67 BUILTIN\Administrators process_3264.log
02/11/2010 09:27 PM 67 BUILTIN\Administrators process_3304.log
02/11/2010 09:36 PM 67 BUILTIN\Administrators process_3305.log
02/11/2010 10:48 PM 67 BUILTIN\Administrators process_3306.log
02/12/2010 12:45 PM 67 BUILTIN\Administrators process_3324.log
02/12/2010 12:51 PM 67 BUILTIN\Administrators process_3325.log
02/12/2010 05:40 PM 67 BUILTIN\Administrators process_3326.log
02/16/2010 01:28 PM 67 BUILTIN\Administrators process_3344.log
scott@10G> select created_by,dbms_lob.getlength(t_data) as lob_length from T;
CREATED_BY LOB_LENGTH
------------------------------ ----------
Administrators 550
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 553
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 366
Administrators 469
Administrators 761
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 544
Administrators 550
Administrators 550
Administrators 550
Administrators 550
Administrators 550
Administrators 550
Administrators 550
Administrators 550
Administrators 553
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
Administrators 67
55 rows selected.
No comments:
Post a Comment