steps are below..
1) first i have created a "d:/sql_ld_control_file.ctl" file for sql loader. contents are belows (no enter/line break is allowed in control file). here i am inserting four image files in t_image table.
=================================================
"d:/sql_ld_control_file.ctl"
---------------------------------
LOAD DATA
INFILE *
INTO TABLE scott.t_image
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
file_id INTEGER EXTERNAL(5),
fn BOUNDFILLER,
file_name "SUBSTR(:fn,INSTR(:fn,'\\',-1,1)+1,INSTR(:fn,'.')-INSTR(:fn,'\\',-1,1)-1)",
file_data LOBFILE (fn)
TERMINATED BY EOF
)
BEGINDATA
1,c:\image\Blue hills.jpg
2,c:\image\Sunset.jpg
3,c:\image\Water lilies.jpg
4,c:\image\Winter.jpg
------------------------------------
[note: file_name "SUBSTR(:fn,INSTR(:fn,'\\',-1,1)+1,INSTR(:fn,'.')-INSTR(:fn,'\\',-1,1)-1)", will be in one line .]
2) follow the below steps
==================================
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 10 12:28:53 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn scott@
orcl251
Enter password:
Connected.
SQL>
SQL>
SQL> drop table t_image ;
Table dropped.
SQL>
SQL> CREATE TABLE t_image
2 (file_id NUMBER(5),
3 file_name VARCHAR2(30),
4 file_data BLOB)
5 /
Table created.
SQL>
SQL>
SQL>
SQL> HOST SQLLDR scott/tiger@orcl251 CONTROL=d:/sql_ld_control_file.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jul 10 12:34:59 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
Commit point reached - logical record count 4
SQL>
SQL>
SQL> SELECT file_id, file_name, DBMS_LOB.GETLENGTH (file_data) length
2 FROM t_image ;
FILE_ID FILE_NAME LENGTH
---------- ------------------------------ ----------
1 Blue hills 28521
2 Sunset 71189
3 Water lilies 83794
4 Winter 105542
SQL>
SQL>
SQL>
SQL>
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>cd ..
C:\Documents and Settings>cd ..
C:\>
C:\>cd image
C:\image>dir
Volume in drive C has no label.
Volume Serial Number is 04B1-1B54
Directory of C:\image
07/10/2012 12:33 PM
.
07/10/2012 12:33 PM ..
04/14/2008 08:00 PM 28,521 Blue hills.jpg
04/14/2008 08:00 PM 71,189 Sunset.jpg
04/14/2008 08:00 PM 83,794 Water lilies.jpg
04/14/2008 08:00 PM 105,542 Winter.jpg
4 File(s) 289,046 bytes
2 Dir(s) 8,457,371,648 bytes free
C:\image>
C:\image>
C:\image>
C:\image>
C:\image>
C:\image>
Extra steps for Getting name list from command prompt (in windows)
======================================================
C:\image>
C:\image>
C:\image>
C:\image>dir *.jpg
Volume in drive C has no label.
Volume Serial Number is 04B1-1B54
Directory of C:\image
04/14/2008 08:00 PM 28,521 Blue hills.jpg
04/14/2008 08:00 PM 71,189 Sunset.jpg
04/14/2008 08:00 PM 83,794 Water lilies.jpg
04/14/2008 08:00 PM 105,542 Winter.jpg
4 File(s) 289,046 bytes
0 Dir(s) 8,457,371,648 bytes free
C:\image>
C:\image>
C:\image>
C:\image>dir /b *.jpg > image_name_list.txt
C:\image>
C:\image>type image_name_list.txt
Blue hills.jpg
Sunset.jpg
Water lilies.jpg
Winter.jpg
C:\image>
C:\image>
C:\image>
C:\image>
C:\image>dir
Volume in drive C has no label.
Volume Serial Number is 04B1-1B54
Directory of C:\image
07/10/2012 12:41 PM .
07/10/2012 12:41 PM ..
04/14/2008 08:00 PM 28,521 Blue hills.jpg
07/10/2012 12:41 PM 58 image_name_list.txt
04/14/2008 08:00 PM 71,189 Sunset.jpg
04/14/2008 08:00 PM 83,794 Water lilies.jpg
04/14/2008 08:00 PM 105,542 Winter.jpg
5 File(s) 289,104 bytes
2 Dir(s) 8,457,367,552 bytes free
C:\image>
C:\image>
C:\image>
How to insert multimedia or blob via directory see below..
http://halimdba.blogspot.com/2011/01/how-to-insert-multimedia-data-image.html
How to save blob data to disk see below..
http://halimdba.blogspot.com/2012/06/how-to-save-blob-data-to-disk-from.html
