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 Go to next message
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 #558654 is a reply to message #558653] Mon, 25 June 2012 07:16 Go to previous messageGo to next message
Littlefoot
Messages: 21594
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How is "Ramesh Patel" related to "F:\Koala.jpg"?
Re: How to upload photos in oracle database [message #558705 is a reply to message #558654] Tue, 26 June 2012 01:08 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
yeah Littlefoot its sorry yeah actuly its just sample photo i used in yeah F:\Koala.jpg i want to put this photo at ramesh patel employee
Re: How to upload photos in oracle database [message #558728 is a reply to message #558705] Tue, 26 June 2012 02:29 Go to previous messageGo to next message
Littlefoot
Messages: 21594
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I probably don't understand the question.

If file name was "F:\Ramesh_Patel.jpg", then it would be stored in IMAGE_TABLE.FILE_NAME column (instead of "F:\Koala.jpg"). But that's probably not the issue, right? What is it, then? Could you explain it once again, providing some more details this time? Or, simply wait until someone smarter than me answers your question.
Re: How to upload photos in oracle database [message #558737 is a reply to message #558728] Tue, 26 June 2012 04:39 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
ok littlefoot this image is F:\Ramesh_Patel.jpg
out put is

SQL>
SQL> Select file_id,file_name,DBMS_LOB.GETLENGTH(file_data ) Length from Image_table;

   FILE_ID FILE_NAME                          LENGTH
---------- ------------------------------ ----------
         1 F:\Ramesh_Patel.jpg                    780831

but actul i want out put is just like this




SQL> Select file_id,file_name,DBMS_LOB.GETLENGTH(file_data ) Length from Image_table;

   FILE_ID FILE_NAME                          LENGTH
---------- ------------------------------ ----------
         1 Ramesh_Patel                   780831


so what can i

and i have 1500 images so how can i upload all images in single query
all immages r in one folder
Re: How to upload photos in oracle database [message #558740 is a reply to message #558737] Tue, 26 June 2012 05:02 Go to previous messageGo to next message
Littlefoot
Messages: 21594
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
Littlefoot
Messages: 21594
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Barbara
file_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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
Littlefoot
Messages: 21594
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 #559125 is a reply to message #559019] Fri, 29 June 2012 00:17 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply to all

yeah its really helpful for me

but the problem is

i have more than 1500 JPEG images

so how can i upload all images in bulk into database
Re: How to upload photos in oracle database [message #559134 is a reply to message #559125] Fri, 29 June 2012 01:49 Go to previous messageGo to next message
Littlefoot
Messages: 21594
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The same way as you upload a single image.

You wouldn't type 1500 file names into the BEGINDATA section of your control file, of course; a simple way is to store image names into a file and use its contents:
M:\>dir *.jpg
 Volume in drive M is file sharing 1
 Volume Serial Number is 98A9-A7AE

 Directory of M:\

18.06.2012.  07:10            33.295 big_foot.jpg
18.06.2012.  07:10            33.295 little_foot.jpg
18.06.2012.  07:10            33.295 Ramesh_Patel.jpg
               3 File(s)         99.885 bytes
               0 Dir(s)     502.792.192 bytes free

M:\>dir /b *.jpg > my_images.txt

M:\>type my_images.txt
big_foot.jpg
little_foot.jpg
Ramesh_Patel.jpg

M:\>

FILE_ID might be populated with a SQL*Loader's SEQUENCE parameter, for example
...
trailing nullcols
(
 file_id SEQUENCE (MAX, 1),             --> HERE!
 file_name CHAR(30),
 ...
Re: How to upload photos in oracle database [message #559198 is a reply to message #559134] Fri, 29 June 2012 13:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.


Re: How to upload photos in oracle database [message #559602 is a reply to message #559198] Wed, 04 July 2012 14:06 Go to previous messageGo to next message
Amine
Messages: 318
Registered: March 2010
Senior Member

in a multi user environment, adding session_id in DIR_LIST table will it ensure that users are inserting there own images ?

Am listing a directory by returning all files into a string separated with semi columns. and it works. When will it not work ?
Re: How to upload photos in oracle database [message #559604 is a reply to message #559602] Wed, 04 July 2012 16:00 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The data in a global temporary table is only visible to the one that inserts it. Please see the following section of the online documentation for further explanation:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables003.htm#ADMIN11633
Previous Topic: ARGUMENT segment taken more space out of total system space
Next Topic: Data Pump Import - how to identify Dump file's tablespaces
Goto Forum:
  


Current Time: Thu Sep 24 04:16:22 CDT 2020