Tuesday, July 10, 2012

How to insert image/pdf/multimedia or blob data to oracle database table from client machine without using database directory ?

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


No comments: