Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

saving images in oracle

Posted on 2002-03-26
10
Medium Priority
?
2,952 Views
Last Modified: 2008-03-03
how do i save an image in oracle?
0
Comment
Question by:ananya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

636 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