Solved

Image insert into picture column

Posted on 2004-04-12
8
10,105 Views
Last Modified: 2012-05-04
If i like to insert image or picture into the database:
for instance:
i hv table customer(custnum, custname,...................cust_pic)

what will be my data types and length is for image.
and how can i insert it into database;
what is the data tyoe that use for long characters like aroubd 300 words and more?

"BLOB", "CLOB" what r they?
0
Comment
Question by:jay4
8 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 10810999
0
 
LVL 10

Expert Comment

by:Sys_Prog
ID: 10811009
You can use the BOLb or CLOB data types to store binary data (for example an image, a movie clip etc)

These data types are used to store binary data

I guess The insertion would be possible only thru a front end API

I have done this before using Java

Amit
0
 
LVL 47

Accepted Solution

by:
schwertner earned 30 total points
ID: 10811051
This is a solution 9Oracle environment):

SQL> create directory MYDIR as 'C:\MYIMAGE';

Directory created.

SQL> create table special(a number, b varchar2(20), c blob);

Table created.

SQL>
SQL> insert into special values(1,'abcd',empty_blob());

1 row created.

SQL>
SQL> commit;

Commit complete.


create or replace procedure load(id number)
is
dest_lob blob;
src_lob bfile:=bfilename('MYDIR','img.bmp');
amt integer:=sys.dbms_lob.getlength(src_lob);
begin
select c into dest_lob from special
where a=id for update;
dbms_lob.fileopen(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amt);
dbms_lob.fileclose(Src_lob);
commit;
end;
/

SQL> @ blob_insert

Procedure created.

SQL> exec load(1);

PL/SQL procedure successfully completed.

SQL> select a from special;

A




The LONG datatypes are accessible via subprograms from the DBMS_LOB package.
The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs,NCLOBs, BFILEs, and temporary LOBs.



You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs.

For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND



exception:

DECLARE
fil BFILE;
pos INTEGER;
amt BINARY_INTEGER;
buf RAW(40);
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
dbms_lob.open(fil, dbms_lob.lob_readonly);
amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
dbms_lob.read(fil, amt, pos, buf);
dbms_output.put_line('Read F1 past EOF: '||
utl_raw.cast_to_varchar2(buf));
dbms_lob.close(fil);
exception
WHEN no_data_found
THEN
BEGIN
dbms_output.put_line('End of File reached. Closing file');
dbms_lob.fileclose(fil);
-- or dbms_lob.filecloseall if appropriate
END;
END;
/
Statement processed.
End of File reached. Closing file



PURPOSE:
========
How to create LOB objects and differences compared with LONG RAW behavior.
Working with LOB objects in Forms 6.0.


SCOPE & APPLICATION:
====================
This article explains the basic use and advantages of the Oracle 8 LOB
column type recognized in Developer 6.0 and above.


RELATED DOCUMENTS:
==================
Note:66046.1, Note:1038856.6, Bug:804091, BUG:831105


Oracle Developer 6 To Oracle 8 NEW DATA TYPES
=============================================
The new table column TYPE's (constructs):

BLOB - binary data
CLOB - single byte character data, fixed width (8.1 fixed or variable width).
NCLOB - multi-byte character, fixed width (8.1 fixed or variable width)
BFILE - binary file stored to O/S.

Advantages of new column TYPE

1) Differences (Note:66046.1 Hints:  Oracle8 - New Constructs)
2) Multiple LOB's and BFILE columns (one LONG RAW column per table).
3) LOB's support object types.
4) Replication can be used with LOB's.
5) LOB's stored in a way that optimizes space and efficient access and participate in transaction model

of the server
6) BFILE's (external LOB) stored in O.S. files outside of database, so do not participate in transactions
7) LOB maximum size is 4GB, a LONG RAW maximum is 2GB.
 
Example:  
Create the table.
SQL*Plus simple create statement:
 
   Create table mylobdata  
  (refno number,
   myblob1 BLOB,
   myclob1 CLOB,
  mynclob1 NCLOB,
  mybfile1 BFILE);

Manipulate LOB's in SQLPLUS - NO !
1) It is not possible to query back LOB data.
Lets say you need to test/manipulate BLOB without using forms. PL/SQL provides package
called DBMS_LOB for this (see Note:61737.1)

2) Cannot alter type LONG to LOB and visa versa (Note:1038856.6), so do this :-
ALTER TABLE table_name ADD (new_column_name new_datatype);
UPDATE table_name SET new_column_name = old_column_name;

BLOB - IMAGES
=============
BLOB properties :
- Item Type -> Image
- Image format -> GIF, TIF , BMP...etc. Determines NOT incoming image type but storage format in database

!
- Load in from file usinge read_image_file() used as normal to process the image on the file system.

GOTCHAS !
=========
Create all table BLOB columns as outset otherwise you may hit bug:804091. This bug meant
that if you create table with one BLOB column then add another BLOB column at a later date, forms
will save images only to first column.

BLOB - SOUND
============
BLOB properties :
- Item type -> Sound
- sound format -> eg. WAVE
- Load (wav) file from file
- read_sound_file() as usual to process the wav file.

BLOB - OLE
==========
BLOB properties :
- Item Type -> OLE container
- Insert object ...etc. Just the same as with LONG RAW columns.

CLOB - text
===========
CLOB Properties :
- Item Type -> Text Item
Problems :
- Query on CLOB item criteria does not work in form

NCLOB - text
============
NCLOB properties :
Item Type -> Text Item
Problems :
- Cannot insert or query data in form, BUG:831105.
NB: To insert from SQLPLUS :-
INSERT INTO <CLOBTABLE> VALUES
(N'mydata');

BFILE
=====
- The files are stored on the server as BFILE  (binary file) column type.
- The directory alias and file name is held in the BFILE column.
Forms 6 can display the image (say) directly, using the directory alias and filename held in  the BFILE

column.
With Forms 5 you would use dbms_lob() to identify the file and READ_IMAGE_FILE() to display it (Note:66312.1).
Problems:
- You can't load BFILE data directly using Forms 6. So, in SQL Plus do the following :
GRANT CREATE ANY DIRECTORY TO SCOTT;
CREATE OR REPLACE DIRECTORY MYDIR AS 'D:\TEMP2';
CREATE TABLE MY_BFILETABLE
   (REFNO NUMBER, PHOTO BFILE);
INSERT INTO MY_BFILETABLE VALUES (1,BFILENAME('MYDIR','A_PHOTO.BMP'));






How to read image (in rcle environment):

CREATE OR REPLACE PACKAGE BODY myblob AS

FUNCTION get_blob_len
       RETURN NUMBER IS
BEGIN
      RETURN dbms_lob.getlength(my_blob);
END;

PROCEDURE read_blob
              (
              amount          IN OUT NUMBER,
               offset          IN NUMBER,
              buf             IN OUT RAW
              ) IS
BEGIN
      dbms_lob.read(my_blob,amount,offset,buf);
END;

FUNCTION load
              (
              filename IN VARCHAR2
              )
               RETURN INTEGER AS
lobd       BLOB;
fils       BFILE := BFILENAME('MYBLOBS', filename);
amt        INTEGER;
id         INTEGER;
BEGIN
      select myblobs_s.nextval
           into id
           from dual;
      dbms_lob.fileopen(fils, dbms_lob.file_readonly);
      amt:=dbms_lob.getlength(fils);
      insert into myblobs values (id, empty_blob())
               returning theblob into lobd;
      dbms_lob.loadfromfile(lobd, fils, amt);
      commit;
      dbms_lob.fileclose(fils);
     
       return(id);

END;


FUNCTION find
              (
              iFind   IN INTEGER
              )
              RETURN INTEGER is
BEGIN
      select theblob
         into my_blob
         from myblobs
       where id = iFind;
      return (1);
EXCEPTION
      when no_data_found then
              return (-1);
END;

END;
/


Run this as a user who has privelige to create a directory, and access to dbms_lob.
You will be prompted to provide a value for 'blob_dir'.  This is the physical
directory on the server that you will load your sample image files from.

Generally speaking you have to use the DBMS_LOB package:
This package provides general purpose routines for operations
   on Oracle Large OBject (LOBs) datatypes - BLOB, CLOB (read-write)
   and BFILEs (read-only).
   
   Oracle 8.0 SQL supports the definition, creation, deletion, and
   complete updates of LOBs. The main bulk of the LOB operations
   are provided by this package.

----------------------
RULES AND LIMITATIONS
----------------------
   The following rules apply in the specification of functions and
    procedures in this package.

   LENGTH and OFFSET parameters for routines operating on BLOBs and
   BFILEs are to be specified in terms of bytes.
   LENGTH and OFFSET parameters for routines operating on CLOBs
   are to be specified in terms of characters.
   
   A function/procedure will raise an INVALID_ARGVAL exception if the
   the following restrictions are not followed in specifying values
   for parameters (unless otherwise specified):

   1. Only positive, absolute OFFSETs from the beginning of LOB data
      are allowed. Negative offsets from the tail of the LOB are not
      allowed.
   2. Only positive, non-zero values are allowed for the parameters
      that represent size and positional quantities such as AMOUNT,
      OFFSET, NEWLEN, NTH etc.
   3. The value of OFFSET, AMOUNT, NEWLEN, NTH must not exceed the
      value lobmaxsize (which is (4GB-1) in Oracle 8.0) in any DBMS_LOB
      procedure or function.
   4. For CLOBs consisting of fixed-width multi-byte characters, the
      maximum value for these parameters must not exceed
            (lobmaxsize/character_width_in_bytes) characters
      For example, if the CLOB consists of 2-byte characters such as
      JA16SJISFIXED, then the maximum amount value should not exceed
            4294967295/2 = 2147483647 characters

   PL/SQL language specifications stipulate an upper limit of 32767
   bytes (not characters) for RAW and VARCHAR2 parameters used in
   DBMS_LOB routines.
   
   If the value of AMOUNT+OFFSET exceeds 4GB (i.e. lobmaxsize+1) for
   BLOBs and BFILEs, and (lobmaxsize/character_width_in_bytes)+1 for
   CLOBs in calls to update routines - i.e. APPEND, COPY, TRIM, and
   WRITE routines, access exceptions will be raised. Under these input
   conditions, read routines such as READ, COMPARE, INSTR, SUBSTR, will
   read till End of Lob/File is reached.
   For example, for a READ operation on a BLOB or BFILE, if the user
   specifies offset value of 3GB, and an amount value of 2 GB, READ
   will read only ((4GB-1) - 3GB) bytes.

   Functions with NULL or invalid input values for parameters will
   return a NULL. Procedures with NULL values for destination LOB
    parameters will raise exceptions.  

   Operations involving patterns as parameters, such as COMPARE, INSTR,
   and SUBSTR do not support regular expressions or special matching
    characters (such as % in the LIKE operator in SQL) in the PATTERN
    parameter or substrings.

   The End Of LOB condition is indicated by the READ procedure using
   a NO_DATA_FOUND exception. This exception is raised only upon an
   attempt by the user to read beyond the end of the LOB/FILE. The
   READ buffer for the last read will contain 0 bytes.
     
   For consistent LOB updates, the user is responsible for locking
   the row containing the destination LOB before making a call to
   any of the procedures (mutators) that modify LOB data.
   
   For BFILEs, the routines COMPARE, INSTR, READ, SUBSTR, will raise
   exceptions if the file is not already opened using FILEOPEN.

---------
SECURITY
---------

   Privileges are associated with the the caller of the procedures/
   functions in this package as follows:
   If the caller is an anonymous PL/SQL block, the procedures/functions
   are run with the privilege of the current user.
    If the caller is a stored procedure, the procedures/functions are run
   using the privileges of the owner of the stored procedure.

-------------------------
PROCEDURES AND FUNCTIONS
-------------------------
   Procedures and functions are listed here but do not have separate
   notes as there is plenty of existing documentation on the use of
   the PL/SQL DBMS_LOB routines.

PROCEDURE append(dest_lob IN OUT NOCOPY BLOB, src_lob  IN BLOB);
PROCEDURE close(lob_loc IN OUT NOCOPY BLOB);
FUNCTION  compare(lob_1    IN CLOB CHARACTER SET ANY_CS, ... );
PROCEDURE copy(dest_lob    IN OUT NOCOPY BLOB, ... );
PROCEDURE createtemporary(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE erase(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE fileclose(file_loc IN OUT NOCOPY  BFILE);
PROCEDURE filecloseall;
FUNCTION  fileexists(file_loc IN BFILE)
PROCEDURE filegetname(file_loc  IN  BFILE, ... );
FUNCTION  fileisopen(file_loc IN BFILE)
PROCEDURE fileopen(file_loc  IN OUT NOCOPY  BFILE, ... );
PROCEDURE freetemporary(lob_loc IN OUT NOCOPY  BLOB);
FUNCTION  getchunksize(lob_loc IN BLOB) /* Oracle8i only */
FUNCTION  getlength(lob_loc IN BLOB)
FUNCTION  istemporary(lob_loc IN BLOB)
FUNCTION  isopen(lob_loc in blob)
PROCEDURE loadfromfile(dest_lob    IN OUT NOCOPY  BLOB, ... );
PROCEDURE open(lob_loc   IN OUT NOCOPY BLOB, ... );
FUNCTION  instr(lob_loc IN BLOB, ... );
PROCEDURE read(lob_loc IN            BLOB, ... );
FUNCTION  substr(lob_loc IN BLOB, ... );
PROCEDURE trim(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE write(lob_loc IN OUT NOCOPY  BLOB, ... );
PROCEDURE writeappend(lob_loc IN OUT NOCOPY  BLOB, ... );
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10811074
CLOB and BLOB are used to store the Large Objects.

CLOB can store upto 4GB of character data, whereas BLOB can store upto 4GB of Binary Data.

You can use the DBMS_LOB package to make transactions on CLOB or BLOB columns.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jay4
ID: 10811100
So i cannto insert the image file from my pc, do i need to API?

And catchme the link u have provided confused me, i want simple solution how image are inserted into db and what
data type helps them.
and how can i check my picture are stored in DB?
regards
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 10811107
You can use dbms_lob package (avlb in your database) to store the images. CLOB or BLOB helps them .

>>>Check my pictures stored in db?

0
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 20 total points
ID: 10811495
No you don't need an API for inserting the image file into database.

These steps are easy to test, using dbms_lob.loadfromfile and BLOB datatype:

1. Create a folder, '/db01/test' (Unix) or c:\temp if NT. This folder should be on the host where running the  RDBMS. Do not create on the client :

cd /db01
mkdir test

2. Create a table:

SQL> create table test(id integer, blob_col blob);
table created

3. Create directory:

SQL>create or replace directory dir_of_file as '/db01/test/';
Directory created.

4. Create a procedure:

 CREATE procedure file_into_blob(v_id IN varchar2,v_file_name IN varchar2)
 IS
 BEGIN
 DECLARE
 v_bfile bfile := BFILENAME('DIR_OF_FILE', v_file_name); -- note that uppercase
 v_lob blob;
 BEGIN
 insert into test(id,blob_col) values(v_id,empty_blob());
 select blob_col into v_lob from test where id = v_id for update;
 DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
 DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
 DBMS_LOB.CLOSE(v_bfile);
 commit;
 END;
 END;
/

5. Test it:


SQL>  exec file_into_blob(1, 'image_file.jpg');

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
         1

SQL>

-----------

And from Oracle:

This article contains information on how to read the contents of a binary
file into a database BLOB using PL/SQL.  This is also possible with Pro*C
and OCI, however, it involves a greater cost in time and expertise.
 
 
Procedure
---------
 
The following is the only method available to read the contents of a binary
file into a database BLOB using PL/SQL:
 
1.  Create a database directory object to point to the operating system
    directory which contains the file (or files) that you wish to read  
    into a database blob.
 
      SQL> create or replace directory carldir as '/tmp';
 
      Directory created.
 
2.  Create a table that contains a blob column (If one does not already
    exist).
 
      SQL> create table blobtab(index_col integer, blob_col blob);
 
      Table created.
 
3.  Insert something into the blob column.
 
      SQL> insert into blobtab(index_col, blob_col)
         values(1, utl_raw.cast_to_raw('this is only a test'));
 
      1 row created.
 
                   or
 
      SQL> insert into blobtab(index_col, blob_col)
         values(1, empty_blob());
 
      1 row created.
 
      SQL> select count(*) from blobtab
         ;
 
        COUNT(*)
      ----------
               1
 
4.  Run the program.  The program is described below.
 
SQL> @rbfile
Size of input file: 154240
After loadfromfile
Size of blob: 154240
 
PL/SQL procedure successfully completed.
 
------

rbfile.sql:
--
-- A program that demonstrates that an operating system file containing
-- raw data may be read into a database BLOB column.
--
declare
  ablob blob;
  abfile bfile := bfilename('CARLDIR', 'cc');  -- Get a pointer to the file.
  amount integer;
  asize integer;
begin
--
-- Note:  Above, the blobtab table was initialized with one entry.  This
-- is needed because the PL/SQL BLOB locator (ablob) must point to a  
-- specific EXISTING NON-NULL database BLOB.  Also, the select statement  
-- may only return one entry.  If more than one row is returned  
-- (more than one row with index_col set equal to 1), then the script  
-- will fail.
--
  select blob_col into ablob from blobtab where index_col = 1;
  dbms_lob.fileopen(abfile);
  asize := dbms_lob.getlength(abfile);
  dbms_output.put_line('Size of input file: ' || asize);
  dbms_lob.loadfromfile(ablob, abfile, asize);
  dbms_output.put_line('After loadfromfile');
  asize := dbms_lob.getlength(ablob);
  dbms_output.put_line('Size of blob: ' || asize);
  exception
    when others then
      dbms_output.put_line('An exception occurred');
      dbms_output.put_line(sqlcode || sqlerrm);
end;
 
Hope this helps
0
 

Expert Comment

by:anwar_sudan
ID: 11004883
I WANT TO EXECUTE THE PICTURE IN INTERFACE (IMAGE ITEME)
FROM BLOB DATABASE FIELD USING DEVELOPER6I
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now