Solved

saving images in oracle

Posted on 2002-03-26
10
2,888 Views
Last Modified: 2008-03-03
how do i save an image in oracle?
0
Comment
Question by:ananya
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 6896610
ananya,
   You want the Application Developers Guide to Objects.  Here's a pointer to a section on loading an object from a binary file.  You'll find a lot more examples and info in the rest of the manual.  The site requires registraton, but it's free.

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76940/adl09p13.htm#106789

Good luck!
0
 
LVL 5

Accepted Solution

by:
sora earned 100 total points
ID: 6898544
Ananya -

Here is a step by step explanation with examples, based on a working script that I used:

1. Give the necessary grants:

SQL> connect system/manager
Connected.

SQL> grant create any directory to TEST_USER;

Now login as TEST_USER:

SQL> connect TEST_USER/TEST_USER
Connected.

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 TEST_BLOB_DIR as '/export/home/oracle';

Directory created.

2.Create a table that contains a blob column

SQL> create table blobtab(index_col integer, blob_col blob);

Table created.

SQL>
SQL> desc blobtab

 Name         Type
 --------------------
 INDEX_COL    NUMBER(38)
 BLOB_COL     BLOB


REM insert an empty blob

insert into blobtab(index_col, blob_col)  
values(1, empty_blob());

1 row created.


SQL> REM ****note you cannot select contents from sqlplus****


SQL> select * from blobtab;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

SQL> select count(*) from blobtab;

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

-----------------------bf.sql-----------
----copy this below script to a file called bf.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('TEST_BLOB_DIR', 'image.bmp');  -- 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;
/

-------------------copy the above script to bf.sql-----


SQL> set serveroutput on size 1000000
SQL>  @bf.sql

SQL>
Size of input file: 406006
After loadfromfile
Size of blob: 406006

PL/SQL procedure successfully completed.

As you can see the size matches with my file size (shown below):

-rw-r--r--   1 oracle   oinstall  406006 Mar 27 14:17 sucheta.bmp


Some additional tips (for free):

While loading large files, it is advisable to zip them up using gzip or other tools, depending on which platform you are them and then store them to reduce the following:

  a) Storage space
  b) loading time

Good Luck!


sora
0
 
LVL 5

Expert Comment

by:sora
ID: 6898555
Ananya

Note that loading can be done from OCI and Pro*C as well but needs a bigger investment in time and effort. The PL/SQL way of loading is by far the easiest way

You can also use SQL*Loader to load really large BLOBs

sora
0
 
LVL 9

Expert Comment

by:konektor
ID: 6898770
VERY VERY simply using Oracle Forms procedure Write_Image_File. Read_Image_File allows you display image in forms. Supported formats : bmp, gif, jpg, tiff ... see forms help
0
 
LVL 2

Expert Comment

by:ddandekar
ID: 6906641
As replied by konektor Form 6 is simple tool to load image files into database blob columns.

Create a form with key_col, blob_col.
Give key_value in key_col and after clicking in blob_col read image file from image source.

Click save button to save image in database.
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.

 
LVL 5

Expert Comment

by:sora
ID: 6930445
ananya

if you are done with this thread, please close it


sora
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7063086
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7082285
Recommended disposition:

    Split points between: DrSQL and sora

DanRollins -- EE database cleanup volunteer
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 7083591
Mindphaser,
    I think sora's step-by-step earns the award - solo.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7084512
Thanks for your input DrSQL.
Changing Recommended disposition:

  Accepts sora's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 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

19 Experts available now in Live!

Get 1:1 Help Now