Home » SQL & PL/SQL » SQL & PL/SQL » Storing gifs (Oracle 9i)
Storing gifs [message #341562] Tue, 19 August 2008 06:13 Go to next message
deviji
Messages: 23
Registered: November 2007
Junior Member
Hi,

My Database is in need of storing .gif files as company logos. what could be the pros and cons of storing .gif files in database. Please suggest / advice on this.

Thanks,
Best Regards
Viji
Re: Storing gifs [message #341573 is a reply to message #341562] Tue, 19 August 2008 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is neither pros nor cons.
Do it if you have to do it.

Regards
Michel
Re: Storing gifs [message #341587 is a reply to message #341562] Tue, 19 August 2008 07:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I believe this question has been addressed on the ASKTOMHOME site. Seems to me however,

Quote:

Pros: Backup/Recovery, Security, Integrity.

Quote:
GIFs stored in the database will be backed up as part of your normal database backup along with all your other data.

GIFs stored in the database will be protected by the same security as all your other data.

GIFs stored in the database are managed by the same commit/rollback transaction semanitcs as all your other data.

Cons: BLOBS and their headaches.



Good luck, Kevin
Re: Storing gifs [message #341588 is a reply to message #341562] Tue, 19 August 2008 07:46 Go to previous messageGo to next message
rajeshwaranbtech
Messages: 4
Registered: August 2008
Location: Chennai
Junior Member
Here's an example to load the images in the database.

This function return's BFILE Locator Associated with the Binary File's in the Server.

Syntax : BFILENAME( <Directory_name> , <File_name>)


CREATE DIRECTORY media_dir as 'E:\oracle\admin\AFRSQA\udump'

# This will create an Alaisis for the Directory that exists in the Server that holds the Binary file needs to be loaded.

CREATE TABLE IMG_TEST (IMG BLOB)

INSERT INTO IMG_TEST (IMG) VALUES ( BFILENAME('media_dir','test.jpg'));
Re: Storing gifs [message #341590 is a reply to message #341588] Tue, 19 August 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does NOT load the image in the database, this just put a reference to the image in the database WITHOUT the guarantee that the images still exists, is modified or not and so on.

In addition, the following statements are inconsuistent:
CREATE TABLE IMG_TEST (IMG BLOB)
INSERT INTO IMG_TEST (IMG) VALUES ( BFILENAME, 'media_dir','test.jpg'));
A BLOB and a BFILE are completly different.

Regards
Michel

Re: Storing gifs [message #356938 is a reply to message #341562] Mon, 03 November 2008 04:52 Go to previous messageGo to next message
PerGew
Messages: 3
Registered: October 2008
Location: Denmark
Junior Member
I've written a procedure, which should do the job, but I allways gets an error ORA-22288.

The procedure looks like this:
CREATE OR REPLACE PROCEDURE write_blob_to_db (
i_file_dir in varchar2,
i_filename IN VARCHAR2
) AS
f_lob BFILE;
b_lob BLOB;

BEGIN
f_lob := BFILENAME (i_file_dir, i_filename);

DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);

DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));

DBMS_LOB.fileclose (f_lob);

END;
/

I've created a directory FILE_DIR, which points at my local c:\temp, and i have copied tthe file to that directory.

Any suggestions wil be appreciated.
Thanks in advance.

Per
Re: Storing gifs [message #356942 is a reply to message #356938] Mon, 03 November 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-22288: file or LOB operation %s failed\n%s
 *Cause: The operation attempted on the file or LOB failed.
 *Action: See the next error message in the error stack for more detailed
          information.  Also, verify that the file or LOB exists and that
          the necessary privileges are set for the specified operation. If
          the error still persists, report the error to the DBA.

Regards
Michel
Re: Storing gifs [message #356952 is a reply to message #356942] Mon, 03 November 2008 05:53 Go to previous messageGo to next message
PerGew
Messages: 3
Registered: October 2008
Location: Denmark
Junior Member
The error-text says:
The system cannot find the file specified.

But the file is in the directory, and i have access to it.

I've tried placing the file on the server and then make a remote desktop connection to the server, but I get the same error.

Re: Storing gifs [message #356954 is a reply to message #356952] Mon, 03 November 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
and i have access to it.

But does Oracle has access to it?
You don't access the file, this is Oracle.

And of course, the file is on the server, doesn't it?

Regards
Michel
Re: Storing gifs [message #356995 is a reply to message #341562] Mon, 03 November 2008 07:55 Go to previous message
PerGew
Messages: 3
Registered: October 2008
Location: Denmark
Junior Member
Now it have Smile

Thanks for the help.

/Per
Previous Topic: Finding Recordset
Next Topic: ora-01839 : date not valid for month specified
Goto Forum:
  


Current Time: Sat Dec 10 03:14:29 CST 2016

Total time taken to generate the page: 0.14978 seconds