-
saving gif pdf etc in oracle db
Hi Friends,
I got this excerpt from ASK TOM, about saving and retreiving objects
to Oracle DB. Does this program be able to save movies too? or a
mp3 music?
Are there other codes/programs shorter or better than these
I am using 10gXe DB, I believe his code is generated from lower
Db versions like 8i coz he is mentioning svrmgrl
and I think 10g has more advanced features
Thanks a lot
==========================================================
Saving and Retrieving .gif .pdf etc. etc
Here is an example that loads a GIF into the datbase and displays it.
You would of course change the mime type and the name of the procedure
from .gif to .pdf. Then you will be able to use HTML like:
Click Me
to retrieve "document 55" for example.
The following code just created a demo table and loads an image into it -- you already
have the pdfs loaded so its not 100% relevant to your problem
create table demo
( id int primary key,
theBlob blob
)
/
create or replace directory my_files as '/export/home/tkyte/public_html';
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/
Now here is the package that can retrieve the pdf (or anything for that matter. Just
keep adding procedures that are named after the file type like .doc, .pdf, .xls and so
on. Some browsers really want the extension in the URL to be "correct")
create or replace package image_get
as
procedure gif( p_id in demo.id%type );
end;
/
create or replace package body image_get
as
procedure gif( p_id in demo.id%type )
is
l_lob blob;
l_amt number default 30;
l_off number default 1;
l_raw raw(4096);
begin
select theBlob into l_lob
from demo
where id = p_id;
-- make sure to change this for your type!
owa_util.mime_header( 'image/gif' );
begin
loop
dbms_lob.read( l_lob, l_amt, l_off, l_raw );
-- it is vital to use htp.PRN to avoid
-- spurious line feeds getting added to your
-- document
htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
l_off := l_off+l_amt;
l_amt := 4096;
end loop;
exception
when no_data_found then
NULL;
end;
end;
end;
/
UTL_RAW might not be installed on your database. It is part of replication. If you
do not have it installed, simply:
o cd $ORACLE_HOME/rdbms/admin
o find the two files with "raw" in their name (eg: ls *raw*)
o using svrmgrl connect as INTERNAL OR SYS -- only these users, no one else can
successfully install UTL_RAW
o run the .sql and then the .plb file
Last edited by jennifer2007; 04-24-2007 at 10:47 PM.
-
Hi Friends,
Just a follow-up related to this post.
We have an old application developed in VB and the database in MSSQL
server. Our developers (not including me) developed our in house applications using VB and MSSQL (i dont know how to use this VB, coz
I am only a diehard fan of Oracle). Our programmers were old in this
company while I am only new. I was hired because they wanted to shift
to oracle db.
Now we migrated our MSSQL database to Oracle DB but the front end is
still VB. The program codes are a lot similar accessing between MSSQL
and Oracle DBs. So not so much revisions.
My problem is with regard to the saving of images/jpeg/gif which was done
before in VB/MSSQL. The programmers adopted the datatype longchar in
MSSQL, and they used the counterpart in Oracle which is CLOB, because
the programmers have so little knowledge about Oracle and its saving
of images. But still they were able to save and retrieve the images using
CLOB since their codes were based/patterned on what they used to do
with MSSQL datatype.
What I want to tell them is to use BLOB because I know it is the right
datatype of storing images data. But their codes wont work on BLOBs,I dont
know what command/code to give them to pass in VB to be able to
save and retrieve images.
If i follow or install the program demonstrated by TOM, can the VB program
used the oracle commad like this :
>>>>> saving image file <<<<<
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/
How do I pass this in VB please?
Thanks
Last edited by jennifer2007; 04-25-2007 at 11:33 PM.
-
Hi Jen,
I used to program with C/C++ but not in VB. But you can probably use this codes from docs actually.
The following example shows usage of OraBlob and OraBfile.
Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob,
OraMyBfile as OraBFile
OraConnection.BeginTrans
set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab order by clip_
id", ORADYN_DEFAULT)
set OraSound1 = OraDyn.Fields("Sound").value
set OraSoundClone = OraSound1
OraParameters.Add "id", 1,ORAPARAM_INPUT
OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT
OraParameters("mybfile").ServerType = ORATYPE_BFILE
OraDatabase.ExecuteSQL ("begin GetBFile(:id, :mybfile ") end")
Set OraMyBFile = OraParameters("mybfile").value
'Go to Next row
OraDyn.MoveNext
OraDyn.Edit
'Lets update OraSound1 data with that from the BFILE
OraSound1.CopyFromBFile OraMyBFile
OraDyn.Update
OraDyn.MoveNext
'Go to Next row
OraDyn.Edit
'Lets update OraSound1 by appending with LOB data from 1st row represenetd by
'OraSoundClone
OraSound1.Append OraSoundClone
OraDyn.Update
OraConnection.CommitTrans
In the preceding example:
OraSound1 -- represents the locator for the current row in the dynaset OraSoundClone -- represents the locator for the 1st row.
A change in the current row (say a OraDyn.MoveNext) means the following:
OraSound1 -- will represent the locator for the 2nd row
OraSoundClone -- will represent the locator in the 1st row. OraSoundClone only refers the locator for the 1st row irrespective of any OraDyn row navigation).
OraMyBFile -- refers to the locator obtained from an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure, either by doing an OraDatabase.ExecuteSQL.
---------------
-
Thanks dear ....do u mean I don't need to install something like the program
demo of TOM? and use only the codes u provided?
-
if its a package then desc it like SQL>DESC DBMS_JOB;
in your case DESC UTL_RAW.
if it exist then you don't need to install.
---------------
-
I got it now :-)
You are confused about UTL_RAW. Since it is located at /rdbms/admin that means it has nothing to do with TOM. This is standard database package and should be installed by catproc.sql
Just in case if it is not, you get the recommendation to install it by hand, finding the scripts at /rdbms/admin and running them
Support to Ray, try describe utl_raw before doing anything
Regards
-
thank you all
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|