Home » RDBMS Server » Server Utilities » How to upload photos in oracle database (oracle 10.2.0.4, windows 7 64 bit)
| How to upload photos in oracle database [message #558653] |
Mon, 25 June 2012 07:13  |
 |
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
|
Senior Member |
|
|
hello friends i have 1500 images in jpeg format
i want to upload this immages into my oracle database
i did this steps using google serach
1) Create the table as follows
Quote:SQL> CREATE TABLE image_table (file_id NUMBER(5),
2 file_name VARCHAR2(30),file_data BLOB);
Table created.
2)
Create the control file as follows in notepad and just give a name control.ctl ....ctl extension is needed
Quote:LOAD DATA
INFILE *
INTO TABLE ron.image_table
REPLACE
FIELDS TERMINATED BY ','
(
file_id INTEGER EXTERNAL(5),
file_name CHAR(30),
file_data LOBFILE (file_name)
TERMINATED BY EOF
)
BEGINDATA
1,F:\Koala.jpg
3)
Run this command on command prompt
F:\oracle\product\10.2.0\db_1\bin>SQLLDR control=E:\control.ctl
Username:system
Password:
SQL*Loader: Release 10.2.0.5.0 - Production on Thu Jun 9 16:20:17 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 1
4)
Now check this image file load in oracle by using this
SQL>
SQL> Select file_id,file_name,DBMS_LOB.GETLENGTH(file_data ) Length from Image_table;
FILE_ID FILE_NAME LENGTH
---------- ------------------------------ ----------
1 F:\Koala.jpg 780831
SQL>
but actuly i want to file_name is my actul employee name like Ramesh Patel so what are the changes i can do into my this control file so i can get this file name as my employee name and using this tool i can upload 1500 images into database easily
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to upload photos in oracle database [message #558740 is a reply to message #558737] |
Tue, 26 June 2012 05:02  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Filename is a "file" name, not "personal" name.
Here's one option: alter a table and add another column. Create a function which will select a name from the file path.
SQL> desc image_table
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_ID NUMBER(5)
FILE_NAME VARCHAR2(30)
FILE_DATA BLOB
ENAME VARCHAR2(30)
SQL> create or replace function f_ename
2 (par_file_name in varchar2)
3 return varchar2
4 is
5 retval varchar2(30);
6 begin
7 retval := substr(par_file_name,
8 instr(par_file_name, '\', -1) + 1,
9 instr(par_file_name, '.', -1) - instr(par_file_name, '\', -1) - 1
10 );
11 return (retval);
12 end;
13 /
Function created.
A control file:LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
trailing nullcols
(
file_id INTEGER EXTERNAL(5),
file_name CHAR(30),
file_data LOBFILE (file_name),
ename "f_ename(:file_name)"
)
BEGINDATA
1,M:\Ramesh_Patel.jpg
Loading session and the result:
SQL> $sqlldr scott/tiger@ora10 control=test3.ctl log=test3.log
SQL*Loader: Release 11.2.0.2.0 - Production on Uto Lip 26 12:02:08 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
SQL> select * from image_table;
FILE_ID FILE_NAME FILE_DATA ENAME
---------- -------------------- -------------------- --------------------
1 M:\Ramesh_Patel.jpg Ramesh_Patel
SQL>
|
|
|
|
| Re: How to upload photos in oracle database [message #558961 is a reply to message #558740] |
Wed, 27 June 2012 13:15 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There is no need to add a column or create a function. Just change the control file to the one below.
LOAD DATA
INFILE *
INTO TABLE ron.image_table
REPLACE
FIELDS TERMINATED BY ','
(
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,F:\Ramesh_Patel.jpg
Please see the demonstration below in which I have used a different schema and directory path, but everything else is the same.
-- test.ctl:
LOAD DATA
INFILE *
INTO TABLE scott.image_table
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:\my_oracle_files\Ramesh_Patel.jpg
-- table, load, and results:
SCOTT@orcl_11gR2> CREATE TABLE image_table
2 (file_id NUMBER(5),
3 file_name VARCHAR2(30),
4 file_data BLOB)
5 /
Table created.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl
SCOTT@orcl_11gR2> SELECT file_id, file_name, DBMS_LOB.GETLENGTH (file_data) length
2 FROM image_table
3 /
FILE_ID FILE_NAME LENGTH
---------- ------------------------------ ----------
1 Ramesh_Patel 8263
1 row selected.
|
|
|
|
| Re: How to upload photos in oracle database [message #558964 is a reply to message #558961] |
Wed, 27 June 2012 14:00 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Barbarafile_name "SUBSTR(:fn,INSTR(:fn,'\\',-1,1)+1,INSTR(:fn,'.')-INSTR(:fn,'\\',-1,1)-1)",
A-ha! I tried *that* too (though, formatted it somewhat more nicely, so that my "file_name" was spread through 3 lines). Although loading session went OK (no errors), FILE_NAME column was left empty. That's why I moved to a function.
Can't test it right now, but will try to remember and do it tomorrow (i.e. put my SUBSTR into one line) and see what happens.
|
|
|
|
| Re: How to upload photos in oracle database [message #558969 is a reply to message #558964] |
Wed, 27 June 2012 14:16 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Littlefoot wrote on Wed, 27 June 2012 12:00
... I tried *that* too (though, formatted it somewhat more nicely, so that my "file_name" was spread through 3 lines). ...
I usually format things more nicely as well. However, in this case, there are length limits on the string that I wanted to make sure I did not exceed, which is also why I used a short name, fn, for the boundfiller.
Littlefoot wrote on Wed, 27 June 2012 12:00
... Although loading session went OK (no errors), FILE_NAME column was left empty. That's why I moved to a function. ...
I bet I can guess what you did (or more accurately didn't do). The \ needs to be escaped within a SQL*Loader control file, but not in a function. If, after you ran your load that produced a blank file_name, if you checked your SQL*Loader log file, you would find that within your string, where you thought you had '\' it only showed '', so you need to use '\\' to escape it.
Quote:
... Can't test it right now, but will try to remember and do it tomorrow (i.e. put my SUBSTR into one line) and see what happens.
When you do, you can just copy and paste what I provided.
|
|
|
|
| Re: How to upload photos in oracle database [message #559019 is a reply to message #558969] |
Thu, 28 June 2012 00:45 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Right! "\" that was not escaped was the cause, not multiple lines. Thank you, Barbara!
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
trailing nullcols
(
file_id INTEGER EXTERNAL(5),
file_name CHAR(30),
file_data LOBFILE (file_name),
ename "substr(:file_name,
instr(:file_name, '\\', -1) + 1,
instr(:file_name, '.', -1) - instr(:file_name, '\\', -1) - 1
)"
)
BEGINDATA
1,M:\Ramesh_Patel.jpg
SQL> $sqlldr scott/tiger@ora10 control=test3.ctl log=test3.log
SQL*Loader: Release 11.2.0.2.0 - Production on Čet Lip 28 07:15:41 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
SQL> select * from image_table;
FILE_ID FILE_NAME FILE_DATA ENAME
---------- ------------------------------ -------------------- --------------------
1 M:\Ramesh_Patel.jpg Ramesh_Patel
SQL> |
|
|
|
|
|
|
|
| Re: How to upload photos in oracle database [message #559198 is a reply to message #559134] |
Fri, 29 June 2012 13:24 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SQL*Loader is not the only way to load image files. If your files are on your server or you can copy or move them there, then you can use DBMS_LOB.LOADFROMFILE. You can use Tom Kyte's get_dir_list procedure to load the names of all of the files in a directory into a table, so that you can loop through them to load them, specifying just jpg files or whatever you want. Please see the demonstration below.
SCOTT@orcl_11gR2> -- get_dir_list procedure by Tom Kyte
SCOTT@orcl_11gR2> -- source: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584
SCOTT@orcl_11gR2> create global temporary table DIR_LIST
2 (filename varchar2(255) )
3 on commit delete rows
4 /
Table created.
SCOTT@orcl_11gR2> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23 }
24 /
Java created.
SCOTT@orcl_11gR2> create or replace
2 procedure get_dir_list( p_directory in varchar2 )
3 as language java
4 name 'DirList.getList( java.lang.String )';
5 /
Procedure created.
SCOTT@orcl_11gR2> -- end of code by Tom Kyte
SCOTT@orcl_11gR2> CREATE TABLE image_table
2 (file_id NUMBER(5),
3 file_name VARCHAR2(30),
4 file_data BLOB)
5 /
Table created.
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE SEQUENCE my_seq
2 /
Sequence created.
SCOTT@orcl_11gR2> DECLARE
2 v_blob BLOB;
3 v_bfile BFILE;
4 BEGIN
5 get_dir_list ('c:\my_oracle_files');
6 FOR r IN
7 (SELECT filename fn
8 FROM dir_list
9 WHERE filename LIKE '%.jpg' OR filename LIKE '%.pdf')
10 LOOP
11 INSERT INTO image_table (file_id, file_name, file_data)
12 VALUES
13 (my_seq.NEXTVAL,
14 SUBSTR(r.fn,INSTR(r.fn,'\',-1,1)+1,INSTR(r.fn,'.')-INSTR(r.fn,'\',-1,1)-1),
15 EMPTY_BLOB())
16 RETURNING file_data INTO v_blob;
17 v_bfile := BFILENAME ('MY_DIR', r.fn);
18 DBMS_LOB.OPEN (v_bfile);
19 DBMS_LOB.LOADFROMFILE (v_blob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
20 DBMS_LOB.CLOSE (v_bfile);
21 END LOOP;
22 END;
23 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SELECT file_id, file_name, DBMS_LOB.GETLENGTH (file_data) length
2 FROM image_table
3 /
FILE_ID FILE_NAME LENGTH
---------- ------------------------------ ----------
1 Banana 222824
2 Ramesh_Patel 8263
2 rows selected.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 19:51:24 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
