Skip to Main Content
  • Questions
  • Displaying PDF files stored in the Database

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, sanjay.

Asked: May 20, 2000 - 4:23 pm UTC

Answered by: Tom Kyte - Last updated: June 15, 2017 - 12:41 am UTC

Category: Database - Version: Intermedia

Viewed 100K+ times! This question is

You Asked

Tom,
How can we display PDF files stored in the database,
using htp.p (OAS PL/SQL cartridge )calls and dbms_lob ?
We are developing a document archival and display app.
Using ctxload, I can upload the files. Now, I would like
to display the file in its native format (PDF) on the
web.
thanks
sanjay


and we said...

Sure, if you have the images in a BLOB, its quite easy. 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:

<a href=/dcd/owa/image.pdf?p_id=55>Click Me</a>

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



and you rated our response

  (303 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

April 04, 2001 - 5:38 pm UTC

Reviewer: A reader from UK


May 24, 2001 - 9:29 am UTC

Reviewer: PK from Raleigh, NC

Excellent subject knowledge.

May 24, 2001 - 3:30 pm UTC

Reviewer: PK from Raleigh, NC

Excellent subject knowledge.

Excellent in providing the guideline

July 04, 2001 - 4:54 am UTC

Reviewer: PraveenKumar Talla from India

There are no words to give any review on the data you are providing to the users. Thanks and very much for the beneficial work that Oracle Corporation is giving to the people who loves the Oracle.

Just in time

February 26, 2002 - 10:34 pm UTC

Reviewer: Dave from Florida

It works!

March 14, 2002 - 1:39 pm UTC

Reviewer: daxu from usa

Tom,
I tried to use the codes you presented here to display the pdf file. It works partly. The pdf file downloaded from database just show bookmark contents on the right side, the main contents of pdf is blank, do you have any idea about this? Thanks,

Tom Kyte

Followup  

March 14, 2002 - 1:48 pm UTC

instead of letting the browser display the pdf, right click the link, save it to disk and make sure its EXACTLY the same as the source document. If its bigger/smaller - then check to make sure the NLS_LANG of the "app server" is the same as the database character set (if not, character set translation could be kicking in and changing the data)

How to install htp

March 15, 2002 - 3:43 am UTC

Reviewer: kiro

Hi how to install package htp? Is it come with OAS?
Thanks

Tom Kyte

Followup  

March 15, 2002 - 1:28 pm UTC

it comes with OAS/iAS and in 816 and up -- its in the database upon install. Before that, installing OAS/iAS will get it.

March 15, 2002 - 10:18 am UTC

Reviewer: daxu

Tom,
Thanks a lot. After we change the NLS on both db server and web app server, the PDF file works perfectly. Thanks again.


How to insert PDF file into database using BLOB without BFILE

March 16, 2002 - 6:29 pm UTC

Reviewer: PRS from USA

Hi Tom,
I have a table defined in oracle as follows.
Create table pdf_test(pdf_id number(10),pdf_file blob);
I am trying to insert from the powerbuilder
application after scanning couple of pages and storing
that pages as PDF files on to my local c:\ drive. I
cannot use BFILE or create directory stuff as my
oracle database resides on UNIX and my PDF file
is on local c:\ drive.

Powerbuilder has a datatype BLOB. I am storing that
PDF files into BLOB using fileread(filepointer,blob)
after opening the file in stream mode. BLOB Variable
pdf_content gets populated properly on powerbuilder
side. Then I issue following insert statement.

insert into pdf_test values(:pdf_id,:pdf_content);

But this gives me an error of sqlca.code = -1.
So the execution is not successful. What am i doing
wrong here? I think i cannot insert straight BLOB
variable from my powerbuilder application.

I would appreciate any insight on this.

Thanks


Tom Kyte

Followup  

March 17, 2002 - 10:31 am UTC

Sorry -- never programmed "powerbuilder" in my life. If their documentation doesn't tell you how -- don't know what to tell you . I don't know what they mean by "blob".

If some PB programmer out there knows, feel free to followup. Otherwise this is a question for Sybase to answer. Sorry.

April 02, 2002 - 4:28 am UTC

Reviewer: Ramachandran S from New Delhi, India

Dear Tom,

Using the the code which u have provided i am in a position to insert the gif image on to a table. But my problem remains how to retrieve the image on a Form 6i (Patch 2) using PL/SQL and display it while running the form. If you can guide me I will be thankful.


Thanking you



Tom Kyte

Followup  

April 02, 2002 - 8:28 am UTC

with forms, you should use read_image_file() to load it into the database. This will convert the image into a format that forms can use.

The Incredible Tom Strikes Again...

June 17, 2002 - 3:17 pm UTC

Reviewer: Benoit Hebert from Quebec City, Canada

I've tried adapting the procedure to display jpg images stored in the lobs, but the result is a broken image link. I know the image makes it into the database just fine, since a Perl script can download it again, and the image is the same as the original (confirmed through visual inspection and checksum). I tried right-clicking the broken link and saving the image, in case it's just a display problem, but the image, while being the same size as the original, is corrupted.

Here's the package body for the procedure. Any idea what the problem might be? Our initial impression was that it might be a mime problem.

Thank you!

CREATE OR REPLACE package body insitu.image_get
AS

procedure jpg (p_id in images.no_image%type)
AS
s_image blob;
l_amt number default 30;
l_off number default 1;
l_raw raw(4096);

BEGIN

SELECT fichier_image INTO s_image FROM images WHERE no_image = p_id;
owa_util.mime_header( 'image/jpeg' );

begin
LOOP
dbms_lob.read(s_image, l_amt, l_off, l_raw);
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;
/
show errors;

Tom Kyte

Followup  

June 17, 2002 - 8:52 pm UTC

Is the character set of the client (the webserver) exactly the same as the database -- if not, since owa does varchar2 -- the character set conversion will corrupt the image.

Why default of 30 for l_amt?

August 20, 2002 - 9:48 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

Tom:

Why does l_amt get assigned a default of 30 in the procedure? Is there any significance to 30? After the 1st iteration through the loop l_amt is assigned 4096... why not start at 4096 instead of 30?

Sorry if I'm being daft (not been the best of days...)

Thanks,

Mark

Tom Kyte

Followup  

August 21, 2002 - 7:25 am UTC

Sorry -- should have put it at 4096.

Why did I do 30? Just to make sure in my test that I iterated at least once, to test the looping.

ORA-22288: file or LOB operation FILEOPEN failed

September 05, 2002 - 3:39 pm UTC

Reviewer: A reader

Tom,
I was changing the create directory statement to
create or replace directory my_files as 'c:/test';
and then running
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', 'oracle_image_map.gif' );
dbms_lob.fileopen( l_bfile );

dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );

dbms_lob.fileclose( l_bfile );
end;

got an error:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 9


Tom Kyte

Followup  

September 05, 2002 - 10:36 pm UTC

Hmm, would appear that the file oracle_image_map.gif does not exist on the SERVER in the c:/test directory (why do people insist on using Unix / on windoze?)


does the file actually exist on the SERVER in that directory.

yes it does exist on the server

September 06, 2002 - 10:38 am UTC

Reviewer: A reader

I have tried to put the file in one of the local drives of the server...and also networking to a drive on the server..and putting a file there...
still getting the same error

8.1.7.3.0 (Enterprise Edition) on Windows 2000 advanced server

Thanks..

Tom Kyte

Followup  

September 06, 2002 - 1:31 pm UTC

sorry -- but I don't actually believe you.


Log onto the server.
in a DOS like window (just text).
do a "dir" for us, show us the file exists.
using sqlplus.exe (character mode), log into sqlplus.
do the create directory.
run the code.
cut and paste the results.


This has always been a case of "file actually truly did not exist", every time I see this -- every time (and I've seen it lots), and plesae use the right slash for this backwards OS -- / is wrong on NT, \ is "right".

Excellent Material Tom !!!

September 06, 2002 - 11:51 am UTC

Reviewer: C Singh from Netherlands

Have a question related with it .. am working on a document imaging system where i need to open more than one file ( in this case .tif) at once in the same window. for example
have a table with two columns as below

select a, b from wblob ;

A B
------ ---
(BLOB) 6
(BLOB) 5
(BLOB) 5


And have changed the code a little bit as below (added another loop)


create or replace package body wtest_get as
procedure tif -- ( p_id in number )
is
l_lob blob;
l_amt number default 30;
l_off number default 1;
l_raw raw(4096);
cursor c1 is select a
from wblob
where b = 5 ; -- p_id;

begin
for v1 in c1
loop
owa_util.mime_header( 'image/tiff' );
begin
loop
dbms_lob.read( v1.a , l_amt, l_off, l_raw );

htp.print( 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 loop ;
end;

BUT the problem is (as from the table it can be seen my cursor will return 2 rows for b = 5 ) it still opens only one file and not 2 as i would like. what am i doin wrong OR how can we open more than one file at the same time.



Tom Kyte

Followup  

September 06, 2002 - 1:37 pm UTC

You would have to look up on the internet what the exact format of a multi-part document is and write out that specific formatted document using its protocol. It'll look like a multi-part email if you've ever seen one of those.

Proabably not a good thing in PLSQL -- you really don't have the degree of control you really need over the document format to do that.

Not the answer you wanted to hear -- but I don't think there is a really "good" one here.

"sorry -- but I don't actually believe you."

September 06, 2002 - 2:30 pm UTC

Reviewer: A reader

You were right...I was day dreaming I guess...I was putting file on another server...
Here are some issues:

1.We are about to put lot of images into the database...because there is one particular software..that can not pull the images from CD's...
Anyway...since they have to be on the server for us to move them into the database....I was trying to map a network drive to the server....and then run your procedure to move them into the database....it worked O.K. on the local drives but the network drive gave an error:
ORA-22288: file or LOB operation FILEOPEN failed
The data is invalid.
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 9

2. Is there a way to avoid this whole situation of putting the images into the database....
I mean the database is like 3 Gigs only...and these images are going to beef it up so much...
So, can we move the images into TEMP tables through PL/SQL
and return them as a record set to the front end...
or is this a "Dream on Son..." situation...


Thanks...

Tom Kyte

Followup  

September 06, 2002 - 2:37 pm UTC

we can use SQLLDR to load them over the network.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:624423639385 <code>

all you'll need is a file that is the list of file names to load and a control file....

Is it posible ??

October 10, 2002 - 7:07 am UTC

Reviewer: C Singh from Netherlands

Tom,
is it posible to store/insert more than one file in a blob column (same row) ???


Tom Kyte

Followup  

October 10, 2002 - 7:18 am UTC

It seems it would be more sensible to have a blob column per file but you could if you wanted to just keep appending to a single blob.

It would be upto you to remember where one file starts and the other ends -- it would just be a blob to us

SQLLDR

October 10, 2002 - 8:18 am UTC

Reviewer: ashraf from kuwait

i have pdf files and that files are in some directories
you link is tell that i can load gif or pdf file using sqlldr but how to mention the derictory in sqlldr

Tom Kyte

Followup  

October 11, 2002 - 7:09 pm UTC

Just like you might think:

BEGINDATA
7782,CLARK,MANAGER,7839,2572.50,,10,/home/tkyte/test/allbytes.dat

(from that same example, but using a directory with the filename)

Is it posible ??

October 10, 2002 - 8:30 am UTC

Reviewer: C Singh from Netherlands

Thankyou Tom for the quick reply.

but
1) how can i insert more than one file in a blob column
2) sorry, but what do you mean by "It would be upto you to remember where one file starts and the other ends -- it
would just be a blob to us "

3) the question actually is related to the one i asked before (in this thread) . what if can store more than one file in a BLOB column and then display that using the package image_get given above ???



Tom Kyte

Followup  

October 11, 2002 - 7:14 pm UTC

1) you would
a) open file, read file, use dbms_lob.writeAppend to add file to blob
b) close that file
c) goto A and do it with another file


2) sorry, but I don't know what I could possibly say further. If you loaded 3 files into a blob -- WE (oracle) would have no way to tell you "file 1 starts at byte 1 and goes to byte N, file 2 starts at byte N+1 and goes to M and so on". YOU would have to "remember" that.

3) that doesn't compute. the browser expects a file back from a link, not a set of files back. The browser wouldn't be capable of dealing with it nicely. You could use the MIME multi-part document encoding scheme to stream back more then one file but it probably won't do what you want anyway.

October 10, 2002 - 9:54 am UTC

Reviewer: Sagi from India

Hi Tom!

My DAD name is sagi.

How do i execute it now with passing the value 1 as input parameter. I have not used modplsql much.

If I execute it as below:

</code> http://localhost/pls/sagi/image_get.gif

it gives me the below ERROR:

image_get.gif: SIGNATURE (parameter names) MISMATCH
VARIABLES IN FORM NOT IN PROCEDURE: 
NON-DEFAULT VARIABLES IN PROCEDURE NOT IN FORM: P_ID

  DAD name: rk_dad
  PROCEDURE  : image_get.gif
  URL        : 
http://localhost:80/pls/RK_DAD/image_get.gif <code>
PARAMETERS :
============

Regards,
Sagi

Tom Kyte

Followup  

October 11, 2002 - 7:23 pm UTC

</code> http://localhost/pls/sagi/image_get.gif?p_id=1 <code>

(you know, sort of like my example

<a href=/dcd/owa/image.pdf?p_id=55>Click Me</a>

in the answer)

October 10, 2002 - 9:55 am UTC

Reviewer: A reader

Sorry Tom,

I mistakely gave the dad name as sagi. It was RK_DAD....


Regards,
Sagi

Reading Blob details

December 04, 2002 - 2:21 am UTC

Reviewer: Daya from India

Hi Tom!
 IT is very good article regarding Blob.
  I have a problem in reading the Blob data from the database. 
After storing the data to database, i have a procedure to read 
the data from database as given below:
  We are using Database version 8.1.7.

create or replace procedure read_lob( p_id in demo.id%type )
is
    l_lob    blob;
    l_amt    number default 30;
    l_off   number default 1;
    l_raw   raw(32767);
begin
    select theBlob into l_lob
        from demo
         where id = p_id for update;
        begin
           loop
              dbms_lob.read( l_lob, l_amt, l_off, l_raw );
              dbms_output.put_line( utl_raw.cast_to_varchar2( l_raw ) );
              l_off := l_off+l_amt;
              l_amt := 4096;
           end loop;
        exception
           when no_data_found then
             dbms_output.put_line(sqlerrm);
        end;
end;

When i try to execute this procedure, i get a error like:

SQL> exec read_lob(2);

PL/SQL procedure successfully completed.

SQL> exec read_lob(2);
spool e:\woc\woctabledel.lst
BEGIN read_lob(2); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_OUTPUT", line 57
ORA-06512: at "WOC.READ_LOB", line 14
ORA-06512: at line 1


  What could be the problem? For another file which has got no 
space or null values, the same procedure works fine.
Thanks
Daya 

Tom Kyte

Followup  

December 04, 2002 - 8:21 am UTC

I do not understand what I'm looking at:

SQL> exec read_lob(2);

PL/SQL procedure successfully completed.        <<<==== it ran OK

SQL> exec read_lob(2);
spool e:\woc\woctabledel.lst                    <<<==== what is this, it cannot
                                                        be there, not SQLPlus
                                                        syntax
BEGIN read_lob(2); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error       <<<=== this is probably a result
                                                       of way overflowing the 
                                                       dbms_output buffer.
ORA-06512: at "SYS.DBMS_OUTPUT", line 57
ORA-06512: at "WOC.READ_LOB", line 14
ORA-06512: at line 1


dbms_output.put_line has a limit of 255 characters/line.  the second time through the loop, you use 4k, not 30bytes -- hence you are overflowing it.


ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_output.put_line( rpad('*',4000,'*') );
BEGIN dbms_output.put_line( rpad('*',4000,'*') ); END;

*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 1


use 255 max. 

Problems with bigger (> 3 MB) PDF-Files

December 11, 2002 - 9:05 am UTC

Reviewer: Michael from Germany

Hello Tom!

I tried your procedures to display PDF-files.
They work fine, but when i try to display PDF-files which are bigger than 3 MB (or so) then i get an error message.

The details:

create table mypdfs (
id number(38) primary key,
theblob blob not null);

create or replace package getpdfs
as
procedure pdf(p_id in mypdfs.id%type);
end;
/

create or replace package body getpdfs
as
procedure pdf(p_id in mypdfs.id%type)
is
l_lob blob;
l_amt number := 32767;
l_off number := 1;
l_raw raw(32767);
begin
select theblob into l_lob
from mypdfs where id = p_id;
owa_util.mime_header('application/pdf');
begin
loop
dbms_lob.read(l_lob, l_amt, l_off, l_raw);
htp.prn(utl_raw.cast_to_varchar2(l_raw));
l_off := l_off + l_amt;
end loop;
exception
when no_data_found then
null;
end;
end;
end;
/

Then i loaded with a little java some PDF-files into this table. The result:

select id, dbms_lob.getlength(theblob) from mypdfs;

ID DBMS_LOB.GETLENGTH(THEBLOB)
---------- ---------------------------
1 47985
2 5069381
3 483618
4 1192106
5 2584836
6 3974764

Then i tried in my browser the url:

</code> http://mike:mike@localhost/pls/ora92dbdad/mike.getpdfs.pdf?p_id=X <code>

With X = 1, 3, 4 and 5 it works perfect. But with X= 2 and 6, i get the error (in MS IE and Netscape 4.7x):

Not Found
The requested URL /pls/ora92dbdad/mike.getpdfs.pdf was not found on this server.

(Even wget can download 1, 3, 4, and 5, but not 2, 6 - it too gives a "not found"-error message).

Do you have any idea what i am doing wrong?

Tom Kyte

Followup  

December 11, 2002 - 9:28 am UTC

when a procedure "fails" with an unhandled execption, by default with mod_plsql, it returns a 404-not found instead of "error".

Check your web server logs and mod_plsql logs in particular.

Odds are -- since the document is in fact "cached" in ram here -- you are running out of process memory -- you would be looking for an ora-4030

You are right!

December 11, 2002 - 9:45 am UTC

Reviewer: Michael from Germany

Thanks Tom, you are right ... and fast ;-)
In the logs from Apache i found the error about a too small large pool (only 4 MB).
After restart with 8 MB large pool everything work perfect!
Thanx!!!

Tom Kyte

Followup  

December 11, 2002 - 9:58 am UTC

ok, that means you are using MTS (shared server).

that means the UGA for all sessions will be in the SGA.

8mb sounds small unless you have a tiny number of connections -- so, be aware of that. This is where the session states will be stored.

In my experience, using MTS with a connection pool (ala mod_plsql) is like buffering a buffer -- eg: not something i would do.

You are right again

December 11, 2002 - 10:46 am UTC

Reviewer: Michael from Germany

Yes, the DB is in MTS and mod_plsql connects over an MTS-connection - and so i had a problem with files which are bigger than my large pool.
(Even after i doubled the large pool from 4 to 8 MB, i had a problem with files > 10 MB).

So i set up a new DAD which connects explicitly to dedicated server and it seems to work now with all (small and big) files.



Another (standard) method

December 13, 2002 - 3:21 am UTC

Reviewer: Michael from Germany

Hello all!

I found the following solution - in the oracle documentation! - to download a blob to the browser:

(My blobs are in a table called "files" in the column "content" and the mime type of the content is store in another table called "mimetypes" column "name". Master-Detail-Relationship.)

create or replace procedure download(p_file_id files.id%type)
as
l_lob files.content%type; l_mime mimetypes.name%type;
begin
select f.content, m.name
into l_lob, l_mime
from files f, mimetypes m
where f.id = p_file_id
and f.mimetype_id = m.id;
owa_util.mime_header(l_mime, false);
owa_util.http_header_close;
wpg_docload.download_file(l_lob);
end;

This worked on my 9i Rel. 2 DB.
I think this is the best way to do this - and it's already there in the DB!
So, an hour invested in reading the docus is a well invested hour.

Why use LOB's in the first place ??!

January 02, 2003 - 3:53 am UTC

Reviewer: Santhosh from On top of the world !

Hello Tom,

I have gone thru the concept of using LOB's in Oracle. But, I'm still unclear as to WHAT IS THE NEED TO USE LOB's in the first place ?

LOB's store picture / gif images in the database. But, to ask u, can't I simply refer the "physical" file from the front-end instead of storing it in the database, & then retrieving it from the db ?

Why use LOB's, when I can do the same operation of storing files / pictures in the OS directory itself ??

Pls clarify.

Warm Regards,

Santhosh


Tom Kyte

Followup  

January 02, 2003 - 7:37 am UTC

are the images important to you?

might you want to back them up?
recover them?
even to a specific point in time, you know -- like your relational data.

Do you want to export your servers file system?
or maybe it is easier to put everything you own into a DATABASE and serve it from there?

what happens when someone says "hey, what are all of these silly files doing here, let me erase them"

what happens when you try to put 10,000 images into a single directory (try it sometime). So now you need to come up with some algorithm to spread files out over many directories, many file systems, etc.

what happens when you try to put 1,000,000 images into a single table? same thing that happens with 1, or 10,000,000.




But...

January 03, 2003 - 12:05 am UTC

Reviewer: Santhosh from Again on top of the world :-)

Hi Tom,

You said right - to handle around 10,000 images in a folder is cumbersome & quite difficult, BUT if the user has limited images to display, will the OS file system be better ?
Also, we can give perfect rights to the folder / files that no one can meddle with it....

Also, to use so many LOB functions need much more technical know-how, and require more efforts from the developers... right ?

Santhosh

Tom Kyte

Followup  

January 03, 2003 - 8:07 am UTC

I don't think so -- but you seem to want to use it so go for it.

If your developers cannot figure out the LOB stuff -- well, keep them away from the database then please! It is not rocket science.

Go for giving "perfect rights". Manage access control here, there, everywhere. Me, I like backing up my database and knowing "i got it all". I might PUBLISH my images from the database to the file system (eg; the images you see on this page) however, the IMAGES exist primarily in the database. If some knucklehead wipes them out on the file system or the file system gets out of sync -- republish them. PRoblem solved. I use the database to -- well you know -- manage data.

Thank you Tom!

February 20, 2003 - 1:56 pm UTC

Reviewer: Joyce Fowler from Belleville, IL USA (across the river from St. Louis, MO)

My boss (for some unknown reason) decided to go with just Apache as a web server and not the full blown iAS, therefore, so sad...no dad. I am about to reinstall iAS and then, I expect, I will be able to call through HTML a PL/SQL procedure to display an image stored as a BLOB in our database (we're actually using XML but I figured if I could get it to display in HTML..). Also, your answer to Daya when he said he got the error 'PL/SQL: numeric or value error' when executing a PL/SQL procedure to read a BLOB was also helpful since I have several PL/SQL procedures to do this and got the same error - didn't realize I was overflowing the buffer. You certainly seem very knowledgeable!!!!!!!!!!!!! Also enjoy your writing style in the newsletter. If you're ever in the St. Louis area see me - I owe you a dinner. :-)

Tom Kyte

Followup  

February 20, 2003 - 7:07 pm UTC

St Louis -- I was fogged out of and then stuck in St Louis just last friday -- didn't think I was going to make it back east before the snow starting falling... Finally made it out of there though late friday.

(you will find the mod_plqsl is just an apache module -- doesn't need "full blown" ias. you have it in your install already I bet. Look in $ORACLE_HOME/Apache.

can u give a procedure to store pdf file in database

February 20, 2003 - 11:23 pm UTC

Reviewer: Nikunj from India

hi tom,

can u give me procedure to store pdf file in database from local machine ?

Thanks,


Tom Kyte

Followup  

February 21, 2003 - 9:26 am UTC

Nope.

think about it. what is your local machine, how does it communicate with the server, what tools do you use, are you client server, 3 tier, are you looking to occasionally upload a document or to do a bulk load, etc etc etc etc


search for

load lobs


on this site for many different alternatives -- there is less said in this request then is actually "said".

Very useful!!. Can I show the image with scaled down/up size on the web?

February 25, 2003 - 11:55 pm UTC

Reviewer: Prince from CA, USA

Tom,

When displaying the image on the web browser, how do I reduce the dimension of the image retrieved from the database?

I am using java to retrieve the image from the DB. But when displaying, I would like to scale down/up the image size. Is there a way to do this in java/html without storing the file in the web server?

Your help is much appreciated.


Tom Kyte

Followup  

February 26, 2003 - 8:42 am UTC

<img src=foobar height=xx width=nn>

in the html. pretty standard method.

I guess, I wasn't clear when explaining

February 26, 2003 - 5:09 pm UTC

Reviewer: Prince

I want to retrieve the blob image from the DB and show them on the web page, without storing in any temporary file, before displaying.

This is the pseudocode.

PreparedStatement pstmt = conn.prepareStatement(sql);

//get the result from database
ResultSet rs = pstmt.executeQuery();

if (rs!=null && rs.next())
{
Blob l_Blob = rs.getBlob("B_COL");
response.setContentType("image/jpeg");
byte[] ba = l_Blob.getBytes(1, (int)l_Blob.length());
response.getOutputStream().write(ba);
response.getOutputStream().flush(); }
// close your result set,


Tom Kyte

Followup  

February 27, 2003 - 7:17 am UTC

ummm -- guess I'm totally missing your point.

where does the temporary file come into play?????


you have to create a HTML file that'll have an <img src = > tag in it that will tell the browser "hey, grab this url to get a image from the server" which in turn runs your code.

still having problems

February 26, 2003 - 10:35 pm UTC

Reviewer: Gabriel Ilarda from Aus

Hi Tom,

I've used the code you provided in your above answer about 3 months ago taken from your expert one-on-one book. It worked perfectly fine and I even used the code to view excel files. I revisted my code just recently and it no longer works. When it tries to open the excel file it says that the file is in an unrecognizable format, and if I open it, it displays the raw data as jumbled text. The only thing i know that has changed in the last 2-3 months is that our iAS was upgraded from 9iAS V1.0.2.2 to 9iAS V1.0.2.2.2. I checked with my DBA if the DB character set and web server character set were changed, and they say, nope they are still both the same. I have even done the 'save as' test and the bytes loaded and received are exactly the same. I've pretty much hit a dead end now and not sure what to do. I've written a TAR to metalink and i won't make a comment of how long it is taking them just to figure out my problem before they have even started coming up with a solution. if you could suggest anything that might help, please let me know. actually another thing i found odd that i've only had to include now not 3 months ago, is in my loading procedure, i had to add a commit statement. if the commit statement isn't there and i load the file and try to view it through the browser, my select statement fails as it can not find the row. once i do a commit, it finds the row and trys to open the file but obviously i can't read it because of the unrecognizable format error. i never had the commit statement in my loading procedure previously when this was all working 3 months ago.

Thanks

Tom Kyte

Followup  

February 27, 2003 - 7:31 am UTC

download the file, do a binary compare with original source

if they are the same size but the bytes differ a bit -- then the NLS_LANGS are in fact different.

search for wpg_docload on this site for an alternative way to download (interal to ias) as well

Insert PDF file into database using BLOB - IN Power Builder

February 27, 2003 - 7:37 am UTC

Reviewer: Riyaz from Chennai, India

Hats off to Tom for excellent & fantabulous guidance to oracle users across the world.
I just came across this query, only couple of days back.

Suggestion for doing the storage and retrieval of BLOB (oracle database) from Power Builder.


Try out the following script in update and retrieve buttons (user defined) in one window with 2 ole controls.

declaration
-----------

lb_object = ole_1.objectdata

for update
----------

updateblob demo set theblob = :lb_object
where id=1;
//messagebox('',sqlca.sqlcode)
commit using sqlca;

for Retrieve
------------
selectblob theblob into :ole_blob from demo
where id= 2 ;
ole_2.objectdata = ole_blob

Hope it will be useful.



wpg_docload.download_file works!!!

March 03, 2003 - 8:37 pm UTC

Reviewer: Gabriel Ilarda from Australia

Tom,

I did the binary compare you asked me too and the bytes compared exactly the same so I don't think it is an NLS_LANG problem, but I did try replacing the whole loop algorithm in your procedure, which uses DBMS.READ to retrieve the lob in chunks, with just the wpg_docload.download_file procedure, as suggested by you and also some other guy on this page, and amazingly it all worked fine. the lob is retrieved and the browser reads it as an excel file without saying its in an unrecognizable format. i could ask why it worked this way and not the other way but i'm quite happy just knowing thats its working fine again. thanks for your help.

How to display the gife files?

April 14, 2003 - 9:04 am UTC

Reviewer: junior from VA USA

Hi, Tom:

I followed your example to create procedure and I am able to store the gif files in Database now. Only different is I set MYFILES directory as 'C:\TEMP';

Question is I do not understand how to display the gif file. I installed 9iAS already.
You told us create a html and have a link like follow:
</code> http://localhost/pls/sagi/image_get.gif?p_id=1 <code>
What is the acture path for the link (can you give me an example) and should I put my gif files or my html file to the directory? Thanks.


Tom Kyte

Followup  

April 14, 2003 - 10:15 am UTC

read this page from top to bottom. The answer is there already.

You need to setup and configure mod_plsql.
sagi in this exmaple is the DAD
image_get.gif is the package.
p_id=1 is the primary key value for this example.

Using dbms_lob under Oracle Forms

July 02, 2003 - 11:12 am UTC

Reviewer: MMatsuya from Brasil

Hi Tom,

I would like to use this Oracle package (dbms_lob) to display .rtf files under Oracle forms. I'm using Oracle8i under Win200 Server.



Use BLOB

August 13, 2003 - 10:45 am UTC

Reviewer: Hector Ulloa Ligarius from Santiago of Chile

TOM , YOU ARE THE BEST !!!!!

THANK YOU

Upload from Database (blob object) to disk

August 29, 2003 - 4:12 pm UTC

Reviewer: Hector tito1973 Ulloa from Santiago of Chile

Hi Tom :

I am using the WebUtil beta (great!!) , and i store in the dataBase a blob object , but i like upload to disk ..

How?

Thank you very much..

PD: You are the best ....y sabes muchísimo de Oracle
PPD : Sorry my english

Tom Kyte

Followup  

August 29, 2003 - 6:10 pm UTC

no idea what "webutil" is. sorry.

performance??

August 29, 2003 - 6:44 pm UTC

Reviewer: Prasad from San Francisco,CA

Tom,

Could you please tell me which will be better .

1. store the pdf file in O/S and write some java beans for displaying and retrieving the pdf
or
2. store as BLOB in database itsel .

Thanks
Prasad

Tom Kyte

Followup  

August 30, 2003 - 10:32 am UTC



depends if you want

o data integrity
o security
o backup
o RECOVERY
o ease of use and access

if you don't want those, use the file system.

Why Java, when it's so much easier with PL/SQL?!

August 30, 2003 - 6:52 am UTC

Reviewer: Jens from Deutschland, Niedersachsen

> Could you please tell me which will be better .
> 1. store the pdf file in O/S and write some java beans
> for displaying and retrieving the pdf
> or
> 2. store as BLOB in database itsel .

Do you really think, that PL/SQL-Oracle-Guru Tom would prefer the OS/Java way?

Look at the review from January 02, 2003. There you could find the answer.

Bye,

Jens




Tom Kyte

Followup  

August 30, 2003 - 10:53 am UTC



;)

Store File in the BBDD

September 04, 2003 - 4:04 pm UTC

Reviewer: Hector Gabriel Ulloa Ligarius from Santiago of Chile

>Hi Tom :
>
> I am using the WebUtil beta (great!!) , and i store in >the dataBase a blob
>object , but i like upload to disk ..
>
> How?

>Thank you very much..

>PD: You are the best ....y sabes muchísimo de Oracle
>PPD : Sorry my english


>Followup:
>no idea what "webutil" is. sorry.

Hi Tom..

Webutil is a tools created by Duncan Mills and Frank Nimphius and it serves for Forms9i , it work into the client..

</code> http://otn.oracle.com/products/forms/htdocs/webutil/webutil.htm <code>


Example :

TEXT_IO , is a command in the server

But in the Web (Forms9i) , TEXT_IO, not used because write the files in the server , it should write in the client

Oracle Team Developers create the Webutil package for architecture web.

I used the WebUtil Beta..and store the data in the BBDD from to disk .

but i like upload to disk ..

How?

Thank Tom

PD : Sorry my english

Tom Kyte

Followup  

September 05, 2003 - 3:23 pm UTC

I guess you would have to ask Duncan or Frank this question? I don't know a thing about this.

Store File in the BBDD

September 08, 2003 - 5:46 pm UTC

Reviewer: Hector Gabriel Ulloa Ligarius from Santiago of Chile

But you know PL/SQL, i like know how upload files from database to disk , only this...

You help me ???

Tom Kyte

Followup  

September 08, 2003 - 5:57 pm UTC

you can use utl_file to write files to disk from plsql after they are uploaded into the database (where they RIGHTLY belong, in the database)

i don't know what else to tell you as I do not use forms at all myself and this is a forms utility here. utl_file is fully documented in the supplied packages guide.

a java stored procedure can do it was well

Store Files in the DDBB

September 08, 2003 - 6:13 pm UTC

Reviewer: Hector

Well..

You don't understand me

Ok...

Thanks

file permissions for my_files

September 09, 2003 - 11:08 am UTC

Reviewer: Sandeep from German

Oracle OS: HP Unix
Oracle Version: 8.1.7.4
Application Server (IIS) OS: Windows 2000 Server

Scenerio: We have to initially upload some 1500 Excel (average size 4k) file into the database..and then user will upload file using a web page (So Web server will copy the file to one of it's directory...and then Oracle will have to upload from there)

Questions:
1. What is the best way to handle the initial batch?
2. using your demo package above....how do we set the permission on oracle server (my_files directory) to see this directory on the web server or any directory may be on my local computer...
is it possible to do it this way?


Thanks...a bunch


Tom Kyte

Followup  

September 09, 2003 - 11:54 am UTC

1) sqlldr is probably what i would use

2) i don't know what you mean.

Size on Blobs

September 09, 2003 - 11:15 am UTC

Reviewer: Sandeep from Germany

Tom,
I created the table demo...in a tablespace as follows...
Questions:

1. why sizes are shown 38 and 86?
2. Do we need to give a size to blob column...like if the average file inserted will be 4k...

Oracle version 8.1.7.4 (HP Unix)
SQL Worksheet (Oracle Enterprise Manager Ver 1.6.0)

CREATE TABLESPACE TS_PMP_BLOB
DATAFILE '/u03/oradata/dev/ts_pmp_blob.dbf' SIZE 10m
AUTOEXTEND ON NEXT 1m MAXSIZE 20m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
LOGGING
ONLINE
/


create table demo
( id int primary key,
theBlob blob
)
tablespace TS_PMP_BLOB
lob (theBlob) store as the_blob
(disable storage in row
chunk 4000
pctversion 20
nocache logging)
/


desc demo

Column Name Null? Type
------------------------------ -------- ----
ID NOT NULL NUMBER(38)
THEBLOB BLOB(86)





Tom Kyte

Followup  

September 09, 2003 - 11:59 am UTC

1) because an int is just a number and a number with 38 digits of precision.

the 86 is interesting, i've never actually seen that do that. a lob locator is about 80 some odd bytes so it must be that length.


oh i see - you are using sql worksheet, not sqlplus -- that is why i've never seen it, i use plus. it is the lob locator length.


file permissions for my_files follow up

September 09, 2003 - 12:15 pm UTC

Reviewer: sandeep from germany

"Followup:
2) i don't know what you mean. "


Let me try again...
Different users will upload files into the database from a web page.
So, Oracle server has to read the file that it needs to insert into the blob from a
directory on the application server....so it will need permissions to read that directory...or to see it...
In your example you created a directory using create directory..and used it to read the file path and
then inserted the file into the table:

"
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' );

.........

......."


So, in this case my_files path existed on the server..and server had permissions on that path...so it could read..
But in my case files will be on Application server (Windows 2000) and database server is HP Unix box..
So, how would Oracle see the directory...what do we need to do to grant permissions..

Thanks again for your patience...

Tom Kyte

Followup  

September 09, 2003 - 12:40 pm UTC

you will need to talk to your system admins and see about getting the drive mounted on the unix box -- totally outside the realm of "Oracle" here....

if you used iAS and mod_plsql, we would upload right into the database, bypassing this extra work.

file permissions for my_files....

September 09, 2003 - 4:41 pm UTC

Reviewer: Sandeep from Germany

Oracle OS: HP Unix
Oracle Version: 8.1.7.4
Application Server (IIS) OS: Windows 2000 Server

May be a different approach...
Active Server Pages (ASP) has this binary data type called
LongVarBinary for binary data....can there be a procedure that expects BLOB parameter and ASP will send this binary data (the whole file) to this procedure and it will then insert into the blob column....

Couple of problems:
Will this work?
What is the code to do this?

Thanks a bunch

file permissions for my_files....

September 10, 2003 - 10:11 am UTC

Reviewer: Sandeep from Germany

Any thing about this:

May be a different approach...
Active Server Pages (ASP) has this binary data type called
LongVarBinary for binary data....can there be a procedure that expects BLOB
parameter and ASP will send this binary data (the whole file) to this procedure
and it will then insert into the blob column....

Couple of problems:
Will this work?
What is the code to do this?

Thanks a bunch



Tom Kyte

Followup  

September 10, 2003 - 7:43 pm UTC

the procedure will NOT expect a blob

the procedure WILL expect a long raw
the procedure can take upto 32k of it at a time.

i've never programmed (nor will I ever program) using ASP or VB. that stuff doesn't run on any computer I own. a tad locked in to a single OS it is. sorry.



Inserting Blob..question

September 10, 2003 - 11:21 am UTC

Reviewer: Sandeep from Germany

file permissions for my_files....continues
Oracle OS: HP Unix
Oracle Version: 8.1.7.4
Application Server (IIS) OS: Windows 2000 Server

So, If I get an ASP parameter (Binary type) passed into a procedure that accepts blob...here is the procedure I found in oracle docs:

CREATE or REPLACE PROCEDURE INSERT_TEMP_CTC_XLS_SHEET (pXLSSHEET CTC_XLS_SHEET.XLSSHEET%TYPE) AS
vXLSSHEET TEMP_CTC_XLS_SHEET.XLSSHEET%TYPE;
Buffer VARCHAR2(32767);
Amount BINARY_INTEGER := 32767;
Position INTEGER;
i INTEGER;
Chunk_size INTEGER;
BEGIN

INSERT INTO TEMP_CTC_XLS_SHEET (CTCID, XLSSHEET, MODIFIEDBY)
VALUES (1, EMPTY_BLOB(),'C0013')
RETURNING XLSSHEET INTO vXLSSHEET;
/* Opening the LOB is optional: */
DBMS_LOB.OPEN (vXLSSHEET, DBMS_LOB.LOB_READWRITE);
Chunk_size := DBMS_LOB.GETCHUNKSIZE(vXLSSHEET);

/* Fill the buffer with 'Chunk_size' worth of data to write to
the LOB. Use the chunk size (or a multiple of chunk size) when writing
data to the LOB. Make sure that you write within a chunk boundary and
don't overlap different chunks within a single call to DBMS_LOB.WRITE. */

Amount := Chunk_size;

/* Write data starting at the beginning of the second chunk: */
Position := Chunk_size + 1;
FOR i IN 1..3 LOOP
DBMS_LOB.WRITE (vXLSSHEET, Amount, Position, Buffer);
/* Fill the buffer with more data (of size Chunk_size) to write to
the LOB: */
Position := Position + Amount;
END LOOP;
/* Closing the LOB is mandatory if you have opened it: */
DBMS_LOB.CLOSE (vXLSSHEET);

END INSERT_TEMP_CTC_XLS_SHEET;




I have modified it a little bit...so that instead of selecting a blom from the table...I am using the parameter...

Question:
1.The parameters coming from ASP/VB will be average 150K..
(Excel files in binary form)
Will this still work...parameter limits etc.
If not how can we make it work...

2. "FOR i IN 1..3 LOOP"
Why does it always go 3 times


Thanks.



ASP VB and BLOBS

September 11, 2003 - 10:34 am UTC

Reviewer: Sandeep from Germany

Tom,
Figured it out...it turned out to be really simple:
INSERT INTO BLOB_TBL (ID, THEBLOB)
VALUES (1, pTHEBLOB);
---WHERE pTHEBLOB IS THE BLOB VARIABLE PASSED TO THE PROCEDURE

AND THEN
Here is the code for ASP/VB people:
</code> http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=238489 <code>

only extra line in asp code was:
declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;

and that did it....

We inserted a 5 meg file and it took like 2 seconds...

Thanks...


Accessing PDF files

September 19, 2003 - 12:53 am UTC

Reviewer: Senthil.S from KL

Dear Tom,

How to access the PDF file from PL/SQL. The requirement is like
this:

There is one report prints one page. There are some pages in PDF file. We
have to select only a few pages corressponds to the first page printed
(Oracle) and print it. For example:

Say page 1 will be printed using oracle report writer . Pages from 2 - 5
will have to be printed from PDF file. There is a key field (matches the key
field in Oracle) in PDF file . We have to select the pages based on this
key. ie. select those pages from PDF files where ever this key is appearing
and print those pages..just print without formatting. Hope I made you
understand the requirement.

Can you pls. show some lights on it..

Thanks,
Senthil.

Tom Kyte

Followup  

September 20, 2003 - 4:59 pm UTC

not going to be doing that in plsql. PDF is something the acrobat reader can read, but plsql cannot.

Reply Immd Pls

September 22, 2003 - 9:45 am UTC

Reviewer: suresh from India


hi tom ,

How to Change the fonts & Sizes In AcrobatReader document?

pls reply immd ....

ok,Thanking



Tom Kyte

Followup  

September 22, 2003 - 10:55 am UTC

dude, this is "asktom.oracle.com" :)

ask me about Oracle...

(and reply immd is sort of pushy isn't it?)


November 11, 2003 - 10:28 am UTC

Reviewer: JP

I know this is a silly question..
Does the directory in <a href=/dcd/owa/image.pdf?p_id=55>Click Me</a>

should be the same in
create or replace directory my_files as '/export/home/tkyte/public_html'; ??

Thanks

Tom Kyte

Followup  

November 11, 2003 - 11:59 am UTC

nope, you are not reading a file from the OS at all -- you are running a procedure. you need to configure a "dad" in iAS for mod_plsql

Memory usage? CLOB?

November 20, 2003 - 5:22 pm UTC

Reviewer: msc

Hello Tom,

I am concerned of htp.prn() memory usage.

My app would generate quite large XMLType which I can send out with loop and htp.prn():
...
l_lob := l_xml.getCLOBval();
loop
htp.prn( dbms_log.substr( l_lob, l_amt, l_loff) );
...
end loop

But how does htp.prn() use session memory?
1. is all response content held in session memory or is it streamed in chunks to mod_pls?
2. is there size limit to for CLOB / BLOB size using this technique?

Is there any alternate method to stream CLOB or XLMType out?


Tom Kyte

Followup  

November 21, 2003 - 4:41 pm UTC

1) all in session memory.
2) 4gig in 9i, terabytes in 10g

3) haven't tried it, but you might look at wpg_docload -- it works with BLOBS, not CLOBS but might be useful (it streams)

how about resizing the image

November 23, 2003 - 3:51 am UTC

Reviewer: Frank from NL

Hi,

I try to build some sort of photogallery using mod_plsql.
I can insert images into db. I can also display images in a page.
What I want to do is to make a downsized example of the image (i.e. a thumbnail) to show in an overview page.
I tried this using java, but I got stuck because of the GUI not supported error of the JVM.
(Preferrably I want to create the thumbnails 'on the fly', not store them in the db)

I tried this code, which works outside of the db (using files instead of BLOBs):

create or replace and compile java source named "Thumbnail" as
package nl.tripode.thumbs;

import java.awt.Image;
import java.awt.Toolkit;
import java.awt.Graphics2D;
import java.awt.geom.AffineTransform;
import java.awt.image.BufferedImage;
import java.io.OutputStream;
import oracle.sql.BLOB;
import com.sun.image.codec.jpeg.JPEGCodec;
import com.sun.image.codec.jpeg.JPEGImageEncoder;

class Thumbnail {

public static void createThumbnail(BLOB orig, BLOB thumb, int maxDim) throws Exception {

int length = (int) (orig.length());
byte[] imageBytes = orig.getBytes(1, length);
Image inImage = Toolkit.getDefaultToolkit().createImage(imageBytes);


// Determine the scale.

double scale = (double)maxDim / (double)inImage.getHeight(null);
if (inImage.getWidth(null) > inImage.getHeight(null)) {
scale = (double)maxDim/(double)inImage.getWidth(null);
}

// Determine size of new image.
int scaledW = (int)(scale * inImage.getWidth(null));
int scaledH = (int)(scale * inImage.getHeight(null));

// Create an image buffer in
//which to paint on.
BufferedImage outImage = new BufferedImage( scaledW
, scaledH
, BufferedImage.TYPE_INT_RGB);

// Set the scale.
AffineTransform tx =
new AffineTransform();

// If the image is smaller than
//the desired image size,
// don't bother scaling.
if (scale < 1.0d) {
tx.scale(scale, scale);
}

// Paint image.
Graphics2D g2d = outImage.createGraphics();
g2d.drawImage(inImage, tx, null);
g2d.dispose();

OutputStream os = thumb.getBinaryOutputStream();
JPEGImageEncoder encoder = JPEGCodec.createJPEGEncoder(os);
encoder.encode((BufferedImage)outImage);
os.close();

}
}
/

Wrapper:
create or replace procedure make_thumb
( p_orig blob
, p_thumb blob
, p_maxdim number
)
as language java
name 'nl.tripode.thumbs.Thumbnail.createThumbnail(oracle.sql.BLOB, oracle.sql.BLOB, int)'
;

and funtion:

create or replace function make_thumbfnc
( p_image in out blob
, maxdim in int
)
return blob
as
l_thumb blob;
begin
if p_image is null
then
return null;
end if;
dbms_lob.createtemporary( lob_loc => l_thumb
, cache => true
);
make_thumb(p_image, l_thumb, maxdim);
return l_thumb;
end make_thumbfnc;


Any ideas/hints ?

Tom Kyte

Followup  

November 23, 2003 - 8:47 am UTC

no need for java. interMedia does this

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a88786/toc.htm <code>



December 08, 2003 - 6:10 am UTC

Reviewer: Huss from Kuwait

Dear tom ,
the case i have is
i want to store all forms and report *.rdf and *.fmb
in Blobs, to be stored in the database.
so ,
1 - no problem in upload files , but what about retrive from db to client machin (i mean by clients developers machins)
i.e developer can retrive the report or form file and modify it and upload again to db
2 - i need a hint about how to encrypt the Blobs ?!

thnakx for ur help


Tom Kyte

Followup  

December 08, 2003 - 6:46 am UTC

if you want to store the rdf/fmb files in blobs, you should be looking at collaboration suite (a filesystem in the database)

if you just put them into blocks, you are responsible for getting them out -- If you figured out how to "upload them", downloading is done using the same technique (which you did not describe, so I cannot be less vague. did you write a program, use mod_plsql what)


read about dbms_obfuscation_toolkit and if you have my book expert one on one Oracle -- i demonstrated how to encrypt blobs 32k at a time using that api

PLease explain

December 09, 2003 - 1:50 am UTC

Reviewer: umesh s kasturi from bangalore India

Tom
In your answer at the top refer to :
<a href=/dcd/owa/image.pdf?p_id=55>Click Me</a>

Do I need to have this 
I have utl_file_dir = c:\usk_utl_file_dir
and I have a file called "rarrow.gif","oracle.gif" in that directory 


  1  declare
  2      l_blob    blob;
  3      l_bfile    bfile;
  4  begin
  5      insert into demo values ( 2, empty_blob() )
  6      returning theBlob into l_blob;
  7      l_bfile := bfilename( 'MY_FILES', 'oracle.gif' );
  8      dbms_lob.fileopen( l_bfile );
  9      dbms_lob.loadfromfile( l_blob, l_bfile,
 10                                 dbms_lob.getlength( l_bfile ) );
 11      dbms_lob.fileclose( l_bfile );
 12* end;
SQL> /

PL/SQL procedure successfully completed.

Do I use 
<a href=c:\usk_utl_file_dir\Oracle.gif?p_id=1>Click Me</a>
to display the "oracle.gif" image in it

I have not understood the point of displaying on html page from the DB Please help 


 

Tom Kyte

Followup  

December 09, 2003 - 6:28 am UTC

no, we are not using a file system, we are using blobs.

my above question is incorrect

December 09, 2003 - 1:55 am UTC

Reviewer: umesh kasturi from Bangalore India

Tom
In your answer at the top refer to :
<a href=/dcd/owa/image.pdf?p_id=55>Click Me</a>

Do I need to have this 
I have utl_file_dir = c:\usk_utl_file_dir
and I have a file called "rarrow.gif","oracle.gif" in that directory 

SQL> show parameter utl

NAME                                 TYPE    VALUE
------------------------------- ------- ---------------utl_file_dir                     string  c:\usk_utl_file_dir

SQL> create or replace directory my_files as 'c:\usk_utl_file_dir';

Directory created.


  1  declare
  2      l_blob    blob;
  3      l_bfile    bfile;
  4  begin
  5      insert into demo values ( 2, empty_blob() )
  6      returning theBlob into l_blob;
  7      l_bfile := bfilename( 'MY_FILES', 'oracle.gif' );
  8      dbms_lob.fileopen( l_bfile );
  9      dbms_lob.loadfromfile( l_blob, l_bfile,
 10                                 dbms_lob.getlength( l_bfile ) );
 11      dbms_lob.fileclose( l_bfile );
 12* end;
SQL> /

PL/SQL procedure successfully completed.

Do I use 
<a href=c:\usk_utl_file_dir\oracle.gif?p_id=2>Click Me</a>
to display the "oracle.gif" image in it

I have not understood the point of displaying on html page from the DB Please 
help 
 

Retaining a filename in blob

December 12, 2003 - 8:34 pm UTC

Reviewer: Tony Reed from Oslo, Norway

Hi Tom,

Is there any way of retaining the original filename of an image stored as a blob, (actually an ordsys.ordimage)?

When users save an image from the web they recieve the procedure name + image id which served the image.
I've tried various approaches including using the mime-header (Content-Type: image/jpeg; name="filename") attribute to no avail.
I know this probably is out of your scope, but I thought that you might have come across the problem, and any hints would be much appriciated as always

Tony Reed

ps. On a personal note. Looking forward to seeing you in Denmark in January!

Tom Kyte

Followup  

December 14, 2003 - 10:39 am UTC

This should do it:


dbms_lob.open(i.blob_content, dbms_lob.lob_readonly);
owa_util.mime_header(i.mime_type, false);
htp.p('Content-Length: ' || i.doc_size);
htp.p('Content-Disposition: attachment; filename=' || l_filename);
htp.p('Last-Modified: ' || to_char(i.last_updated,'Dy, DD Mon YYYY HH:MI:SS') || ' GMT');
owa_util.http_header_close;
wpg_docload.download_file(i.blob_content);


Displaying PDF files stored in the Database

December 15, 2003 - 9:47 am UTC

Reviewer: Leonard Anukam from Finland

This was very useful, as I learned another way of loading imgae into the DB. But still could not get thr PDF side of it running successfully. I will like to know how to get it done.
It says Acrobat can not open the file image_get.my_pdf. Either it is not a support file type or the file is corrupt. I will like my info on the best way to do it

Thanks

Tom Kyte

Followup  

December 15, 2003 - 10:09 am UTC

well, you didn't tell me which approach you used.

if you use the htp approach -- you need to make 100% sure the app/web servers NLS_LANG is exactly the same as the databases character set -- else characterset conversion takes place.

if your app server and db are on say windows and unix respectively -- this is most likely not the case by default -- you'll have to make sure it is

(or use the wpg_docload.download_file(l_lob); approach which was added after the question was originally asked and is a superior method)

December 17, 2003 - 4:35 pm UTC

Reviewer: A reader


Displaying PDF files

December 18, 2003 - 3:06 pm UTC

Reviewer: Leonard Anukam from Finland

Tom can you be a bit specific on where to add the following codes you gave
dbms_lob.open(i.blob_content, dbms_lob.lob_readonly);
owa_util.mime_header(i.mime_type, false);
htp.p('Content-Length: ' || i.doc_size);
htp.p('Content-Disposition: attachment; filename=' || l_filename);
htp.p('Last-Modified: ' || to_char(i.last_updated,'Dy, DD Mon YYYY HH:MI:SS')
|| ' GMT');
owa_util.http_header_close;
wpg_docload.download_file(i.blob_content);


At what point in the Package body can we add it. Or do we totally replace the following section with it.

begin
select theblob into l_lob
from mypdfs where id = p_id;
owa_util.mime_header('TEST/pdf');--- I tried to put the code here but it failed.
begin
loop
dbms_lob.read(l_lob, l_amt, l_off, l_raw);
htp.prn(utl_raw.cast_to_varchar2(l_raw));
l_off := l_off + l_amt;
end loop;

if you can please rewrite the original code by using the this

dbms_lob.open(i.blob_content, dbms_lob.lob_readonly);
owa_util.mime_header(i.mime_type, false);
htp.p('Content-Length: ' || i.doc_size);
htp.p('Content-Disposition: attachment; filename=' || l_filename);
htp.p('Last-Modified: ' || to_char(i.last_updated,'Dy, DD Mon YYYY HH:MI:SS')
|| ' GMT');
owa_util.http_header_close;
wpg_docload.download_file(i.blob_content);

Thanks.

Tom Kyte

Followup  

December 18, 2003 - 4:08 pm UTC

see the followup in "Another (standard) method "

Oracle Reports reading a BLOB

December 22, 2003 - 5:30 pm UTC

Reviewer: MT from USA

We have a requirement to display the content of BLOB stored in Oracle database using Oracle reports. The BLOB can have various document files (pdf, Word files, Excel files....).

Is this possible, to display in an Oracle Report output. I am new to Oracle Reports, so, any input would be valuable. Really appreciate the help. Thanks.

Tom Kyte

Followup  

December 22, 2003 - 6:43 pm UTC

sorry, i don't use or do reports.

try otn.oracle.com -> discussion forums.

there you will find people that use it everyday.

creating pdf

December 29, 2003 - 7:54 am UTC

Reviewer: A reader

Dear Tom,
How best would you suggest to create pdf files from database.
The requirement is to create one pdf file corresponding to a database record. Oracle reports could have been used if the generated file was required to be one, instead of many?
Please provide any reference or third party tool that you think might do the job.
Best regards

Tom Kyte

Followup  

December 29, 2003 - 10:39 am UTC

i'll let others comment, not aware of anything specific.

(although reports can do this, you would just pass in the primary key to the report and run reports for each key value)

Create Pdf file in a database

December 29, 2003 - 12:13 pm UTC

Reviewer: Lev from Toronto

Hi,

To create PDF file I loaded java library from
</code> http://www.lowagie.com/iText/ <code>in database.

I just repeated example from "Expert One on One Oracle".
It works fine but I have not tested it in a real environment.
By default this library creates PDF file, but it is not difficult to
create it BLOB instead.

Hope this helps.

Lev


December 30, 2003 - 12:59 pm UTC

Reviewer: A reader

Hi Tom,
I loaded iText.jar file into database from </code> http://www.lowagie.com/iText/ <code>site using loadjava.. and I am able to create pdf file from database...This is fine for static text BUT my datasource is SQL query like SELECT * FROM EMP
WHERE EMPNO = 100 and I want output written in pdf file in Tablar format.

How can I do that??

Thanks


Public class pdftest {
public static void pdfdemo(String args) {


System.out.println("Chapter 1 example 3: PageSize");

// step 1: creation of a document-object
Document document = new Document(PageSize.A4.rotate());

try {

// step 2:
// we create a writer that listens to the document
// and directs a PDF-stream to a file

PdfWriter.getInstance(document, new FileOutputStream("c:\\Chap0101.pdf"));

// step 3: we open the document
document.open();

// step 4: we add some phrases to the document
for (int i = 0; i < 10000; i++) {
document.add(new Phrase("Jay Swaminarayan; ")); <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---- HERE I WANT SQL QUERY
}

}
catch(DocumentException de) {
System.err.println(de.getMessage());
}
catch(IOException ioe) {
System.err.println(ioe.getMessage());
}

// step 5: we close the document
document.close();

Tom Kyte

Followup  

December 30, 2003 - 2:05 pm UTC

you'd have to find a java programmer who codes jdbc -- it is very straightforward to do that....

PFF is NOT working but MS-Word and Text is working fine.

December 30, 2003 - 4:34 pm UTC

Reviewer: Sami from NJ,USA

Browser: IE 6.0 DB:Oracle 9.2.0.4 EE OS: Windows NT 4.0

Dear Tom,

I went thru this page from top to bottom.

But for me...

1)
</code> http://rutq/pls/sami/getpdfs.txt
==> is working fine

2)
http://rutq/pls/sami/getpdfs.doc
==> is working fine IF i specify,
    l_amt number := 32767;
    l_raw raw(32767);

But IF l_amt number and l_raw  := 4096; is opening ms-word applications but not opening the file.

3)

http://rutq/pls/sami/getpdfs.pdf
==> is NOT working. 

There is no error message on the screen nor on apache logfile. As soon as I hit the above URL, status bar says "start downloading from site 
http://rutq/pls/sami/getpdfs.pdf" <code>and keep hanging, after while status became "Done".

Could you please throw some light on this?

--spec is not included
CREATE OR REPLACE package body getpdfs
as
PROCEDURE pdf
is
l_lob blob;
-- l_amt number := 32767;
l_amt number := 4096;
l_off number := 1;
l_raw raw(4096);
begin
select theblob into l_lob
from mypdfs where id = 1; -- pdf fileid
owa_util.mime_header('application/pdf');
begin
loop
dbms_lob.read(l_lob, l_amt, l_off, l_raw);
htp.prn(utl_raw.cast_to_varchar2(l_raw));
l_off := l_off + l_amt;
end loop;
exception
when Others then
htp.prn('PDF Error'||SQLCODE||' : '||SQLERRM(SQLCODE)||' '||l_off);
end;
exception
when Others then
htp.prn('PDF Error'||SQLCODE||' : '||SQLERRM(SQLCODE)||' '||l_off);
end;


PROCEDURE doc
is
l_lob blob;
l_amt number := 32767;
l_off number := 1;
l_raw raw(32767);
begin
select theblob into l_lob
from mypdfs where id = 4;-- ms-word doc fileid

owa_util.mime_header('application/msword');
begin
loop
dbms_lob.read(l_lob, l_amt, l_off, l_raw);
htp.prn(utl_raw.cast_to_varchar2(l_raw));
l_off := l_off + l_amt;
end loop;
exception
when no_data_found then
htp.prn('Doc Error'||SQLCODE||' : '||SQLERRM(SQLCODE));
end;
end;

PROCEDURE txt
is
l_lob blob;
l_amt number := 32767;
l_off number := 1;
l_raw raw(32767);
begin
select theblob into l_lob
from mypdfs where id = 3; --Text fileid
owa_util.mime_header('text/plain');
begin
loop
dbms_lob.read(l_lob, l_amt, l_off, l_raw);
htp.prn(utl_raw.cast_to_varchar2(l_raw));
l_off := l_off + l_amt;
end loop;
exception
when no_data_found then
htp.prn(' Text Error'||SQLCODE||' : '||SQLERRM(SQLCODE));
end;
end;

end;

end;
/



Tom Kyte

Followup  

December 30, 2003 - 4:59 pm UTC

use

wpg_docload.download_file(l_lob);

tis easier, faster and takes less memory.

DB PU usage went to 100%

December 30, 2003 - 5:33 pm UTC

Reviewer: Sami

Dear Tom,

1)Replaced a single line(wpg_docload.download_file(l_lob); ) instead of dbms_lob.read as you suggested.

2)did hit </code> http://rutq
<== works fine

3)did  
http://rutq/pls/hsbc/getpdfs.txt
 <== cpu went to 100%
(oracle.exe process cpu usage went to 100% & stays there until I issue shutdown abort.)

4)replaced wpg_docload.download_file with dbms_lob.read.

5)did  
http://rutq/pls/hsbc/getpdfs.txt <code> <== WORKS FINE.



I tried even restarting Apache but no luck.

procedure txt
is
l_lob blob;
l_amt number := 32767;
l_off number := 1;
l_raw raw(32767);
begin
select theblob into l_lob
from mypdfs where id = 3;
owa_util.mime_header('text/plain');
begin
loop
wpg_docload.download_file(l_lob); -- <==newly added line
--dbms_lob.read(l_lob, l_amt, l_off, l_raw);
htp.prn(utl_raw.cast_to_varchar2(l_raw));
l_off := l_off + l_amt;
end loop;
exception
when no_data_found then
htp.prn('\n Text Error'||SQLCODE||' : '||SQLERRM(SQLCODE));
end;
end;


Tom Kyte

Followup  

December 30, 2003 - 6:13 pm UTC

the docload approach should look like this:

create or replace procedure download(p_file_id files.id%type)
as
l_lob files.content%type; l_mime mimetypes.name%type;
begin
select f.content, m.name
into l_lob, l_mime
from files f, mimetypes m
where f.id = p_file_id
and f.mimetype_id = m.id;
owa_util.mime_header(l_mime, false);
owa_util.http_header_close;
wpg_docload.download_file(l_lob);
end;

don't call in a loop, no htp.prn, just that.

Dear Tom, YOU ARE THE MAN!

December 30, 2003 - 9:07 pm UTC

Reviewer: Sami


Image Processing

January 08, 2004 - 9:03 am UTC

Reviewer: Rajashekhar GANGA from Mumbai, INDIA

Dear Sir,

is it possible to compare two image file.
for exmple.
we have a table t as .
pid NUMBER
pname VARCHAR2(200)
p_pic blob

i want to store the photograph (From digital Camera) search in database. if new patient insert the row,
if old patient fetch old consulting details.

i have an alternative solution like asking for last visit prescription and getting details from it. But if it happens like earlier it will very much new experience for me.

Thank for reading the request.

Raj.

Tom Kyte

Followup  

January 08, 2004 - 2:18 pm UTC

well, you wouldn't be comparing images i hope here (i mean, today i look different than yesterday).

You would be looking for the patient record before even wasting the batteries on the camera no?

OK

January 09, 2004 - 12:18 am UTC

Reviewer: Siva from Hyderabad,India

Dear Sir,
Is "wpg_docload" a new package?I have not seen this before.
what does "wpg" denote?Is there any docs for it with you?Please do reply.
Bye!


Tom Kyte

Followup  

January 09, 2004 - 8:27 am UTC

it is a couple of years old.

</code> http://docs.oracle.com/docs/cd/A97329_03/web.902/a90855/toc.htm <code>

see section 2.6

Displaying PDF files stored in the Database

January 28, 2004 - 8:03 am UTC

Reviewer: Cosmin Sideras from Medias, Romania

You're the man TOM

Displaying PDF files (stored as BLOBs in Database) using Forms 6i

February 10, 2004 - 5:28 pm UTC

Reviewer: umasankar from California, USA

Hi Tom,

We have PDF and Word documents stored in Database. We are using Oracle Forms 6i as User Interface. Through Forms we could view word documents which are stored in database, using OLE container.

But we could not view PDF files.

It would be more helpful and valuable if I get ideas/suggestions on this.

Thanks in advance,
Umasankar

Tom Kyte

Followup  

February 11, 2004 - 8:41 am UTC

sorry -- I haven't touched forms since about 1995 and have no computers capable of doing "ole" -- you can try otn.oracle.com -> discussion forums.

Different sizes in uploading and downloading

February 25, 2004 - 4:53 pm UTC

Reviewer: A reader

Hi Tom, Sorry, I tried several times, and checked, to do it work, but I can't
I upload one form and get other in other size, I copy the examples from here, but I can't see what is wrong.
If you can please giveme a hand.

create table demo
( id int primary key,
theBlob blob
)
/

drop directory my_files ;
create or replace directory my_files as 'c:\';

--UPLOADING

declare
l_blob blob;
l_bfile bfile;
begin
DELETE FROM ADM.DEMO;
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;

l_bfile := bfilename( 'MY_FILES', 'f.fmb' );
dbms_lob.fileopen( l_bfile );

dbms_output.put_line(dbms_lob.getlength( l_bfile ));
--file length is 2699264
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );

dbms_lob.fileclose( l_bfile );
COMMIT;
end;
/

-- DOWNLOADING
declare
vblob blob;
vstart number:=1;
bytelen number := 32000;
len number;
my_vr raw(32000);
l_output utl_file.file_type;
p_dir varchar2(30) default 'MY_FILES';
p_file varchar2(30) default 'g.fmb';
begin

-- get the blob locator
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
for l_cur in (SELECT theblob mylob FROM demo)
loop
len := DBMS_LOB.GETLENGTH(l_cur.mylob);
vblob := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len)); --****Length of the Column : 2699264 correct

vstart := 1;
while (vstart < len) loop -- loop till entire data is fetched
DBMS_LOB.READ(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
vstart := vstart + bytelen ;
end loop;
utl_file.fclose(l_output);
dbms_output.put_line('vstart : ' || to_char(vstart));
end loop;
exception when others then
utl_file.fclose(l_output);
dbms_output.put_line(sqlerrm);
end ;


Verifying generated file
declare
l_blob blob;
l_bfile bfile;
begin

l_bfile := bfilename( 'MY_FILES', 'g.fmb' );
dbms_lob.fileopen( l_bfile );

dbms_output.put_line(dbms_lob.getlength( l_bfile ));
-- has an incorrect lenght = 2716188
dbms_lob.fileclose( l_bfile );
COMMIT;
end;
/


Tom what is wrong in this code to recreate file I copied from here, I checked several times and I don't see nothing wrong, please.

Tom Kyte

Followup  

February 25, 2004 - 7:17 pm UTC

utl_file is not "binary friendly" -- is this file "binary" (fmb = forms binary, perhaps you want to use an FMT = forms text instead)....

February 25, 2004 - 5:12 pm UTC

Reviewer: A reader

Thank I compared hexadecimals
and the problem is the same
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6379798216275 <code>has

it replaces every 0a for 0a0d

Thanks

Ugly question

February 26, 2004 - 8:51 am UTC

Reviewer: A reader

Tom I have an ugly question to you.
What could happen specifically if I do an extract ddl from utl_file package in 9024 and try to put it in my 902,
In my opinion the worst will be utl_fil will not work, i execute the previous utl_file ddl and all is OK.
or not?
for example alert log uses utl_file package?
Thank you.

Tom Kyte

Followup  

February 26, 2004 - 1:35 pm UTC

you cannot take internal code like utl_file and others from version X and plop it into version y. You should only use the code supplied in the $ORACLE_HOME/rdbms/admin codetree with the install.


no, the alert log writing does not use utl_file. other utilities like logminer might but not writing the alert log.

Thanks Tom

February 26, 2004 - 2:17 pm UTC

Reviewer: A reader


What about a pdf search

March 05, 2004 - 5:01 pm UTC

Reviewer: Mike Cheatham from Kwajalein, Marshall Islands

This was very helpful information. I would like to take it a step future.

How would I search a blob containing the pdf? I have been able to get my search to work with other file types (doc,xls,txt), but not pdf.

What do I need to change in my code so that I can search the blob containing the pdf data?

PROCEDURE search (in_search VARCHAR2) IS
lob_doc BLOB;
Pattern VARCHAR2(30);
l_doc_title varchar2(255);
Position INTEGER := 0;
Offset INTEGER := 1;
Occurrence INTEGER := 1;
BEGIN
Pattern := utl_raw.cast_to_raw(in_search);
for c in (select * from my_docs) loop
lob_doc := c.doc_blob;
l_doc_title := c.doc_title;
DBMS_LOB.OPEN (lob_doc, DBMS_LOB.LOB_READONLY);
Position := DBMS_LOB.INSTR(lob_doc, Pattern, Offset, Occurrence);
IF Position = 0 THEN
DBMS_OUTPUT.PUT_LINE('Pattern not found');
ELSE
DBMS_OUTPUT.PUT_LINE('The pattern '''|| in_search||''' can be found in '||l_doc_title);
END IF;
DBMS_LOB.CLOSE (lob_doc);
end loop;
END search;



Tom Kyte

Followup  

March 05, 2004 - 7:08 pm UTC

you use Oracle Text to index it. pdf is generally not "clear text", it is lzw compressed text and needs be filtered.

Even doc, xls will need this (both will contain text that is not YOUR text)

See
</code> http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-TEX <code>

some additional notes

March 16, 2004 - 5:05 pm UTC

Reviewer: Zsolt Müller from Hungary

Hi!

I've read through the answers/followups and would like to add some notes.

1.) Concerning the "wpg_docload vs. htp.prn" question

The wpg_docload.download_file() method for outputting BLOBs to the browser is definitly better than the htp.prn() method.

Reasons:
a.)
- the latter reads the BLOB from the database in small pieces and puts them to the htp.htbuf array by calling htp.prn ... mod_plsql reads this buffer at successful completion of the procedure and submits the data to the browser
- the wpg_docload method reads the BLOB directly from the database ... no intermediate buffer is used (that's the reason for the smaller memory usage :) )

b.)
- the htp.prn way includes an utl_raw.cast_to_varchar2() call ... that means the data is converted from RAW to VARCHAR2 ... not a big overhead, but still ;)
- the wpg_docload method does not incorporate such a datatype conversion ... since it's absolutely unnecessary :)

2.)
You can put the file_download code (owa_util.mime_header(), ..., wpg_docload.download_file()) anywhere in your procedure(s) if you put a "htp.init" before it and a "return" after it. :)

Like this:

procedure gif(p_id in demo.id%type)
is
l_lob blob;
begin
select theBlob into l_lob
from demo
where id = p_id;

-- ... any code ... (might even contain htp.p() calls :)

htp.init;
owa_util.mime_header('image/gif');
wpg_docload.download_file(theBlob);
return;

-- ... any code ...
end gif;

htp.init: clears the htp.htbuf buffer and resets the htp package state variables.
(Note: many people think owa_util.showpage is meant for reseting the htp output ... they are wrong. It's htp.init.)


Tom Kyte

Followup  

March 16, 2004 - 6:10 pm UTC

1) 100% concurr -- absolutely. Unfortunately, it post-dates the original question by a long time.

1b) that is virtually no overhead, there is no conversion -- it is a datatype field change only -- couple of bits are modified, nothing else.

and one more thing :)

March 16, 2004 - 5:19 pm UTC

Reviewer: Zsolt Müller from Hungary

I almost forgot one more thing ... ;)

Nobody should use Internet Explorer for debugging such things like mod_plsql output, webserver <-> client connections, etc. IE's error pages are far from helpful and most times misleading.

I always use tools like this:
- wget (a command line download utility ... can provide very in-depth view of the webserver-client connection :) )
- curl (another command line tool)
- tcpdump (Only in worst case ... when stuff above doesn't help. Might be replaced by some personal firewall for Win* with capability to save raw TCP packets.)

Both wget and curl are available for Windows and both are open-source and freeware programs. :)
(wget is protected by GPL. curl is not under GPL, but it's free to use.)


Tom Kyte

Followup  

March 16, 2004 - 6:21 pm UTC

I use sqlplus myself :) the ultimate in command line tools...

but actually, now I use htmldb and find i don't really need to resort to plus or wget or anything like that -- it is rather nice.

March 29, 2004 - 2:21 pm UTC

Reviewer: reader

dear sir ,
i use DB_To_Client in the webutil
to Downloads a file from a BLOB column in the database
to the web client machine.
but my problem is that i have a CLOB column now
and i want to make web client user downlad it !
can u help me in this

Tom Kyte

Followup  

March 29, 2004 - 3:08 pm UTC

sounds like forms -- i haven't touched forms since about March of 1995....

I'll have to refer you to otn.oracle.com -> discussion forums.

display PDF in Browser

May 18, 2004 - 6:15 pm UTC

Reviewer: Joaquin from Seaside, CA US

I'm having a probelm displaying a PDF that is 846kb. When the window opens parts are missing. I'm wondering if there is another format I could use that's bigger than 32K?
I'm on 8.1.7 Oracle DB
Here is what I have..
procedure show_pdf is

imageraw RAW(32760);
v_blob BLOB;
l_amt number := 32760;
l_off number default 1;



begin
owa_util.mime_header('application/pdf');
select text_1 into v_blob from ptltdr.articles where name = ('data.pdf');



BEGIN
LOOP
dbms_lob.read(v_blob,l_amt, l_off,imageraw);
htp.prn(utl_raw.cast_to_varchar2(imageraw));
l_off := l_off+l_amt;
--l_amt :=4096;

END LOOP;
exception
when no_data_found then
NULL;
end;
END show_pdf;

Tom Kyte

Followup  

May 19, 2004 - 7:42 am UTC

is your NLS_LANG of the app server the SAME as the NLS_LANG of the database.

if you "right click" on the pdf link and save the pdf to disk, is it "corrupt" or the same as the input file and if different, what is different -- any pattern.

but I'd ctl-f for docload on this page and use that technique.

wpg_download has likely 2GB limit

May 25, 2004 - 11:02 am UTC

Reviewer: bob from PA

Well, I am not exactly sure what the limit is, but my 1.1GB file will download fine, and the 2.3 GB download throws numeric overflow exception in wpg_docload.

I suspect 2GB is the limit. So much for the 4 GB BLOB limit. Can anybody verify this is a limit?

I couldn't find any bugs or documentation indicating this problem/limitation

The same procedure is used to download both lobs. dbms_lob.getlength reports 2.3 GB for blob size on the one that is failing.

I am on 9.0.1.4 db, mod_plsql with apache.


Tom Kyte

Followup  

May 25, 2004 - 11:26 am UTC

you would really download gigabytes over your webserver? wow.

but 2^31 is one of those magic numbers, they most likely have put the content length into a C int and it is overflowing. You'll need to file a tar with support on that one.

how else do your users get your data..

May 25, 2004 - 2:14 pm UTC

Reviewer: bob from PA

As a searchable archive of large data files, serving content out through the web from the data store (the db), is a convenient mechanism.

I could just as easily allow the search interface to present ftp links to the large file(s), assuming we have the ftp server up, but keeping the content in the database is ideal for all the reasons you have mentioned in this thread and others. If we want to ftp than we need collabsuite/o-files. My only other alternative is via the web or a client/server app (which would never happen).

The end user group retrieving the files is the only user of the bandwidth, so if they want to use it up retrieving 2GB files from our webserver so be it. Or at least this is our philosophy so far.

Tom Kyte

Followup  

May 25, 2004 - 2:37 pm UTC

It is just really large for a web server to return (larger then I've ever seen anyone even try actually).

I don't doubt you that it doesn't work -- you'll need a tar with support to get an answer and see if there is a workaround/fix in the works though.

very true..

May 25, 2004 - 2:58 pm UTC

Reviewer: bob from PA

> It is just really large for a web server to return
>(larger then I've ever seen anyone even try actually).

That notion supports why this likely hasn't been discovered before. :)

I filed a tar and will post whatever resolution/workaround/bug info they provide for future asktom users who come looking to this thread for downloading large files.



Solution to the "too large download problem"

May 25, 2004 - 3:23 pm UTC

Reviewer: Tom from England

If you have the java option, it is fairly simple to use the java.util.zip classes to write a java stored procedure to zip any given clob. Assuming the data you are downloading is compressible, that should push you quite a long way over the 2Gb limit.

owa limit

May 27, 2004 - 6:24 pm UTC

Reviewer: bob from PA

Initial response from support is that this is a owa limit (2GB), not a bug.

As far as zipping goes, yes, I could, but these files deflate about 10% without custom compression and that is helpful, but not as helpful as being able to download the limits of the BLOB

Tom Kyte

Followup  

May 27, 2004 - 8:58 pm UTC

are you up for java? in the middle tier?

Displaying PDF files stored in the Database

May 31, 2004 - 4:56 am UTC

Reviewer: A reader


Displaying PDF files stored in the Database

May 31, 2004 - 4:59 am UTC

Reviewer: Reader from Spain

How is the better way to store / retrive information from a table containing a ORDSYS.ORDIMAGE column data type?

Tom Kyte

Followup  

May 31, 2004 - 1:12 pm UTC

not sure what you are saying...

Displaying PDF files stored in the Database

June 01, 2004 - 12:05 pm UTC

Reviewer: Reader from Andorra

Tom

Can you help me? I need to load pdf documents in the database (9.2.0.4) via ASP (IE 6.0, IIS 5.1).
After loading, displaying files by the same via.

Thanks

Tom Kyte

Followup  

June 01, 2004 - 3:22 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96591/toc.htm <code>

lots of ways to do it, examples are in there.

2GB workaround

June 02, 2004 - 9:55 am UTC

Reviewer: bob from PA

The workaround from support for the 2GB limit in wpg_docload.download file was to change the pls_integer to NUMBER in the wpgdocload(b/s) package source and reload. It worked for me.



colour problem while displaying image from BLOB column on UNIX

July 20, 2004 - 6:04 am UTC

Reviewer: Deba from India

Hi,

I have seen the total thread. I am facing colour problem on UNIX platform while trying to display a gif image in browser from BLOB column.

The O/S is Solaris 9.5
The database is 9.2.0.5
Apache HTTP Server Version 1.3 ( comes with Oracle Enterprise manager edition in 9i rel 2)

Now I have a table IMAGES

CREATE TABLE images (
id NUMBER(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
image BLOB NOT NULL
)
/

Added following constraints

ALTER TABLE images ADD (
CONSTRAINT images_pk PRIMARY KEY (id)
)
/
ALTER TABLE images ADD (
CONSTRAINT images_uk UNIQUE (name)
)
/

Now I have created a sequence

CREATE SEQUENCE images_seq
/

Now I have created following directory

CREATE OR REPLACE DIRECTORY image_dir AS '/u01/oracle/das'
/

Now I have created a procedure to load image into BLOB
column of IMAGES table.

CREATE OR REPLACE PROCEDURE image_Load (p_name IN images.name%TYPE) IS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO images (id, name, image)
VALUES (images_seq.NEXTVAL, p_name, EMPTY_BLOB())
RETURN image INTO v_blob;

v_bfile := BFILENAME('IMAGE_DIR', p_name);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;

Now I have created a function to display the image in browser.We are using Oracle HTTP server ( comes Oracle 9i rel 2 ). The function is as follows :

CREATE OR REPLACE PROCEDURE get_img AS
vblob BLOB;
buffer RAW(32000);
buffer_size INTEGER := 32000;
offset INTEGER := 1;
LENGTH NUMBER;
BEGIN
owa_util.mime_header('image/gif');
SELECT image INTO vblob FROM images ;
LENGTH := dbms_lob.getlength(vblob);
WHILE offset < LENGTH LOOP
dbms_lob.READ(vblob, buffer_size, offset, buffer);
htp.prn(utl_raw.cast_to_varchar2(buffer));
offset := offset + buffer_size;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
htp.p(SQLERRM);
END;
/

Now I have ftped a gif file consists of blue colour called
blue.gif .Now I have loaded that file into database using
following program :

BEGIN
image_Load('blue.gif');
END;
/

--successfully loaded

Now if I give </code> http://bbcc:7778/pls/stps2/get_img <code> (where bbcc is host name and stps2 is dad name) in browser then we are not getting blue colours , only black is coming.

Now on windows server ( database and http server are both on windows server 2000 ) everything is working fine, we are getting colours but on UNIX ( database and http server are both on Solaris ),execution is ok but we are not getting any colours, only black colour is coming.

Now I have set nls_lang for http server (to avoid character set conversion ) by setting nls_lang="ENGLISH_UNITED KINGDOM.WE8ISO8859P1" in wdbsvr.app file in $ORACLE_HOME/Apache/modplsql/cfg directory.

Still the problem exists. Now I have imported IMAGES from UNIX to another database which resides on Windows. But here we are getting the colours. It means that data in table is ok.

I can not find out where the problem is . Plesae help me since it is very serious issue to us.

Deba

Tom Kyte

Followup  

July 20, 2004 - 9:11 am UTC

ctl-f on this page for wpg -- that is an INFINITELY superior method.

July 27, 2004 - 10:42 am UTC

Reviewer: A reader

Tom,We are getting bunch of XML files from our client and our developers will convert them to .PDF files.

Now we have to load this files into our Database (9i R2) schema on daily basis and create some kind of web site (URL ?) to read and download that pdf files.Please advice.

Thanks.

Tom Kyte

Followup  

July 27, 2004 - 12:49 pm UTC

ctl-f for wpg_doc and see how easy it is to return a lob from the database.

Key word - wpg_doc

July 27, 2004 - 1:50 pm UTC

Reviewer: A reader

Tom,I have managed to load the pdf's into DB.My question is,
how do i aceess apache web page to create a DAD,so that i can able to view the pdf files .i'm sure we installed apache along with db 9i r2.Pls guide me.





Tom Kyte

Followup  

July 27, 2004 - 2:16 pm UTC

you just edit the config files by hand actually, there isn't a web page.

conf files

July 27, 2004 - 2:54 pm UTC

Reviewer: A reader

Tom,Could pls provide more specific infos or useful links like conf file names,location,entires etc.

Thanks.

Tom Kyte

Followup  

July 27, 2004 - 3:32 pm UTC

</code> http://docs.oracle.com/docs/cd/A97329_03/mix.902/q20102/toc.htm

specifically:

http://docs.oracle.com/docs/cd/A97329_03/web.902/a90855/toc.htm

and

http://docs.oracle.com/docs/cd/A97329_03/web.902/a90855/install.htm#1005663

where I am shown to be a liar :)  there are gui pages, just never used them.


http://docs.oracle.com/docs/cd/A97329_03/web.902/a92173/confmods.htm#1014712 <code>
shows how to to with a command line and an editor though.

links

July 27, 2004 - 4:08 pm UTC

Reviewer: A reader

Thank you Tom!

It's helpful

July 27, 2004 - 4:14 pm UTC

Reviewer: A reader

Hi Tom,Could you pls provide the link to the documentation library for databases (9i,8i etc).Thank you

Tom Kyte

Followup  

July 27, 2004 - 7:02 pm UTC

</code> http://otn.oracle.com/documentation/index.html <code>

August 05, 2004 - 10:10 am UTC

Reviewer: Randy

Tom,
I followed your example for image_get.gif using a BFILE. I changed to l_blob from blob to bfile. I can DBMS_LOB.GETLENGTH(column) in sqlplus fine. However, on the web application is get the following error:

ORA-22289: cannot perform FILEREAD operation on an unopened file or LOB
ORA-06512: at "SYS.DBMS_LOB", line 723
ORA-06512: at "AP32ADMIN.IMAGE_GET", line 17
ORA-06512: at line 8

Any thoughts on this?

Thanks,
Randy

Tom Kyte

Followup  

August 05, 2004 - 1:06 pm UTC

fileopen it -- see the dbms_lob package.



August 05, 2004 - 3:26 pm UTC

Reviewer: Randy

RE: fileopen it -- see the dbms_lob package

That was it. Works perfect.

Thanks Tom!

August 06, 2004 - 3:02 pm UTC

Reviewer: Randy

Tom,
How does setting the mime types work with this procedure? Some files (gif, pdf, jpg) open directly within the browser, while other types (zip) can not find the target application. If I send the filename with extension (test.zip) to the browser, using htp.p('Content-Disposition: attachment; filename="test.zip"');, this works fine and I don't have assign a mime type. However, using this method prevents pdf, gif, jpg files from opening directly within the browser (client receives a file download box). I guess that I'm confused as to why the mime type association has no effect over certain file types.

Tom Kyte

Followup  

August 06, 2004 - 3:32 pm UTC

you send the apppropriate mime type for zip files is all?


application/zip

perhaps.


[tkyte@localhost tkyte]$ telnet asktom.oracle.com 80
Trying 148.87.130.149...
Connected to asktom.oracle.com.
Escape character is '^]'.
HEAD /~tkyte/10g.zip HTTP/1.0

HTTP/1.1 200 OK
Date: Fri, 06 Aug 2004 19:32:06 GMT
Server: Oracle-Application-Server-10g/9.0.4.0.0 Oracle-HTTP-Server
Last-Modified: Tue, 16 Sep 2003 11:23:20 GMT
ETag: "47519-68ac3-3f66f2a8"
Accept-Ranges: bytes
Content-Length: 428739
Connection: close
Content-Type: application/zip


August 06, 2004 - 3:35 pm UTC

Reviewer: A reader

Tried that using...

owa_util.mime_header(v_mime, false);

where v_mime = 'application/zip'.



Tom Kyte

Followup  

August 07, 2004 - 9:07 am UTC

then that would mean your browser isn't setup to handle application/zip.

if you click on </code> http://asktom.oracle.com/~tkyte/10g.zip <code>what happens -- does it "open up" or not.

there is nothing special happening here due to this being "plsql". you send a mime type, browser looks up mime type in list of types. if browser was not setup to deal with application/zip, browser punts and saves to disk.

August 09, 2004 - 11:04 am UTC

Reviewer: Randy

Yes, your link works correct in my browser.

The only thing I could find is possibly the MIME type I am using (application/zip) is not one of the registered MIME types in the web listener.

It seems the problem is with owa_util.mime_header. I could forward you a link through email if you want to see what its doing.

Thanks for responding

Tom Kyte

Followup  

August 09, 2004 - 1:32 pm UTC

if you telnet and do the head command like I did, do you see the same thing or not?

telnet to your own server
head your own procedure
see what it says.


if the mime header is different from what you put out, then you will need to add it to the http server -- so it knows it is allowed to serve it out.

August 09, 2004 - 2:55 pm UTC

Reviewer: Randy

This is what I get from the head command...

HTTP/1.1 200 OK
Date: Mon, 09 Aug 2004 18:45:03 GMT
Server: Oracle HTTP Server Powered by Apache/1.3.22 (Win32) mod_plsql/3.0.9.8.3b mod_ssl/2.8.5 OpenSSL/0.9.6b mod_fastcgi/2.2.12 mod_oprocmgr/1.0 mod_perl/1.25
Content-Length: 154551
Keep-Alive: timeout=15, max=99
Connection: Keep-Alive
Content-Type: application/zip

It seems the content-type is passed correctly to the browser. Still the OS doesn't know what to do with the zip file.

Thanks for assisting!

Randy

Tom Kyte

Followup  

August 09, 2004 - 4:10 pm UTC

sorry -- at this point -- it is purely a "browser issue" and nothing at all to do with plsql/mod_plsql/etc.

Displaying PDF on a browser using asp/jsp

August 10, 2004 - 9:49 am UTC

Reviewer: Urukhai

Dear Tom, I have pdf files stored on my DB, but I want to display it on a web browser (IE) using asp. Can you help me

Tom Kyte

Followup  

August 10, 2004 - 10:05 am UTC

"using asp" = "askbill@microsoft.com"?

if you want to use mod_plsql -- ctl-f and search for wpg_docload

if you want to use java -- use the same technique shown here (recode the plsql into java/jsp)

if you want to use asp, it would be the same sort of technique but a different, foreign (to me) language.

display JPG file on html db environment

August 23, 2004 - 1:41 am UTC

Reviewer: Winston from Canada

I used load_a_file to store a JPG file into the BLOB and tried image_get in Oracle 9.2.0.4.0 with HTML DB installed. I believe NLS lang /character set shouldn't be an issue since web server and db server are on the same linux machine. However I still got the error from Firefox 'The image “</code> http://my_hostname:7776/pls/neis/image_get.gif?P_ID=1” <code>cannot be displayed, because it contains errors.'.

What could go wrong? or is there any new way to display jpg file stored in a blob since we have HTML DB environment?

Thanks!

Tom Kyte

Followup  

August 23, 2004 - 7:31 am UTC

ctl-f for wpg_docload, use that technique, much superior.

Displaying the PDF file

August 23, 2004 - 3:18 pm UTC

Reviewer: Robin from Pensacola, FL USA

I am not using the iAS. Instead, I am going straight to the server with a procedure. Is there an alternate way you would call the file other than what you suggested:

<a href=/dcd/owa/image.pdf?p_id=55>Click Me</a>

Thanks for any assistance you can offer.

Tom Kyte

Followup  

August 23, 2004 - 3:25 pm UTC

if you are not using mod_plsql, how are you "going straight to the server with a procedure"?

Re: Displaying the PDF file

August 23, 2004 - 3:33 pm UTC

Reviewer: Robin from Pensacola, FL USA

I appreciate your prompt response. Thank you. I am using the procedure in an ASP page.

Tom Kyte

Followup  

August 23, 2004 - 3:36 pm UTC

right -- but what is RUNNING said stored procedure???? ASP doesn't know how to run plsql, mod_plsql does.

so, back to you -- what is actually turning the URL into a database connection, setting up the OWA environment, calling the procedure, then calling the get_page routine to properly dump the data back....... not ASP.

Re: Displaying the PDF file

August 24, 2004 - 9:28 am UTC

Reviewer: Robin from Pensacola, FL USA

I am connecting to the database through a connection string using ADODB. I am relatively new to oracle other than accessing to insert, retrieve data to an ASP page.

I am trying to retrieve a file (BLOB) that's stored in the database and open it in its native language/application. Ex. retrieve/open a *.doc file in word.

Tom Kyte

Followup  

August 24, 2004 - 10:16 am UTC

i don't understand how you can be running a procedure in that fashion, that uses htp/htf type functions WITHOUT using mod_plsql.

I need to understand that *first* before I can say anything. are you really able to run a procedure like:

proceudre p
is
begin
htp.p( 'hello world' );
end;


in your environment and get output? if not, i won't be able to tell you how to do this.

Display BLOB object...

August 26, 2004 - 4:54 am UTC

Reviewer: sasanka from srilanka

Hi,
i have a table which stores Mail attachments in a BLOB column.In the blob colum i store .pdf,.doc,.txt etc.. I use this table to do a text search and output the matching files
first i want to display the search results (which will display as link and after clicking a specific link should be able to display the file.
i am relativly new to oracle database. can u suggest a suitable way ?

thanx.
sasanka

Tom Kyte

Followup  

August 26, 2004 - 9:59 am UTC

ctl-f for wpg_docload on this page and there you go.

Display BLOB

August 27, 2004 - 7:12 am UTC

Reviewer: sasanka from sri lanka

HI tom,

i have created a procedure to download the blob in to the browser

create or replace procedure download(attachment_id_ IN NUMBER,message_id_ IN NUMBER)
as
l_lob attachments.attach_file%type;

begin
select a.attach_file
into l_lob
from attachments a
where a.message_id = message_id_
and a.attachment_id =attachment_id_ ;

owa_util.mime_header('application/pdf');

owa_util.http_header_close;
wpg_docload.download_file(l_lob);

end;

But how can i call this procedure in a JSP file ...
IN psp(PLSQL server pages) , i have seen that they have called the procedure in a HREF but how it can be done in JSP
pls explain..

regards
sasanka

Tom Kyte

Followup  

August 27, 2004 - 7:54 am UTC

in a jsp you would:

<a href=</code> http://server/dad/download?attachment_id_=12345&message_id_=532 > <code>
click me
</a>


? a jsp is just a way to generate an html page, you "call" a mod_plsql procedure via url from anything you want.

Store & Retrieve File

September 15, 2004 - 6:52 am UTC

Reviewer: Tanweer Qasim from USA

I have Oracle Database in window 2000 server and the other  node is Window 2000 Professional and wants to store & retrieve file(e.g : .doc, .txt, .PDF etc…..) into the database

SQL*Plus: Release 8.0.6.0.0
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> exec mydocs.list('\aaa\aaa.doc');
PL/SQL procedure successfully completed.

When I execute the Following Query then I got the error 

SQL> exec mydocs.load('\aaa\aaa.doc',1);
BEGIN mydocs.load('\aaa\aaa.doc',1); END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The filename, directory name, or volume label syntax is incorrect.
ORA-06512: at "SYS.DBMS_LOB", line 672
ORA-06512: at "ARCH.MYDOCS", line 39
ORA-06512: at line 1

The Code is given below which I use ………………

CREATE TABLE my_docs 
        (doc_id   NUMBER, 
        bfile_loc BFILE,
        doc_title VARCHAR2(255),
        doc_blob  BLOB DEFAULT EMPTY_BLOB() );



CREATE OR REPLACE PACKAGE 
          mydocs 
      AS
 PROCEDURE doc_dir_setup;
 PROCEDURE list   (in_doc    IN VARCHAR2);
 PROCEDURE load   (in_doc    IN VARCHAR2,
                   in_id     IN NUMBER);
 PROCEDURE search (in_search IN VARCHAR2,
                   in_id     IN NUMBER);
END mydocs;
/
CREATE OR REPLACE PACKAGE BODY 
          mydocs 
      AS
vexists      BOOLEAN;
vfile_length NUMBER;
vblocksize   NUMBER;

PROCEDURE doc_dir_setup IS
 BEGIN
  EXECUTE IMMEDIATE 
  'CREATE DIRECTORY DOC_DIR AS'||
  '''"\jkoopmann\Oracle"''';
END doc_dir_setup;

PROCEDURE list (in_doc IN VARCHAR2) IS
BEGIN
      UTL_FILE.FGETATTR('DOC_DIR',
                        in_doc,
            vexists,
            vfile_length,
            vblocksize);
      IF vexists THEN
        dbms_output.put_line(in_doc||'    '||vfile_length); 
      END IF;
END list;

PROCEDURE load (in_doc IN VARCHAR2,
                in_id  IN NUMBER) IS
temp_blob         BLOB := empty_blob();
bfile_loc         BFILE;
Bytes_to_load     INTEGER := 4294967295;
BEGIN
  bfile_loc := BFILENAME('DOC_DIR', in_doc);
  INSERT INTO my_docs (doc_id, bfile_loc, doc_title) 
         VALUES (in_id, bfile_loc, in_doc);
  SELECT doc_blob INTO temp_blob 
    FROM my_docs WHERE doc_id = in_id 
     FOR UPDATE;
  DBMS_LOB.OPEN(bfile_loc, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.OPEN(temp_blob, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADFROMFILE(temp_blob, bfile_loc, Bytes_to_load);
  DBMS_LOB.CLOSE(temp_blob);
  DBMS_LOB.CLOSE(bfile_loc);
COMMIT;
END load;

PROCEDURE search (in_search VARCHAR2,
                  in_id     NUMBER) IS
lob_doc        BLOB;
Pattern        VARCHAR2(30);
Position       INTEGER := 0;
Offset         INTEGER := 1;
Occurrence     INTEGER := 1;
BEGIN
  Pattern    := utl_raw.cast_to_raw(in_search);
  SELECT doc_blob INTO lob_doc
    FROM my_docs WHERE doc_id = in_id;
  DBMS_LOB.OPEN (lob_doc, DBMS_LOB.LOB_READONLY);
  Position := DBMS_LOB.INSTR(lob_doc, Pattern, Offset, Occurrence);
  IF Position = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Pattern not found');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The pattern occurs at '|| position);
  END IF;
  DBMS_LOB.CLOSE (lob_doc);
END search;

BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
END mydocs;
/


 

Tom Kyte

Followup  

September 15, 2004 - 9:31 am UTC

the oracle database running on server1 can only see filesystems available to server1 and only file systems that the user the database is running as "sees"

seems to me that \aaa\ is a directory on "server2"

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>

How to Disply Store images ?

September 16, 2004 - 9:42 am UTC

Reviewer: parag jayant patankar from India

Hi,

I am trying following steps for exporting image to temp directory ( Oracle 9i on NT )

1. connect / as sysdba
2. create or replace directory imagedir as 'c:\temp';
3. grant read on directory imagedir to parag;
4. call dbms_java.grant_permission 'PARAG','java.io.FilePermission','c:\temp\*','WRITE');
5. call dbms_java.grant_permission'ORDSYS','java.io.FilePermission','c:\temp\*','WRITE');
6. conn parag/parag@test
7. create or replace procedure image_blob_export (source_id number, filename varchar2) as
img_blob BLOB;
ctx raw(64) := null;
begin
select image_blob into img_blob from image_blob_table where id = source_id;
ORDSYS.ORDImage.export(ctx, img_blob, 'FILE', 'IMAGEDIR', filename);
end;
/
8. call image_blob_export(3, 'p1.jpg');

It is giving me following error

call image_blob_export(3, 'p1.jpeg')
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "ORDSYS.ORDSOURCE", line 354
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "ORDSYS.ORDIMAGE", line 404
ORA-06512: at "PARAG.IMAGE_BLOB_EXPORT", line 6

My Table structure is

18:58:34 atlas:atq1p1@spren012> desc image_blob_table
Name Null? Type
----------------------------------------- -------- ------------
ID NOT NULL NUMBER
WIDTH NUMBER(38)
HEIGHT NUMBER(38)
CONTENTLENGTH NUMBER(38)
MIMETYPE VARCHAR2(20)
IMAGE_BLOB BLOB

Other Details are

18:47:24 atlas:atq1p1@spren012> select id, height, width, mimeType, contentLength from image_blob_table
18:47:32 2 order by id
18:47:35 3 /

ID HEIGHT WIDTH MIMETYPE CONTENTLENGTH
---------- ---------- ---------- -------------------- -------------
1 591 385 image/jpeg 202627
2 599 385 image/jpeg 182565
3 59 38 image/jpeg 1470
4 59 38 image/jpeg 1385
5

Kindly suggest me urgently

best regards
pjp

Tom Kyte

Followup  

September 16, 2004 - 10:17 am UTC

I'd start by getting an exception handler in there to catch the list of named exception export() is documented to raise and see which one is happening.




Java Permissions were not set properly

September 16, 2004 - 11:35 am UTC

Reviewer: parag jayant patankar

Hi Tom,

Thanks for your help. Problem is solved. I was getting error because java permissions for directory were not set properly. I had refered following metalink documnet for solving the problem.

</code> http://metalink.oracle.com/metalink/plsql/ml2_gui.startup <code>

Tom,

In Oracle 9i will you pl explain how to send saved BLOB files with attachments by email ?

thanks & best regards
pjp


Tom Kyte

Followup  

September 16, 2004 - 11:42 am UTC

email blob attachments


search.

September 21, 2004 - 10:58 am UTC

Reviewer: Pauline from NY.U.S.A

Tom,
I followed your example to created demo table :
SQL> desc demo;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 THEBLOB                                            BLOB

and uploaded one file called 01285816.rtf to the demo table.

After upload, query

SQL> select id, dbms_lob.getlength(theblob) from demo;

        ID DBMS_LOB.GETLENGTH(THEBLOB)
---------- ---------------------------
         1                      106401

Also I created mimetypes table as:

SQL> desc mimetypes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(20)

insert into mimetypes values(1,'image/rtf');

and create package image_get as:

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 4096;
    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/rtf' );

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

For downloading image file to URL, I did:

SQL> create or replace procedure download(p_file_id demo.id%type)
  2  as
  l_lob demo.theBlob%type; l_mime mimetypes.name%type;
  3    4  begin
  5    select d.theBlob, m.name
  6    into l_lob, l_mime
  7    from demo d, mimetypes m
  where d.id = p_file_id
  8    9    and d.id= m.id;
  owa_util.mime_header(l_mime, false);
 10   11    owa_util.http_header_close;
  wpg_docload.download_file(l_lob);
 12   13  end;
 14  /

Procedure created.

SQL> exec download(1);

PL/SQL procedure successfully completed.

Now what is the exact path for URL to display the image file. I only know
the web server address for this database is

10.8.2.112:12200/

I don't know what is the final step to see the image file from URL. 

Your help will be greatly appreciated.

 

Tom Kyte

Followup  

September 21, 2004 - 11:16 am UTC

what DAD do you have setup.

you need to have a DAD setup, so that mod_plsql knows how to connect to database.

then the url will be like:

http: //hostname/pls/DADA/download?p_file_id=1



September 23, 2004 - 4:13 pm UTC

Reviewer: Pauline from NY,U.S.A.

Tom,
Thanks for the response. We don't have DAD setup yet.
In your comment,
/***
then the url will be like:

http: //hostname/pls/DADA/download?p_file_id=1

***/

'DADA' is the real name or it should be some DAD_NAME?





Tom Kyte

Followup  

September 24, 2004 - 9:39 am UTC

sorry, extra A in there /DAD/ is what i meant

you would put the DAD name in there, see my url, the DAD is "ask"

September 24, 2004 - 12:13 pm UTC

Reviewer: Pauline from NY,U.S.A.

Thanks for reply. Now I see.

extracting blob into XML as binary

November 03, 2004 - 5:32 pm UTC

Reviewer: S.S. from US

Hi Tom,

Would like to extract the blob into an xml document for another application to decode appropriately.

Thanks.

Tom Kyte

Followup  

November 05, 2004 - 11:23 am UTC

ok? go ahead i guess?

very nice

November 11, 2004 - 1:31 am UTC

Reviewer: ram from india

very nice

bfilename Directory

November 13, 2004 - 3:10 pm UTC

Reviewer: robert from CT

package to be recompiled under a different user:

so if I had this line:

l_bfile := bfilename( 'MY_FILES', 'oracle_image_map.gif' );

Can I just modify it like this w/o having to create the directory for the new user ?

l_bfile := bfilename( 'OLD_USER.MY_FILES', 'oracle_image_map.gif' );

Tom Kyte

Followup  

November 13, 2004 - 6:43 pm UTC

directories are a "public" thing, they are not owned by anyone (well, except by "SYS")

JPEG corruption

November 15, 2004 - 4:33 pm UTC

Reviewer: Paul Poley from Oklahoma City, OK

Hi Tom,

You said if the character set of the client (the webserver) is not exactly the same as the database, the character set conversion will corrupt the image.

Our database & webserver are separate machines & I'm assuming the character sets are not the same because my JPEG page produces a broken link. However, if I give the file ID parameter a value that is not in the database, I get a "no data found" exception. I know the file is there & is correct (CRC check). How to I send the binary data to the browser without it being corrupted by varying character sets?

Tom Kyte

Followup  

November 15, 2004 - 9:16 pm UTC

ctl-f for wpg_docload

and use that technique -- no issues then.

I want to print the output "hello World" on jsp page through a PL/SQL procedure.

November 25, 2004 - 3:50 am UTC

Reviewer: A reader

I want to print the output "Hello World" on JSP page written in PLSQL page.





Tom Kyte

Followup  

November 25, 2004 - 9:47 am UTC

while technically possible, one must ask "why"

You would basically have to re-invent MOD_PLSQL in java first.

Try using a tag which supports "src="

November 26, 2004 - 1:47 pm UTC

Reviewer: andrew from ca, usa

Maybe trying a tag which supports "src=" to embed some mod_plsql output into a JSP page would work. This snippet embeds an image sourced from a seperate call. Absolute paths probably needed because of 2 different service handlers.

CREATE OR REPLACE PROCEDURE show_pg_embed_img
AS
BEGIN
htp.p('<HTML><BODY><HEAD><TITLE>Text and image</TITLE></HEAD>');
htp.p('Below is an img<br>');
htp.p('<img src="show_img" alt="Image here" border="0"><br>');
htp.p('Above is an image<br></BODY></HTML>');
END;
/


Complete guide to blobs

December 05, 2004 - 7:59 pm UTC

Reviewer: mark from LA, USA

Hi Tom,

I have read your page </code> http://64.233.183.104/search?q=cache:C4vAdmqQo_4J:asktom.oracle.com/pls/ask/f%3Fp%3D4950:8:11131473361978280247::NO::F4950_P8_DISPLAYID,F4950_P8_B:232814159006,Y+putting+a+database+on+the+web+%2Boracle&hl=en&start=10 <code>page with great interest ..

However being a bit of a Oracle(tm) newbie, I can not see a comprehensive guide to say setting up lobs and retreival of lobs .. Is it possible to provide a defact standard ..
which covers the complete .. transaction -- entering and dl
etc, i beleive that this would stop a few of the other enquiries which i see here ..

Thanks in advance for all the work you have done.

//Ma

Tom Kyte

Followup  

December 05, 2004 - 8:43 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96591/toc.htm <code>

it was already written :)

Images questions

January 26, 2005 - 5:06 pm UTC

Reviewer: Oracle anorak from London

Hi Tom

Using the methods you've outlined it was pretty straight forward to upload images to the database, and then serve them up through a web browser. To further the usefulness of the code sample I was wondering if you had a method for determining the dimensions of the images being uploaded in to the database. Obviously these could be manually supplied in a procedure call, but this isn't really practical when there are thousands of images being uploaded of various dimensions.

The reason this is important is that it is generally considered good form to output the image dimensions in a HTML image tag, eg

<img src="/image.gif?p_id=1" height="100px" width="20px" />

Doing so makes the pages render faster and is required by the html/xhtml standards.

Also to expand on one of your follow up responses above setting the height and width attributes of an image tag to scale the image may make the image appear smaller/larger, but it won't alter the size of the image being downloaded by the browser. The full sized image (say 50kb) will be downloaded irrespective of whether the image dimensions are set to 16 x 16 or 160 x 160.

Tom Kyte

Followup  

January 27, 2005 - 7:34 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a88786/toc.htm <code>

intermedia can do that, and scale them, create thumbnails, convert image types and so on. feature of standard edition and up.

(hope you are using the wpg_docload method added over time to this page, much easier, much faster)

Retrieving BLOB to a Web Broser

February 09, 2005 - 11:39 am UTC

Reviewer: Eduardo from Spain

Dear Tom,

I´ve followed all the examples to retrieve a BLOB stored on a table and view it on a web broser (on my case, IE) but I couldn´t do it. I´ve created a table like

create table afotos
(id number primary key...

and also, I´ve used the pl/sql program to stored an image on it and I´ve created 2 pages on HTML language. One of them have the tag <IMG src="getPhoto.asp?id=1...>
and the other have the following sentences in order to connect to Oracle:

Response.Expires = 0
Response.Buffer = TRUE
Response.Clear
Response.ContentType = "image/jpg"

Const ORAPARM_INPUT = 1
Const ORATYPE_NUMBER = 2

Dim strID
strID = 1

Dim OraSession
Dim OraDB
Dim setPhotos
Dim aFoto

Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
Set OraDB = OraSession.OpenDataBase("PROSERVER", "BOUSER/MMC", 0)

Dim strSQL
strSQL = "select PHOTO from AFOTOS where ID = :photo_id"
OraDB.Parameters.Add "photo_id", strID, ORAPARM_INPUT
OraDB.Parameters("photo_id").ServerType = ORATYPE_NUMBER
Set setPhotos = OraDB.CreateDynaset(strSQL, 0)
OraDB.Parameters.Remove("photo_id")

aFoto = rsphotos("PHOTO")

Response.BinaryWrite aFoto
Response.End

setPhotos.close
Set setPhotos = nothing

OraDB.close
Set OraDB = nothing

Set OraSession = nothing

The mistake: the image doesn´t appear on the first page.

Please, can you help me?

Thanks a lot

Tom Kyte

Followup  

February 09, 2005 - 2:56 pm UTC

not with that language, I've never come close to programing in it, don't really run much software that uses any OS's that can run/compile that language

sorry.....



March 03, 2005 - 2:56 pm UTC

Reviewer: Yuan from Newark, NJ USA

I'm having the same problem as "A Reader" earlier in this thread who you didn't believe, but I hope my code snippet shows that the file does indeed exist.

ESL-CHATDV>declare
2 lbfile BFILE;
3 myfile UTL_FILE.file_type;
4 mybuff VARCHAR2(32000);
5 begin
6 myfile := UTL_FILE.fopen('D_DLVR_FILE', 'UPS_QVD_0223.XML', 'r');
7 utl_file.get_line(myfile, mybuff, 32000);
8 dbms_output.put_line(mybuff);
9 lbfile := bfilename('D_DLVR_FILE', 'UPS_QVD_0223.XML');
10 DBMS_LOB.OPEN(lbfile);
11 end;
12 /
<?xml version="1.0" encoding="UTF-8"?>
declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
ORA-06512: at "SYS.DBMS_LOB", line 672
ORA-06512: at line 10

As you can see, I was able to open and read the file using UTL_FILE, but not with DBMS_LOB. What am I doing wrong?

Tom Kyte

Followup  

March 03, 2005 - 6:57 pm UTC

it was not a matter "of not believing them", it was a matter of the fact that:

got an error:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.

ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 9

their error message said something very different from yours (indicating that no- it was not a matter of not believing, it was a matter that on their system the file did not exist)

So, you are having some other problem -- not the same problem. Look at your error message, it is missing the extra text that describes the problem for some reason.


tell me, is that a UNC mapped drive? tell us more.

March 04, 2005 - 10:15 am UTC

Reviewer: Yuan from Newark, NJ USA

The file is located in a shared folder on my machine running Windows 2000 Pro. Oracle 9.2.0.5.0 is running on an NT server. Our DBA mapped W:\ on the server to my machine, but I'm not sure that makes a difference. I've tried creating a directory object on "W:", "W:\", "W" but none of them work with DBMS_LOB or UTL_FILE. I hope this answers your question.

I also noticed that UTL_FILE doesn't care about using the directory object or not:

ESL-CHATDV>declare
2 lbfile BFILE;
3 myfile UTL_FILE.file_type;
4 mybuff VARCHAR2(32000);
5 begin
6 myfile := UTL_FILE.fopen('D_DLVR_FILE', 'UPS_QVD_0223.XML', 'r');
7 utl_file.get_line(myfile, mybuff, 32000);
8 UTL_FILE.fclose(myfile);
9 dbms_output.put_line('USING DIRECTORY OBJECT: ' || mybuff);
10 myfile := UTL_FILE.fopen('\\MyMachine\Miscellaneous', 'UPS_QVD_0223.XML', 'r');
11 utl_file.get_line(myfile, mybuff, 32000);
12 UTL_FILE.fclose(myfile);
13 dbms_output.put_line('NOT USING DIRECTORY OBJECT: ' || mybuff);
14 lbfile := bfilename('\\MyMachine\Miscellaneous', 'UPS_QVD_0223.XML');
15 DBMS_LOB.OPEN(lbfile);
16 end;
17 /
USING DIRECTORY OBJECT: <?xml version="1.0" encoding="UTF-8"?>
NOT USING DIRECTORY OBJECT: <?xml version="1.0" encoding="UTF-8"?>
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 672
ORA-06512: at line 15

I'd like to continue to find out what I'm doing wrong here, but an imminent deadline causes me to ask for a possible temporary workaround. Is there a way to make the file data type that UTL_FILE.FOPEN returns into a BFILE data type that I need for this to work:

FUNCTION Get_File_As_CLOB(pivDir VARCHAR2, pivFileNm VARCHAR2) RETURN CLOB IS

lbfile BFILE;
lclob CLOB;

BEGIN
lbfile := BFILENAME(pivDir, pivFileNm);
DBMS_LOB.OPEN(lbfile);
DBMS_LOB.CREATETEMPORARY(lclob, TRUE, DBMS_LOB.SESSION);
DBMS_LOB.LOADFROMFILE(lclob, lbfile, DBMS_LOB.GETLENGTH(lbfile));
DBMS_LOB.CLOSE(lbfile);
RETURN lclob;
END Get_File_As_CLOB;

Tom Kyte

Followup  

March 04, 2005 - 11:05 am UTC

see
Note 261822.1

on metalink.

Not Using UNC

March 07, 2005 - 9:35 am UTC

Reviewer: Yuan from Newark, NJ USA

Thanks for pointing me to the cause of the problem. Metalink said that the resolution is not to use UNC notation. What are the alternatives?

Tom Kyte

Followup  

March 07, 2005 - 3:03 pm UTC

Use UNIX/Linux ;)



Not Using UNC Continued

March 07, 2005 - 10:31 am UTC

Reviewer: Yuan from Newark, NJ USA

OK, we copied the file onto the server and everything works now using 'D:\TEMP', but I don't think we would want to copy the file local every time. Could it be permissions? I don't think so because UNC worked for UTL_FILE.

Tom Kyte

Followup  

March 07, 2005 - 3:56 pm UTC

dbms_lob was the issue with unc's

Linux vs Windows

March 08, 2005 - 9:04 am UTC

Reviewer: Yuan from Newark, NJ USA

I'd love to use Linux, but that's not up to me.

<quote>dbms_lob was the issue with unc's</quote>

Yes, I know. I was asking if permissions could have explained the failure when using the server's mapped network drive to my local machine in my Directory object. I was then giving my own 2 cents saying that I don't believe it was a permissions issue since I don't see why it wouldn't have been a problem using UNC as well. Ah well. We might just wind up putting the files onto the server, then deleting them once loaded into db.

Tom Kyte

Followup  

March 08, 2005 - 10:52 am UTC

dbms_lob and unc do not work together by design as detailed in that note.

if you use a mapped drive (z:\....) it should work.

Having Some Problem

March 17, 2005 - 4:01 am UTC

Reviewer: bhavesh patel from Ahemdabad,Gujrat,India

hi Tom,
I want to insert a .gif into table..
I follw exactly urs example:
code is look like that:
create or replace
directory MY_FILES
as '\\Server\Files'

Directory Created.

Then,
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values (101, 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;

But encounter a problem...
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at line 8

it seems file is found..but still unable to open..
What is solution of this problem?? I am newbie in Oracle..
So pls help me.
Thanks in Advance.


Tom Kyte

Followup  

March 17, 2005 - 8:38 am UTC

search for 22288 on this page and read about UNC not being useful with dbms_lob

March 23, 2005 - 10:09 am UTC

Reviewer: Brandon from IL USA

Tom, can these codes be used with coldfusion?

Thanks.

Tom Kyte

Followup  

March 23, 2005 - 6:02 pm UTC

does cold fusion support plsql and sql?

question about storing files in database

May 19, 2005 - 3:54 pm UTC

Reviewer: Xin Hu from USA

Hi Tom,

I have a question regarding storing files in the database as BLOB data type.

If the user accidently deletes a file or one file is corruptted, it will be much more difficult to restore/recover that single file if the file is stored in the database than if the file is stored on the O/S level.

Any comments on that?

Thanks!

Tom Kyte

Followup  

May 19, 2005 - 4:26 pm UTC

umm, no.

flashback for example. (flashback query, transaction history, table, database, ....)

database recovery for example.


and there is no such thing as "rollback" in a file system remember.


things are infinitely recoverable in a database. that is one of the reasons you use them.

question about storing files in database

May 19, 2005 - 4:55 pm UTC

Reviewer: A reader

In my case, I have 1 TB PDF files. If I put them all in database, image how practical to do database recovery just for restoring/recovering one single PDF file if needed.
I cannot do export for it is too big. I have to rely on database media recovery (incomplete) and since they are all in one tablespace (say we don't user partition option), it will be restoring 1TB tablespace to just try to recover one single PDF file.

On the other hand, if the files are on the O/S level, weekly full backup and daily incremental backup will make it very easy to recove the single file in this case.
Am I right here?

Thanks!

Tom Kyte

Followup  

May 20, 2005 - 6:59 am UTC

imagine that you can do block level recovery with the database.

imagine that you have the ability to partition in the database.

imagine that you have the ability to select * from table as of 5 minutes ago (flashback query).

imagine that the metadata in the database about the file (the structured information) can be recovered to the same point in time as the file easily.



no, imagine all you have is a filesystem.... (just for starters).

if this data means anything to you, a database (in my opinion) is the only place to put them. We have 10's of terabytes in a single "database file system" here at oracle -- it works for 45,000 of us.

question about storing files in database

May 20, 2005 - 8:45 am UTC

Reviewer: Xin hu from USA

Tom,
Thank you for the answer. We are not planning to use partition feature because of the license cost. So if we put 1TB PDF files in a single table in the database, we cannot do full export everyday because it is too big. By just using the hot or cold backup, can you advice what is the best way you think to recover single PDF file (actually it is a record in that table in the database) if the file (that record) is accidently deleted or if the data block of that file is corrupted? Point-in-time recovery will require to restore the whole tablespace, which is not practical here because of the size.
Your advice is very much appreciated!

Tom Kyte

Followup  

May 20, 2005 - 10:28 am UTC

you backup databases, you do NOT use export.

hot would be the only thing to consider.

flashback. you have flashback (i keep saying that over and over.... flashback)

and you have block level recovery.

question about storing files in database

May 20, 2005 - 11:01 am UTC

Reviewer: xin hu from USA

For flashback table, it has the time limit (no more than 5 days earlier) and we might need restore a file which was deleted say couple of weeks ago.

For flashback database , it let us flashback more but it would affect too much other things especially if we just need to recover one single file, and there is no easy way to 'roll forword' after 'flashback'.

For hot backup, if only one record in that PDF table is accidently deleted, we have no easy way to restore or recover that record by using point-in-time recovery. Again we are not using partition, so we will have 1TB table on a single tablespace. To restore the whole 1TB tablespace to just recover one record is too much here.

Please advice,

Thanks!

Tom Kyte

Followup  

May 20, 2005 - 6:30 pm UTC

and you are going to keep copies of all changes to files, hmmm.

what if user says "i need the file the way it looked at 12pm 2 weeks ago"

you backed it up 2 weeks and 1 day ago, and 1 week 6 days ago (using a file system)

It was updated 50 times that day.

Please explain how a file system can even begin to accomplish that.


And if you take 5 days to discover "opps, file gone", well.....


You have my advice, you want data protected, secure, manageable, restored to a point in time -- you be using a database.



A few things here

May 20, 2005 - 11:24 am UTC

Reviewer: Paul from Ottawa, ON, CA

Personally I find Oracle's backup and recovery tools and methodologies to be 2nd to none. Tom has provided several out of the box ways of recovering information both in the case of record deletion and disaster recovery.
If those don't work you may actually need to define some requirements and code it yourself.  Especially this one, which seems to have a valid business requirement behind it.


Create an archive table.
Put a before delete trigger on the table to move the data to the archive table.

In the spirit of asktom (and his blog) 

SQL> create table source (x number, b blob);

Table created.
SQL> create table archive (x number, b blob,created_date date);

Table created.

SQL>
SQL> create or replace trigger archive before delete on source
  2  for each row
  3  begin
  4    insert into archive(x,b,created_date) values (:old.x, :old.b,sysdate);
  5  end;
  6  /

Trigger created.

I have a table with some small blobs

SQL> insert into source (select rownum, content from (select content from ps_txn
));

SQL> commit;

Commit complete.

SQL> select count(*) from archive;

  COUNT(*)
----------
         0
Nothing here

SQL> select count(*) from source;

  COUNT(*)
----------
         8
Something here

SQL> delete from source where x=8;

1 row deleted.


SQL> select x, dbms_lob.getlength(b), to_char(created_date,'dd-mon-yyyy hh24:mi'
) from archive;

         X DBMS_LOB.GETLENGTH(B) TO_CHAR(CREATED_D
---------- --------------------- -----------------
         8                   484 20-may-2005 11:18


Then decide how long you need the retention for, either by date, or set a hard limit to X number of GB.

If it is a month, then that is a business requirement that has a cost for some disk space of how much space you need to hold that table.

Setup a job to purge the table.
Create a procedure to retreive the document *undelete* it from archive.

There are lots of ways to do things, some of them require creating or customizing a process to handle it.





 

Getting error

May 25, 2005 - 6:25 am UTC

Reviewer: A reader

Hi  Tom

    Good morning . I was trying to display an image stored in a table(IMAGES) . But I am getting this error . Kindly help me to rectify . Sorry for my unknown mistake (if there is any) .

Thanks for your time . Have a nice day .

/******************************************************/

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE     DISPLAY_IMAGE (P_ID IN IMAGES.IMAGE_ID%TYPE )IS
  2      L_LOB    CLOB;
  3      L_AMT    NUMBER DEFAULT 4096;
  4      L_OFF    NUMBER DEFAULT 1;
  5      L_RAW    RAW(4096);
  6  BEGIN
  7      SELECT IMAGE INTO L_LOB
  8      FROM   IMAGES
  9      WHERE  IMAGE_ID = P_ID;
 10      OWA_UTIL.MIME_HEADER( 'IMAGE/JPEG' );
 11          BEGIN
 12             LOOP
 13                DBMS_LOB.READ( L_LOB, L_AMT, L_OFF, L_RAW );
 14                HTP.PRN( UTL_RAW.CAST_TO_VARCHAR2( L_RAW ) );
 15                L_OFF := L_OFF+L_AMT;
 16                L_AMT := 4096;
 17             END LOOP;
 18          EXCEPTION
 19             WHEN NO_DATA_FOUND THEN
 20                NULL;
 21          END;
 22  EXCEPTION WHEN OTHERS THEN
 23      DBMS_OUTPUT.PUT_LINE('Eror encountered ' || SQLCODE ||' :-> '|| SQLERRM );
 24* END;
SQL> 
SQL> /

Procedure created.

SQL> exec DISPLAY_IMAGE(1);
Eror encountered -6502 :-> ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error

PL/SQL procedure successfully completed.

/******************************************************/

Thanks
Debashis 

Tom Kyte

Followup  

May 25, 2005 - 7:52 am UTC

why are you using a clob? a clob would be inappropriate to store binary image data?

How can display image in a browser.. help please

May 25, 2005 - 7:19 am UTC

Reviewer: A reader

Hi tom

Actually i am from database background and new to web technology . I can't get your point to display an image in an web browser . I have done everything you've shown i.e. database side is done . I have stored image in table(as CLOB) .But can you please tell me in detail , the way(step by step) to display that image in a web browser ?

Thanks in advance .

Thanks
Anjan

Tom Kyte

Followup  

May 25, 2005 - 8:12 am UTC

you cannot store an image in a clob, that corrupts them.

you MUST store them in a blob (binary data)

and then ctl-f for wpg_docload on this page to see the easiest way today..

pdf on the display

May 26, 2005 - 8:42 am UTC

Reviewer: Joe_hun from Hungary

I wrote the procedure ,and the procedure put the pdf,doc,xls and gif files in the demo table. I done all same in the example.
But i have a question: how can i put the pdf,doc,xls etc. on the display?

Sorry, but i'm novice.
Thanks

Tom Kyte

Followup  

May 26, 2005 - 9:56 am UTC

do you have a web server running mod_plsql setup and ready to go.

Pdf on display

June 02, 2005 - 7:36 am UTC

Reviewer: Joe_hun from Hungary

Thanks for previous answer.

I ran the procedure on web server(10g), but the message is the following:
"Service Temporarily Unavailable
The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later."
Calling: </code> http://server:9999/pls/dad_name/blob_vissza.kep?p_azon=1 <code>

What is to be done?

Thanks

displaying PDF with after megrging table 's coulmns

June 07, 2005 - 8:33 am UTC

Reviewer: Athar Parvez from Lucknow, India

Hello Tom,

I am creating a mail merge kind of application. I have stored PDF and WORD templates as a blob in a table. When user access a PDF template, I want to display the PDF after adding some content from the table's. Just like in MS Word or Wordperfect, we have mail merge , I want to perform the same thing from database.

Could you give me some idea about how to go for this, some code will be highly appreciated, code about how to merge table's data with the PDF stored in the dataabse.

Regards


Tom Kyte

Followup  

June 07, 2005 - 8:53 am UTC

sorry, don't know much at all about merging into PDF's, sort of something that would happen outside the database probably.

STORING AND DISPLAYING PDF FILES-9.0.1/D2K

June 30, 2005 - 2:20 am UTC

Reviewer: Rajesh KR from INDIA

Hi Tom,

First of all let me beg for your pardon if i am breaking your rules regarding a TRUE FOLLOWUP. I have gone through the entire thread and i am able to successfully implement only the half part of the solution. I am handicapped for the fact that the version i am using is 9.0.1 in Unix and the front end is D2K(client/server).

The problem actually is i am not able to display using dbms_lob.read.

Hence before i complicate further myself i would kindly like to have a solution as per my db/application scenario is concerned.

So Tom, for my 9.0.1 db with Unix and D2K frontend,
Please help me out to store the pdf files into tables and thereafter extract the data using sql, and display the pdf files through any browser. The displaying of pdf shall be based on some unique file naming based on some sequence numbering or some unique numbering related to the data based on which the pdf report is generated.

I hope this is not a new question! If yes then pls ignore this and when you start responding to new questions, please respond.

Lots of thanks in advance.

Regards

RajeshKR


Tom Kyte

Followup  

June 30, 2005 - 9:28 am UTC

sorry, I haven't used forms in over a decade, if you were using a web server and mod_plsql, this would apply here.

try the forums on otn.oracle.com, there is a forms forum

Question about uploading images to the server table

June 30, 2005 - 9:10 am UTC

Reviewer: KayTecK from Poland

Hi.
I created table, and even procedure to insert and read an image from the table and display it on my site (GREAT THX FOR THIS TEXT).

but i have another problem.
what kind of procedure can send a file from my host computer (f.e. from c:/image.gif) to server's table?

i'd like to send a gif file from any computer that i turn on my web page not.
i tried an ftp connection firs but it was downloading files only from local server's filesystem ('MY_FILES' dir) to ftp dir.

how to make a procedure to read from my hdd not server's??
or how else can i do that??

greetings

Tom Kyte

Followup  

June 30, 2005 - 9:52 am UTC

you cannot make a stored procedure read your disk, that would be called "a virus".


you need to have a client application get the image up off of your disk and sent over the network to the database.

A webbrowser can do that, for example.

Question about uploading images to the server table

June 30, 2005 - 10:13 am UTC

Reviewer: KayTecK from Poland

ok, but how to make web browser to send a file to a server??
i found a <imput type="file"> field but i completly don't know how to use it...

i have ftp server running on my server...

i want to make a web aplication to upload images to the database from my www site, like on many sites over the internet, a kind of fotoservice.

Tom Kyte

Followup  

June 30, 2005 - 10:23 am UTC

are you running mod_plsql, what is your application server, what technologies are you using, capable of using.

Re: question about storing files in database from May 20, 2005

June 30, 2005 - 10:42 am UTC

Reviewer: Dan Kefford from New Haven, CT

<quote author="Tom">
and you are going to keep copies of all changes to files, hmmm.

what if user says "i need the file the way it looked at 12pm 2 weeks ago"

you backed it up 2 weeks and 1 day ago, and 1 week 6 days ago (using a file
system)

It was updated 50 times that day.

Please explain how a file system can even begin to accomplish that.
</quote>

Hey... VMS can do that:
</code> http://h71000.www7.hp.com/doc/731FINAL/6489/6489pro_006.html#files7 <code>

;P


Tom Kyte

Followup  

June 30, 2005 - 10:53 am UTC

if you don't hit the version limit :)

Question about uploading images to the server table

June 30, 2005 - 1:30 pm UTC

Reviewer: KayTecK from Poland

i do run mod_plsql.
my Oracle version is 9i Release 2 (9.2.0.1.0) for Windows NT
on Win2k pro.

i am trying to use only psp and html, but if there is any other solution, i will try it. i don't know java at all...

now i have a kind of semi-solution - to upload a file through the ftp program to a server and then run procedure from www site to insert an image to database but it's a little torture ;)



Tom Kyte

Followup  

June 30, 2005 - 1:42 pm UTC

</code> http://docs.oracle.com/docs/cd/B14099_07/web.1012/b14010/concept.htm#sthref67 <code>



Question about uploading images to the server table

June 30, 2005 - 5:31 pm UTC

Reviewer: KayTecK from Poland

i can't handle it :(

i dont know witch of the procedures is my_doc_download_procedure

i just know that i have to enter in my DAD configuration:
PlsqlDocumentTablename scott.my_document_table
PlsqlUploadAsLongRaw html
PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.my_doc_download_procedure

my_document_table can be
create table mytable
(
blob_name varchar2(128),
blob_data blob
);
i suppose..

and how to point a file from my hdd to exec /pls/dad/dosc/file-name?
where is definition to myTable from write_info procedure??
and do i need it??

i'm getting lost there :(

can you help me and tell me what should i do in turn (which procedures to use, how to call them on my host comp)??


Tom Kyte

Followup  

June 30, 2005 - 5:57 pm UTC

when you upload, the files will be placed into scott.my_document_procedure and your target routine will get the filename. (the table structures are documented in that same link, your structure does not match)


there are examples and everything right there, on that page?

Question about uploading images to the server table

July 01, 2005 - 12:16 am UTC

Reviewer: KayTecK

there is no info about myTable table structure from point 1.7.4.
i tried something like this:
create myTable(
person varchar2(64),
descript varchar2(64),
blob_content blob
);

and it doesn't work

i just created procedure write_info and trying to call it from the form examle...
what else shold i do?
do i have to configure DAD like:
PlsqlDocumentTablename scott.myTable
PlsqlUploadAsLongRaw html
PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.write_info
??

it dosn't work :(

Tom Kyte

Followup  

July 01, 2005 - 9:37 am UTC

the table structures are certainly right there? Are we looking at the same link?

<quote>
You can specify the document storage table for each DAD. The document storage table must have the following definition:

CREATE TABLE [table_name] (
NAME VARCHAR2(256) UNIQUE NOT NULL,
MIME_TYPE VARCHAR2(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR2(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR2(128),
[content_column_name] [content_column_type]
[ , [content_column_name] [content_column_type]]
);


Users can choose the table_name. The content_column_type type must be either LONG RAW or BLOB.

The content_column_name depends on the corresponding content_column_type:

*

If the content_column_type is LONG RAW, the content_column_name must be CONTENT.
*

If the content_column_type is BLOB, the content_column_name must be BLOB_CONTENT.

An example of legal document table definition is:

CREATE TABLE MYDOCTABLE (
NAME VARCHAR(256) UNIQUE NOT NULL,
MIME_TYPE VARCHAR(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR(128),
CONTENT LONG RAW,
BLOB_CONTENT BLOB ;
);

</quote>

Question about uploading images to the server table

July 02, 2005 - 12:30 am UTC

Reviewer: KayTecK from Poland

hi.
we certainly look at the same link.
i create a table with the same structure you show (p.1.7.1).
then we have to configure dad (p.1.7.3):
<quote>
PlsqlDocumentTablename scott.my_document_table
PlsqlUploadAsLongRaw html
PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.my_doc_download_procedure
</quote>
so... my_document_table is the table created a moment ago :)
but what shold i insert into PlsqlDocumentProcedure??
there is no exactly written...
in p.1.7.4 there is a form to pick a file from disk and then to insert it to table myTable.
i don't know if i have to connect a table from p.1.7.1 with procedure from p.1.7.4.?
if so... i was trying many possibilities...
doesn't work :(
so... can you tell me, please, step by step which procedures, tables to use and how to use it then, as you told how to insert and read files from and into a table :) ?
i think i'm not to smart to figure it out of the text you gave me a link to... :(

Tom Kyte

Followup  

July 02, 2005 - 9:22 am UTC

did you do this in SCOTT?


you write the code for my_doc_download_procedure



Question about uploading images to the server table

July 02, 2005 - 11:19 am UTC

Reviewer: KayTecK from Poland

yes i did it as scott.

i don't know which of codes written in that site is proper to my_docs_download_procedure.

it would be much easier and faster, if it is possible, if you can write what should i do step by step to do my code running :)

indeed, i can't put that code on the site together and working...


Tom Kyte

Followup  

September 30, 2010 - 1:48 pm UTC

it would be much easier and faster, indeed. for whom?

sorry, I'm going to stick with the teaching to fish metaphor. I cannot believe we cannot work through the docs. Lets see, we have clicked the above link and are looking at 1.7 "File upload and download"

Now, you have already done the create table and DAD setup, so we are here
</code> http://docs.oracle.com/docs/cd/B14099_07/web.1012/b14010/concept.htm#sthref89

In that bullet list, the third item reads 
PlsqlDocumentProcedure (Document Access Procedure)
and happens to be a hyper link itself
http://docs.oracle.com/docs/cd/B14099_07/web.1012/b14010/concept.htm#i1010535

Ok, we are at 1.7.3.3 and we read:
<quote>
The PlsqlDocumentProcedure procedure is an application-specified procedure. It has no parameters and processes a URL request with the document access path. The document access procedure calls wpg_docload.download_file(filename) to download a file. It knows the filename based on the URL specification. For example, an application can use this to implement file-level access controls and versioning. <b>An example of this is in Section 1.7.7, "File Download".</b>
</quote>

Ah - an example at 1.7.7 and it too is a hyper link!  Lets click on it:
http://docs.oracle.com/docs/cd/B14099_07/web.1012/b14010/concept.htm# BCEBHHEI <code>

well, there you go -- the example routine....



Question about uploading images to the server table

July 05, 2005 - 1:33 pm UTC

Reviewer: KayTecK from Poland

when i compile a procedure:
procedure process_download is
v_filename varchar2(255);
begin
v_filename := getfilepath;
select name into v_filename from plsql_gateway_doc
where UPPER(name) = UPPER(v_filename);
wpg_docload.download_file(v_filename);
exception
when others then
v_filename := null;
end process_download;

i got an error about getfilepath function.

when i compile:
CREATE OR REPLACE procedure process_download is
v_filename varchar2(255);
begin
wpg_docload.download_file(v_filename);
exception
when others then
v_filename := null;
end process_download;

all seams to be all right.
when i call a procedure from my web broser
thre is no error, but my table is steal empty
i think i need that part with getdirname stuff, but there is an error.
any idears??


Tom Kyte

Followup  

July 05, 2005 - 2:02 pm UTC

care to SHARE the error

Question about uploading images to the server table

July 07, 2005 - 11:53 pm UTC

Reviewer: KayTecK from Poland

instead of many tries i resigned...
i can't handle it...

grate thanks for your help.
you are greate.

Question about uploading images to the server table

July 10, 2005 - 8:46 am UTC

Reviewer: KayTecK from Poland

hi.
i've been reading the reference you gave me a link to again
and i found out that it is a reference to Oracle® Application Server mod_plsql User's Guide
10g Release 2 (10.1.2)!!!

is it possible that i couldn't make it becouse of my oracle version differences?



Tom Kyte

Followup  

July 10, 2005 - 9:32 am UTC

the directions as they are have not changed for many many many releases, mod_plsql has always plugged along as it has.

you may refer to your versions documentation, but you'll find it to be pretty much the same set of step by step with examples.

Out Of Topic

July 10, 2005 - 11:26 am UTC

Reviewer: KayTecK from Poland

ok.
i try to implement it...

and i have one question out of topic.

i want to do a kind of search engine
there i have loop displaying results <% for bk in (select * from table where <conditions> order by <something> <order>) loop %>
<statement>
<% end loop; %>

how can i insert my variable value in <something> and <order> place?
i was trying something like:
<%@ plsql parameter='sort' default='1'%>
<%! sorting varchar2(15); %>
<% if sort=1 then sorting:='price'; end if; %>
and then:
select * from table where <conditions> order by sorting <order>
but it doesn't work...


Tom Kyte

Followup  

July 10, 2005 - 12:07 pm UTC

you would order by a decode typically:



order by decode( l_sorting, 1, price ),
decode( l_sorting, 2, amount ),
decode( l_sorting, 3, name ),
...........


and so on. STRONGLY suggest you prefix plsql variable names with L_ for local variable, P_ for parameters to procedures and G_ for package globals. It removes the scoping confusion when you have column names that are the same as your variable names!!!! strongly strongly STRONGLY recommend that.

Out Of Topic

July 10, 2005 - 1:50 pm UTC

Reviewer: KayTecK from Poland

if i add only:
order by decode(val1,1, price,2, somethig)
everythiong's all right.
but when i try to add asc or desc there are errors in compilation.

i tried:
order by decode(val1,1, price,2, somethig) decode(val2,1,asc,2,desc);
and:
order by decode(val1,1,'price asc',2,'somethig desc')

and it doesn't work...

Tom Kyte

Followup  

July 10, 2005 - 2:03 pm UTC

that isn't what I said to do -- if you do a SINGLE decode, you need to worry about type conversions


order by decode( X, 1, price ),
decode( X, 2, somthing ),.....


if you want an asc/desc flag *as well*, you would


order by case when x=1 and y=1 then price end ASC,
case when x=1 and y=2 then price end DESC,
case when x=2 and y=1 then something end ASC,
case when x=2 and y=2 then something end DESC

really -- "L_" do it and you'll never every be sorry.


You can also consider dynamic SQL here where you build the query in a string.

Out Of Topic

July 10, 2005 - 2:22 pm UTC

Reviewer: KayTecK from Poland

Well... What to say...
You're grate...

Thx.


displayin PDF from Web forms

July 12, 2005 - 4:56 pm UTC

Reviewer: A reader from Ravi, Atlanta

Tom,

Any idea how can I display PDF via procedure "image_get" from web forms. I am executing a form using following link,

</code> http://wert0168:8889/forms90/f90servlet?form=car3410.fmx <code>

How would I use "image_get" procedure with the above link to display the PDF.

Thanks

Ravi


Tom Kyte

Followup  

July 13, 2005 - 10:54 am UTC

haven't touched forms in over a decade.

otn.oracle.com has some discussion forums for forms.

Misspelled?

July 14, 2005 - 4:27 pm UTC

Reviewer: Henrik from Copenhagen, Denmark

Hi'

Looking at the original sample in the top. The HMTL URL
is ...\owa\image.pdf....

But the package is named image_get.

Is there somthing I misunderstod here - how's the url related to the package?

Best regards, Henrik


Tom Kyte

Followup  

July 15, 2005 - 7:19 am UTC

typo


but, ctl-f for wpg_docload for the right way to do this in 2005.

ALHITAR

July 25, 2005 - 4:25 am UTC

Reviewer: TAHA A. ALHITAR from Yemen

THANS TOM,
I AM EXECUTE THE FOLLOWING PACKAPE IN SUCSSFULL MANEL 
BUT WHEN I CALL THE PACKAGE  image_get TO RETRIVE THE GIF FILE APPER THE FOLOWING ERROR
==
SQL> EXEC image_get.GIF(1);
BEGIN image_get.GIF(1); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 315
ORA-06512: at "SYS.HTP", line 859
ORA-06512: at "SYS.OWA_UTIL", line 359
ORA-06512: at "TEST.IMAGE_GET", line 16
ORA-06512: at line 1

create table demo
( id        int primary key,
  theBlob    blob
)
/
create or replace directory my_files as 'C:\Taha\sys\LOBTEST\DIR';

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', '1.GIF' );
    dbms_lob.fileopen( l_bfile );

    dbms_lob.loadfromfile( l_blob, l_bfile,
                               dbms_lob.getlength( l_bfile ) );

    dbms_lob.fileclose( l_bfile );
end;
/

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;
/
--Package body created.

--SQL> SHO ERR
--No errors.
 

Tom Kyte

Followup  

July 25, 2005 - 7:44 am UTC

because you are not calling it from the web, the owa packages have not been initialized.


declare
nm owa.vc_arr;
vl owa.vc_arr;
begin
nm(1) := 'X';
vl(1) := 'Y';
owa.init_cgi_env( nm.count, nm, vl );
end;
/


run that first.

TIFF Image upload

August 01, 2005 - 7:44 am UTC

Reviewer: Gayatri from Hyderabad, India

I am able to upload/download jpeg files through web using PSPs. I could upload Tiff files too.But, when I try to download a TIFF image, it displays all junk form in the explorer.
In the document table, I see that the MIME type for jpeg is being stored as image/pjpeg and for TIFF it is application/octet-stream.
My http server has an entry 'image/tiff' in its MIME types.
I tried the following but in vain.
1. tried owa_util.mime_header( 'image/tiff ') during download
2. updating mime type in the database as 'image/tiff'

Both did not work.

One more thing.
How to control on the items being inserted into the document table during upload. Is it possible to customize the values being inserted, such as file name etc. (The 9i documentation on mod_plsql says that customization is possible, but does not say how)

Shall be grateful to get a response.
Regards,


Tom Kyte

Followup  

August 01, 2005 - 9:06 am UTC

what application mime type does your browser report it being downloaded as now?

(tools -> page info in firefox)


You can update that row, you are passed the "key" to it.

TIFF Image upload

August 02, 2005 - 2:27 am UTC

Reviewer: gayatri from Hyderabad, India

I am using Internet Explorer. I could not find the MIME type.

Tom Kyte

Followup  

August 02, 2005 - 7:34 am UTC

try firefox, I don't use IE.

or

</code> http://web-sniffer.net/ <code>

plug your URL into that if you can.

Displaying PDF files

August 08, 2005 - 5:22 am UTC

Reviewer: A reader

Tom, I am to work in a BD 9i release 1, it IAS 10g release 1. Meeting the filing-cabinets * raw *. What I must make to install the UTL_RAW, therefore necessary not to place to function an application that opens files pdf save in a field blob. Debtor

Tom Kyte

Followup  

August 08, 2005 - 8:28 am UTC

utl_raw will be installed, but don't use it, ctl-f for wpg_docload on this page and use that much much better technique.

tiff image upload

August 27, 2005 - 8:01 pm UTC

Reviewer: Andrzej Niedzielski from Poland

hi gayatri.
how did you do uploading file to the table through psp?

i was trying many possiebilities and everything i tried faild....

can you give me some advice?

greetings...

Tom: maybe you can give me gayatris e-mail adress to contact him directly.

Tom Kyte

Followup  

August 28, 2005 - 2:55 am UTC

You do it the way mod_plsql does it, it is just a file upload

</code> http://docs.oracle.com/docs/cd/B14099_10/web.1012/b14010/toc.htm http://docs.oracle.com/docs/cd/B14099_10/web.1012/b14010/concept.htm#sthref111 <code>



various plsql parameter

August 28, 2005 - 9:39 am UTC

Reviewer: Quarantine from Poland

how can i create parameters depending of table contents?

i have a dimension table wall_color.

i wold like to design a psp site that will create a number of parameters depending on how many rows wall_color table would have, becouse previous page have checkbox field.

ie...

wall_color:
color_id name
1 red
2 blue

html code:
<%@ plsql parameter='color1' default='0' %>
<%@ plsql parameter='color2' default='0' %>

example2
wall_color:
color_id name
1 red
2 blue
3 black
4 white

html code:
<%@ plsql parameter='color1' default='0' %>
<%@ plsql parameter='color2' default='0' %>
<%@ plsql parameter='color3' default='0' %>
<%@ plsql parameter='color4' default='0' %>

i don't want to declare constant number of parameters becouse if i create f. e. 8 parameters, there could not be more than 8 colors in wall_color table.

how can i solve this problem??



Tom Kyte

Followup  

August 28, 2005 - 9:49 am UTC

we use plsql tables types for those and mod_plsql will pass you a single array filled with all of your colors.


You can read about plsql table types in the programmers guide:
</code> http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-PL%2F <code>


You'll want to create a package:

create package my_types
as
type table_type is table of varchar(4000) index by binary_integer;
empty_table table_type;
end;


Now you can define variables of type my_types.table_type and default them to my_types.empty_table



How to insert a TIF Image into a Table

October 19, 2005 - 9:51 am UTC

Reviewer: Mohan from Chennai,India

Hi,

I need to insert a TIF Image into a column of a table whose Datatype is RAW and data length for that column is 2000.

How can i do that?

Mohan

Tom Kyte

Followup  

October 19, 2005 - 12:26 pm UTC

depends on the languge, in general


prepare insert into table ( ... ) values ( :bv1, :bv2, .... );
bind the inputs
execute insert.

Inserting TIF image into Database

October 20, 2005 - 4:29 am UTC

Reviewer: Mohan from Chennai, India

Hi,

I have a TIF Image File. I have to insert that image into the Table. First i am Reading that image using BFILE and inserting that Image into a BLOB Column. Then from that BLOB Column, i am reading that using DBMS_LOB.READ to insert that into a RAW Column. But while retrieving the image, i cannot able to see that.

I want to know the difference between TIF File and JPG file. Is there any possibilities to convert TIF image to JPG in PL/SQL.

Thanks

Mohan

Tom Kyte

Followup  

October 20, 2005 - 8:15 am UTC

why use the blob at all.

if you have a bfile, just dbms_lob.substr() it - it'll return the raw data, insert that.

I don't know what you might mean by "But while retrieving the image, i cannot able to see that.", that does not compute.

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a88786/mm_uses.htm#601103 <code>
if you use the interMedia datatypes, yes, you can do image conversion.


Must be very trivial tiff's if they all fit in 2000 bytes or less.

extract data into the Oracle Database

October 20, 2005 - 4:43 pm UTC

Reviewer: Dawar Naqvi from LA, USA, CA

Tom,

I have a file in PDF format.
It has data.

I need to extract data into the Oracle Database.

what should be my action ?
And how could I accomplish?

Is it possible to do imp file from PDF format?


thanks
DN

Tom Kyte

Followup  

October 21, 2005 - 7:52 am UTC

PDF is not structured information, there is nothing "to import" really.

You would need to have an application that can read the pdf extract something and then we can load it.

PDF

October 23, 2005 - 12:44 pm UTC

Reviewer: mo

Tom:

Looking at your code to load and display a PDF file I notice you are loading from a directory on the unix server.

create or replace directory my_files as '/export/home/tkyte/public_html';

If I create PDFs using ADOBE ACROBAT 6.0/7.0 on local machine and I already have files on my local windows PC directory, I want to create an HTML form and use

<tr><th><label for="uploaded_file">File:</label></th><td colspan="2"><input type="file" id="uploaded_file" name="p_uploaded_file" size="50" /></td></tr>

to load the files into the oracle database.

1. What do you set your directory to? or you do not need to since it is getting submitted with the form (my thinking)

2. Do I need to use DBMS_LOB or just a simple insert statement

inesrt into PDF_FILES(ID,FILE) values (PDF_SEQ.nextval,p_uploaded_file)

Thanks,


Tom Kyte

Followup  

October 23, 2005 - 1:50 pm UTC

1) whatever you want to, they will not go to a file on the server, they will go to a BLOB in the database....


2) nothing - you are doing file upload, read the mod_plsql doc for details.
</code> http://docs.oracle.com/docs/cd/B14099_14/web.1012/b14010/concept.htm#sthref111 <code>


I was downloading a file from the file system, you are uploading a file into the database...



pdf

October 25, 2005 - 10:36 pm UTC

Reviewer: sam

Tom:

1.  I want to upload PDFs (my_file.pdf) from PC to oracle BLOB column. Would these be appropriate settings/names for that?

PlsqlDocumentTablename   insd.document_table
PlsqlUploadAsLongRaw   txt
PlsqlDocumentPath   docs
PlsqlDocumentProcedure   insd.download_pdf

2. If I did not specify the docs directory would i be able to retrieve the file directly after the DAD call like 
.../pls/insd/my.pdf

3.  To check the variables values from sql plus can i run

SQL> exec owa_util.get_cgi_env('PlsqlDocumentPath');


4.  Since this document table is set per DAD does that mean that all BLOB columns in several production table are all stored in this my_document table? What is the unique id that links each file to the parent table record.

Thanks,
 

Setting up mod_plsql & DAD

January 18, 2006 - 11:46 am UTC

Reviewer: toyin from nigeria

Thank you this thread has been very useful.
I am trying to display pdf documents from Oracle forms 6i. Is this possible. I have set up everything and so far I can upload documents to blob cols on tables. I have also been able to download from the database to my OS. However I'd like to display these pdf files.
I have tried unsuccesfully to do this via html.
<a href=/xxx/xxx/image_get.pdf?f_id=122>Click Me</a>
Please how do i get 'xxx'?
I do not know how to set up mod_plsql & DAD. I am using Oracle 9.2.0.1.0. Please what do I do? How do I configure mod_plsql and how can I find out what my DAD is?

Tom Kyte

Followup  

January 19, 2006 - 7:56 am UTC

do you have a database/app server administrator that can help you out - they would be the ones that pretty much need to set this up.

if you want to ask how to best do this in FORMS - suggest you use the discussion forums on otn.oracle.com.

file download

January 22, 2006 - 12:04 am UTC

Reviewer: mo

Tom:

I want automate the download of a Digital Talking Book (CD). Each book consist of several files. The Files are on a CD. I need to copy the CD files into a directory on the web server and then allow users to use the internet to download these books.

I am trying to think of the solution to accomplish thi using MOD_PLSQL. Would this be the right steps:

1. copy the files on the CD to a unix or windows server where the web server is installed.

2. Somehow (not sure how) zip the files for each book and create a zip file. I am not sure if you would zip the files on demand or have one that is always there on drive.

3. Create a web page for user that displays all digital books. Then when user clicks a link it downloads the zip file for that book to his PC where he can unzip it and burn the CD and insert it into digital player.

Would you use here dbms_lob and UTL_FILE to do the above or you need to do some java coding too for zipping.

THank you,

Tom Kyte

Followup  

January 22, 2006 - 9:36 am UTC

this doesn't seem to be a "database problem" here at all.

seems you would load the contents fo the CD onto the file system in any format you wanted (compressed or whatever)

Then people click on links that point to these files.

how about crystal report

March 01, 2006 - 1:04 pm UTC

Reviewer: Jimmy Zhu from MD. USA

Do we still need to install some crystal report components in Oracle web side? it seems these crystal report components cause a problem of performance.
any ideal?

Tom Kyte

Followup  

March 01, 2006 - 1:53 pm UTC

to download pdf files stored in the database?

no, you need nothing from crystal reports.

I have no crystal reports installed anywhere, and I have 100% functionality of my oracle database. not sure what you mean?

Problems getting length of BFILE

March 02, 2006 - 9:36 am UTC

Reviewer: Petri Rautakoski from Finland, Europe

Hi! I have some files stored in database table as BFILE and I have created a VIEW into database to show me the length of BILE, BLOB or CLOB column depending which of these has data.

Now when I for example modify one of files (empty one) and then execute SELECT statement for length from VIEW I'll get 0 as length. When I wait like 15 seconds I'll get the exact length. Can You Tom tell me what is behind this behaviour?

Tom Kyte

Followup  

March 02, 2006 - 12:47 pm UTC

you have file names stored in a database table if you are using bfiles, not the files (technicality ;)

You would sort of have to provide an example so we can see what precisely you do and how to replicate it.

Problems getting length of BFILE - continued

March 03, 2006 - 4:17 am UTC

Reviewer: Petri Rautakoski from Finland, Europe

Yep, BFIle is only pointer not actual data. ;o)

I have located the problem and it is the use of NFS. These files are located in other server and accessed via NFS, case:

1. Other than database server writes to this file via NFS
2. Oracle tries to read length via NFS
3. Oracle doesn't get the right result information of file
4. Oracle will get the right information when for example ls command has been executed via NFS in database server

How to test:

1. establish NFS server in other than database server
2. establish NFS client in database server (in this example into directory /mnt/test)
3. create test table

create table bfile_test (
id integer not null primary key,
bfile_data bfile not null
);

4. create directory object

create directory MNT_TEST AS '/mnt/test';

5. create PL/SQL

create or replace procedure insert_test(test_id in integer, filename in varchar) IS
l_bfile bfile;
begin
l_bfile := bfilename('MNT_TEST', filename);
insert into bfile_test (id, bfile_data) values (test_id, l_bfile);
end;
/

create or replace function test_length(test_id in integer) RETURN INTEGER AS
length integer;
begin
select dbms_lob.getlength(bfile_data) into length from bfile_test where id = test_id;
return length;
end;
/

6. insert bfile

call insert_test(1,'test.file');

7. declare length variable

variable test_length number;

8. just test

call test_length(1) into :test_length;

produces exception.

9. write into exported directory this test.file in file server
10. test length again

call test_length(1) into :test_length;

produces exception

11. execute ls in /mnt/test in database server
12. test length again

call test_length(1) into :test_length;

will success.

Tom Kyte

Followup  

March 03, 2006 - 8:16 am UTC

you have a problem with nfs, not with the database then. The NFS cache is getting in your way. Look to NFS not the database to resolve this issue.

MIME encoding using PL/SQL

May 02, 2006 - 8:36 am UTC

Reviewer: mobra from Norway

Are there any supplied packages or utilities for PL/SQL that can be used to MIME encode a binary file (for example, a PDF stored in a BLOB column) ?

Tom Kyte

Followup  

May 02, 2006 - 3:29 pm UTC

SQL> desc utl_encode

 

utl_encode, raw vs blob

May 05, 2006 - 3:00 am UTC

Reviewer: mobra from Norway

UTL_ENCODE.BASE64_ENCODE takes a RAW parameter.

What is a RAW, how is it different from a BLOBL, and how can I convert a BLOB to a RAW for use in the base64_encode function?

Tom Kyte

Followup  

May 05, 2006 - 6:47 am UTC

it permits upto 32k of RAW (binary data) to be worked on.

declare
l_raw long raw;
begin
l_raw := dbms_lob.substr( some_blob, 32765, 1 );
......






text search on GIF/JPG/TIF format

May 23, 2006 - 2:34 am UTC

Reviewer: rais from PAK

The BLOB can have various document files (pdf, Word files, Excel, gif/jpg/tif files....).

Is this possible, to text search on gif/jpg/tif files. for example we scan a cheque and wants to search on cheque no. Really appreciate the help. Thanks.



Tom Kyte

Followup  

May 23, 2006 - 7:30 am UTC

in a gif and jpg - likely *not*
in a tiff - depends on what is in the tiff

sounds like you need some OCR (character recognition)

limit a BLOB field

May 30, 2006 - 3:20 pm UTC

Reviewer: A reader

Hi

I would like to know how can we add a check constraint in a blob field. For example if I dont want users to insert binary files bigger than one megabyte in the BLOB field do I have to previously calculate the size in my code or I can use a check constraint in the BLOB field?

Cheers

Pinguman

Tom Kyte

Followup  

May 30, 2006 - 6:47 pm UTC

You cannot, you'll need to use a stored procedure for this.

Why?

People tend to insert empty_blobs() and then STREAM the data (using any number of APIs - dbms_lob for example). And the streaming APIs - they do not fire any triggers.

So, using a trigger - not going to happen, period.

You will provide your OWN API to the user (stored procedure) so they can WRITE to this lob (do not grant them this ability directly, only let them use your procedure)

32767... !

June 19, 2006 - 5:03 am UTC

Reviewer: Pretaish from UK

Hi Tom,

Is it fine to declare varchar2 variables of size 32767. (E.G. :-
declare
a varchar2(32767);
............
)
Many developers don't usually use varchar2 variables above the size of 4000.

Please explain.

Thanks in Advance,
Pretaish

Tom Kyte

Followup  

June 19, 2006 - 6:06 pm UTC

in a create table - nope.

in a plsql program, that is the max size, you can use that (32k)

pdf download

July 12, 2006 - 7:03 pm UTC

Reviewer: sam

TOm:

I did your emaple and got a blank page. I saved the file to local directory and found out it is blank too.

How can i check the NLS_LANG of app server and database to make sure they are the same and what should they be both set at?(i.e. default).




Tom Kyte

Followup  

July 12, 2006 - 7:09 pm UTC

get onto the application server and if windows - check registry, if unix check environment.

but - just use wpg_docload (ctl-f for it), much easier and safer.

pdf download

July 12, 2006 - 7:05 pm UTC

Reviewer: sam

Tom:

1. Would everything above works fine if i use "BFILE" meaning using external file?

2. DO I need to use WPG_DOCLOAD for that design? If yes, would mod_plsql buffer the file on the filesystem?

Thanks,

Tom Kyte

Followup  

July 12, 2006 - 7:10 pm UTC

1) yes
2) yes you would use wpg_docload. mod_plsql doesn't cache, but it will "stream" the data to the browser.

pdf

July 13, 2006 - 11:21 am UTC

Reviewer: sam

Tom;

I checked the RDBMS NLS_LANG using the following. But how do you check the MOD_PLSQL character set. We are using 8IAS.

I also thought oracle will convert the character set if the client is different.

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_DATE_FORMAT                mm/dd/rrrr
NLS_DATE_LANGUAGE              AMERICAN
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1
NLS_RDBMS_VERSION              8.1.7.2.0
 

Tom Kyte

Followup  

July 13, 2006 - 1:38 pm UTC

you check the environment in unix, the registry in windows (restating what I stated above)

but - just use wpg_docload and IT IS A NON-ISSUE.

July 13, 2006 - 1:52 pm UTC

Reviewer: Matthew from Canada


download files

July 19, 2006 - 7:09 pm UTC

Reviewer: sam

Tom:

1. I did it test it both ways: storing the binary file in a BLOB and storing a pointer in a bfile to point at the file in the unix file system.

I used this procedure to download the file:
procedure get_brf( p_id in demo.id%type )
is
l_lob blob;

begin
select theBlob into l_lob
from demo_brf
where id = p_id;
owa_util.mime_header( 'image/brf' );
wpg_docload.download_file(l_lob);

end;

The binary file is a braille binary format. When I clicked on the link, it gave me a dialog box to "save" or "cancel". I could not get it to open the file in the browser.

When i do it through the CGI program it does open it in the browser. Is it a File association problem or mod_plsql issue and how do you fix it?

2. If you use either method, how do you keep the file between O/S and database in sync. For example, new files get added or deleted every day?

3. If you use the <A HREF=file></A> would this be a straight pointer to the file without going to mod_plsql. meaning that the request goes to the http server and then the file system skipping mod_plsql?


thank you,

Tom Kyte

Followup  

July 22, 2006 - 4:23 pm UTC

1) verify the mime type is being sent appropriately.


2) I do everything in the database, there are no such things as "files" for me - if someone has a new "file" for me, it becomes a row in a table.

3) correct, if the URL in the href points to a file, not a plsql "dad" it will go right after the file.

my_files as '/export/home/tkyte/public_html'

August 02, 2006 - 4:54 pm UTC

Reviewer: A reader

Tom,
In above mentioned example my_files directory structure refers to local system or the Oracle server. I have access to Oracle server through PL/SQL developer.
Thanks.

Tom Kyte

Followup  

August 03, 2006 - 9:03 am UTC

I have no idea what you are commenting on.

Yes, my_files is ALWAYS on the server.

Good that you are using plsql developer?

Not sure what else to say??

On similar grounds...

September 14, 2006 - 2:12 pm UTC

Reviewer: Subu Mysore from Fresno, CA USA

Could U please enlighten me on how to store a fax image(CCITT format) in the database and how to retrieve it in oracle forms (6i) ? Your time and effort is sincerely appreciated.

Tom Kyte

Followup  

September 14, 2006 - 4:33 pm UTC

"U" isn't available - they might be hanging out in the forms discussion forum on otn.oracle.com though!

(I haven't used forms since 1995, not really up to date on that particular tool)

Can i sort the data and show in a report???

October 03, 2006 - 1:24 pm UTC

Reviewer: vishnu from USA

Tom ,
Excellent knowledge resource ...I have a requirement like here
1. Load the text file in to DB (which is similar to pdf ) and i can do it with the above discussiosn.
2. Sort the blob file (text file and not pdf) and put the data in some other table or show it directly in the select statement ???

Thanks
Vishnu

Tom Kyte

Followup  

October 03, 2006 - 4:51 pm UTC

sorry, but this isn't even beginning to make sense to me.

code does not work in DMZ

October 18, 2006 - 4:02 pm UTC

Reviewer: Nuruddin A. Parkar from Suitland, MD

Dear Tom.
I used your procedure to display an Spreadsheet (XSL) file (stored in Oracle DB) to web a server. It works fine and displays the spreadsheet as is. However, when I moved code to DMZ environment, the spreadsheet displays scrambled.

Any idea WHY?

Thanks in advance
Nuru

Tom Kyte

Followup  

October 18, 2006 - 4:13 pm UTC

the DMZ has *nothing* to do with it.

the character set of your application server does.

In any case - do not use this htp.p approach anymore, ctl-f for wpg_docload on this page - better, faster, cheaper, not prone to character set translation....

October 30, 2006 - 1:42 pm UTC

Reviewer: kartikey kumar srivastava

sir
my question is related to procedure.
i have a table(suppose:teacher(t_no,f_name,l_name,....)and i want to list 10 records using loop

Tom Kyte

Followup  

October 30, 2006 - 3:13 pm UTC

ok, go ahead.

I would not use a loop, rather:

select *
from (select * from teacher order by whatever...)
where rownum <= 10;


would be the RIGHT way to do it, using procedural code would be wrong, but you are certainly free to do so.

It is pretty easy, but it really doesn't have a thing to do with "displaying pdf files stored in the database" and smells a bit like "homework" :)

Upload/Download pdf files to and from Oracle DB

January 09, 2007 - 4:48 pm UTC

Reviewer: A.K. from USA

Tom,

I went through your posting "Displaying PDF files stored in the Database" and found it very helpful. In fact it worked exactly the way it was intended to. However, in your solution you mentioned that the image/pdf file has to be saved in that particular directory that you create on the Oracle server.

But in my case, users want to upload a file which is saved on their PC/network folder using a front end screen which has a "Browse" button and clicking on which opens a windows dialog showing respective files and folders on that PC. I then intend to store the file that user selects as a BLOB so that they can download it at a later stage file by clicking on the html link provided on front end screen. The front end screen is coded using html, javascript and plsql.

Users will be mainly uploading and downloading pdf, word or xls documents. I am want to know how do I select the path or the file that user selects and transfer it onto Oracle server and then save it in the Oracle DB as a blob. I am pretty clear as far as the download part goes.

But please advise as to how to do this using plsql with an example if possible.

Thank you very much for your time and help.
A.K.

Excel File save in data base though Forms

February 25, 2007 - 1:23 am UTC

Reviewer: Nazmul Hoque from Bangladesh

Dear Tom,

I want to make facility to save Excel Sheet, Word Files in my oracle data base throug forms of my pack like pciture we save in database. and need option for use file when required. Is it possible, If yes please help me to do this.

Regards
Nazmul Hoque
Dhaka


reading pdfs and mod_plsql

March 12, 2007 - 3:15 am UTC

Reviewer: Nilesh from India

Sir,

I read through the contents, agreed that I am newbie to mod_plsql stuff and it's configuration.

I was able to insert the pdf file into blob using your procedures. This worked well. Now, I would like to read the inserted Pdf file and show it on the browser.
As I understand, I would need to configure mod_plsql and use it to read back the inserted contents. However, I do not have an Oracle Http server, It's going to be Coldfusion server. So would below work?
User will access a .cfm page, which somehow needs (I dont know how) to be routed to the Database (via mod_plsql and without Oracle http server) , which would access the relevant procedures, get the content back and the pdf would be shown in the browser.
Would it be really possible to use mod_plsql like this, without Oracle httpserver and simply with Coldfusion server? It would be hepful if you can point to specific setup details.

Thanks.


Tom Kyte

Followup  

March 12, 2007 - 8:29 pm UTC

time to ask the cold fusion folks "how to best display the contents of a blog in the web browser with your product?"


Privileges Needed?

March 20, 2007 - 1:23 pm UTC

Reviewer: A reader from PA

What are the bare-bones privileges needed for a user to execute this code:

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


I keep hitting a:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_LOB", line 607
ORA-06512: at line 11


Also, more generically, when you hit a situation like this, where the privilege you are lacking isn't evident, what's the best approach to figure out what you need.

I tried to grant execute, and all on dbms_lob to the user, and neither worked.

Thanks much!
Tom Kyte

Followup  

March 20, 2007 - 2:13 pm UTC

they need:

create session
read on directory MY_FILES
select, insert, update on demo (you were likely missing UPDATE)


I just looked at the failing line of code (loadfromfile) and realizing that is an update in effect.....



ops$tkyte%ORA9IR2> create or replace directory MY_FILES as '/tmp'
  2  /

Directory created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create user a identified by a default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA9IR2> create table demo( x number, theBlob blob );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA9IR2> grant read on directory MY_FILES to a;

Grant succeeded.

ops$tkyte%ORA9IR2> grant select, insert on demo to a;

Grant succeeded.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> @connect a/a
ops$tkyte%ORA9IR2> set termout off
a%ORA9IR2>
a%ORA9IR2> set termout on
a%ORA9IR2> alter session set current_schema=ops$tkyte;

Session altered.

a%ORA9IR2> declare
  2    l_blob  blob;
  3    l_bfile  bfile;
  4  begin
  5    insert into demo values ( 1, empty_blob() )
  6    returning theBlob into l_blob;
  7
  8    l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
  9    dbms_lob.fileopen( l_bfile );
 10
 11    dbms_lob.loadfromfile( l_blob, l_bfile,
 12                      dbms_lob.getlength( l_bfile ) );
 13
 14    dbms_lob.fileclose( l_bfile );
 15  end;
 16  /
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_LOB", line 607
ORA-06512: at line 11


a%ORA9IR2> connect /
Connected.
a%ORA9IR2> grant update on demo to a;

Grant succeeded.

a%ORA9IR2> connect a/a
Connected.
a%ORA9IR2> alter session set current_schema=ops$tkyte;

Session altered.

a%ORA9IR2> declare
  2    l_blob  blob;
  3    l_bfile  bfile;
  4  begin
  5    insert into demo values ( 1, empty_blob() )
  6    returning theBlob into l_blob;
  7
  8    l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
  9    dbms_lob.fileopen( l_bfile );
 10
 11    dbms_lob.loadfromfile( l_blob, l_bfile,
 12                      dbms_lob.getlength( l_bfile ) );
 13
 14    dbms_lob.fileclose( l_bfile );
 15  end;
 16  /

PL/SQL procedure successfully completed.



how to do that for bfile

March 27, 2007 - 2:21 pm UTC

Reviewer: A reader

sir,
in your answer to the original question, you used blob. how can i achieve the very same result with bfile. the documents are stored on server.

thanks
hash
Tom Kyte

Followup  

March 28, 2007 - 11:14 am UTC

exactly the same way.

unable to download word/tif files

May 02, 2007 - 2:15 am UTC

Reviewer: P.G. from India

Excellent source of information.
I am able to upload and download files through mod_plsql DAD and wpd_doc... respectively.But when it comes to tif and ms-word files, a lot of junk is displayed on the explorer. Unable to save them on my browser client. Other types viz., jpeg, gif, txt are downloaded fine.
Any changes need to be done during upload or download ?
Thanks and regards,
Tom Kyte

Followup  

May 02, 2007 - 8:17 am UTC

are you using the correct mime types - probably not and the browser just presumes these files are to be viewed as text...

mimetypes

May 16, 2007 - 2:44 am UTC

Reviewer: P.G. from India

Thanks for your response. Sorry that I could not respond immediately. The mime types are getting stored in the database correctly. for tif it is stored as application/octet-stream and for word files it is ms-word.
Tom Kyte

Followup  

May 16, 2007 - 10:29 am UTC



the browser reads the mime type, the browser does the action - the browser is getting some mime type and displaying the document.

use your browser and view the properties of that page and see what mime type the browser it actually getting, you might be surprised.

mimetypes

May 17, 2007 - 2:21 am UTC

Reviewer: P.G. from India

I cannot see any mimetype in the browser.
This is the piece of code I am using for download of tif.It works the same way whether I give or remove the following two lines of code. Request your help to correct the same.

owa_util.mime_header('image/tiff',false);

owa_util.http_header_close;


-- Initiate Direct BLOB download

wpg_docload.download_file(myblob);
Tom Kyte

Followup  

May 17, 2007 - 11:20 am UTC

you can, view properties of the page. In firefox for example, I right click and view page info

if you use less functional browsers, maybe they won't.

I want you to verify what the browser is seeing.

mimetypes

May 17, 2007 - 2:22 am UTC

Reviewer: P.G. from India

I cannot see any mimetype in the browser.
This is the piece of code I am using for download of tif.It works the same way whether I give or remove the following two lines of code. Request your help to correct the same.

owa_util.mime_header('image/tiff',false);

owa_util.http_header_close;


-- Initiate Direct BLOB download

wpg_docload.download_file(myblob);

Mimetypes

May 18, 2007 - 3:21 am UTC

Reviewer: P.G. from India

One interesting observation I had made today is that download of tif and doc files is successful when I use IE 6.0 with XP. I am failing when using win98 with IE6.0. I am unable to make out if its OS or browser problem.

Its a pity that win98 with IE is unable to handle this. My client environment is win98 with ie6.0 which I cannot change because it spans to 400 offices.

Any suggestions which can be useful to me in this regard?
Tom Kyte

Followup  

May 18, 2007 - 4:01 pm UTC

configure ie 6.0 to do something different than it is with the mimetype being delivered. you can tell browsers what to do when they get a certain mime type.

help me please

May 20, 2007 - 4:39 am UTC

Reviewer: A. Sahaat

hi for all
thanks for supportting this code.
i have a problem in this code
my database version is 8
and i can not use this code
any one help me solving this problem.????????
Tom Kyte

Followup  

May 21, 2007 - 10:19 am UTC

laughing out loud.

Ok, this is a rather large page.

And I don't see much code that would not work in 8i.

So, no clue as to what your problem actually might be.

SQL Server vs Oracle

June 05, 2007 - 12:11 pm UTC

Reviewer: Richard from Salzburgo


Hi Tom,

is it possible that a full scan on table with 9MB of blob per row runs faster in SQL Server respect to Oracle 10.2.0.1 . In Oracle 10g store is with the in line and cache options. The chunk is 32KB. I have set the SDU to 32KB but with 20 rows a full scan takes 142 seconds while with SQL Server it only takes 2 seconds. Could it be possible due to the fact Windows and SQL Server use an internal mechanism to speed up the full scan?.

Thank you Tom.
Tom Kyte

Followup  

June 06, 2007 - 1:21 pm UTC

if you have 9mb of blob data per row, we are NOT storing it inline.

without any other sort of information (if it takes 142 seconds to full scan 9mb of data - you have a serious probably somewhere), we cannot really tell a thing. I have no what what you measured, how you measured.

Blob

June 07, 2007 - 6:17 am UTC

Reviewer: Richard from Salzburg


Thanks Tom,

Yes, I know, but although there is 9 MB per row, as I have read, it stores the first 12 address and in Metalink recommend configure the enable in line option.

I measured the performance using 10046 trace and Statspack. The 142 seconds is not due to one row but the 20 rows in total (this is, 142/20 ~ 7.1 seconds per row) . Each blob per row consumes 9MB. In other forums I have read this:

http://forums.oracle.com/forums/thread.jspa?messageID=1765371

My problem is that I don´t know if there is nothing that I can do to solve this because the plataform is windows ...

Tom, thanks you for you patience,


Tom Kyte

Followup  

June 07, 2007 - 2:48 pm UTC

let me see the tkprof, and your table creates.

lobs are stored INLINE until they hit 4000 bytes, they are then stored OUT OF LINE



Writing out from database to the server only

July 17, 2007 - 1:58 pm UTC

Reviewer: Don

Hi Tom -

Great thread here. I see a lot of great examples about how to upload from a file server into the database. And a lot of great examples about how to write out from the database to display in a browser.

Perhaps I overlooked it, but do you have an example that would write a PDF file out from the database and onto the server, so I could just double-click the file and it would open as a PDF document (or .doc, .xls . . . as the case may be).

Basically I can store any document in the database, but just need to write it back out to the file system in the same format it was writing as.

Thanks for pointing me in the right direction.
Tom Kyte

Followup  

July 17, 2007 - 2:37 pm UTC

do you mean to write a blob from the database onto the file system of the database machine?

July 18, 2007 - 3:52 am UTC

Reviewer: Don

yes. from the database to the machine the database is running on (unless there is a way to ftp the output to another server). right now, I'm looking at a 2 step process: 1) write the files out from the database to the machine, 2) ftp them to their final destination. is there an alternative to make this a one step process?

also, is there a BULK UNLOAD utility to use to write out to the machine (something opposite of how SQL*Loader can be used to BULK LOAD the LOBs into the database from the machine)?

thanks

don

Simply Amazing

July 18, 2007 - 4:37 pm UTC

Reviewer: A reader

Exactly what I was looking for! Somehow, the words "thank you" don't seem to be enough to show my appreciation - but I'll try anyways. Thank you.

migrating blobs to table in database

September 18, 2007 - 2:52 pm UTC

Reviewer: Lola Johnson from Columbia, MD USA

Tom - Thanks for your help however I have been given the task of migrating approximately 3000 files (e.g. jpgs, pdfs, docs etc) to a table on a 9208 database! Currently the table has been created with a blob column but I don't know how to proceed with this. From the thread, I understand how to "load" one blob at a time but 3000?! Do you have any tips? Also, the table is already prepopulated with file type, location information all I need to do is load in the appropriate blob data.

I am Jr. DBA (if you can't already tell) and I would really appreciate "dummy" terms if possible.

Thanks so all your assistance again!
Tom Kyte

Followup  

September 18, 2007 - 4:59 pm UTC

EXPort them
IMPort them

just use exp and imp?

To: Lola

September 19, 2007 - 7:15 am UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

Hi, Lola

Sorry for "butting in", but I think there's been a misunderstanding here (which is quite easy if you're not very concise describing your task :-). In this case the term "migrate" to most DBAs mean moving data from one database to another database (for instance from one version to another when upgrading.) In that case Tom's answer is perfectly correct.

My guess (correct me if I'm wrong) is, that you actually mean to copy the files from a file system into the database.
In that case the answer to efficiently load 3000 blobs could be to use SQL loader.

For example see another of Tom's responses here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:624423639385

Basically the technique is to use a control-file like this (assuming a table named blob_table with a column name blob_column):

load data
infile *
append
into blob_table
(
  file_name filler char,
  blob_column lobfile (file_name) terminated by eof
)
begindata
/usr/files/myfile1.pdf
/usr/files/myfile2.doc
/usr/files/myfile3.jpg


The filler means that file_name is a field in the control file data, but not a column in the table. It is just used in the next line to tell sqlloader where it can find the blob data to load.

And call it something like this:

sqlldr control=<name of the above control file>


That way it's basically just a matter of creating a list of all 3000 filenames.

Hope this may be of use to you, Lola :-)


Regards

Kim Berg Hansen

loading client files to blob column in table

September 19, 2007 - 4:00 pm UTC

Reviewer: Lola from Columbia, MD USA

Thanks so much for your response Kim! It is very very helpful. To clarify things, I am trying to load files (pdfs, docs, xmls)that reside on client to a blob column in a table. I do not have the option of moving the files to the database server so I need to load into the database itself. I am running on oracle 9208

Display a multipage tiff using htp.p

November 16, 2007 - 5:37 am UTC

Reviewer: BT from HK

Tom,

how to display, using htp.pa, multipage tiff images (previously captured from scanner) stored in the database intermedia column ?


How to execute

November 21, 2007 - 8:23 am UTC

Reviewer: Karthick from india

I have loaded a log file into a table and also writern a program to get it. It compiles fine. But i dont know how to execute it. From where to call it. From sql plus if i execute the procedure nothing happens. Here is the code.

create table log_file(log_id number(10),log_file blob);

create or replace directory log_dir as 'e:\oracle\Log_files\utl_akivatst';


create or replace package file_get
as
procedure get_log( p_log_id in log_file.log_id%type );
end;
/

create or replace package body file_get
as
procedure get_log( p_log_id in log_file.log_id%type )
is
l_lob blob;
l_amt number default 30;
l_off number default 1;
l_raw raw(4096);
begin
select log_file into l_lob
from log_file
where log_id = p_log_id;

owa_util.mime_header( 'text/plain' );

begin
loop
dbms_lob.read( l_lob, l_amt, l_off, l_raw );

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

declare
load_log blob;
log_file bfile;
begin
insert into log_file values(1,empty_blob()) returning log_file into load_log;
log_file := bfilename('LOG_DIR','SelectiveNameShuffle_19112007151137.log');
dbms_lob.fileopen(log_file);
dbms_lob.loadfromfile(load_log,log_file,dbms_lob.getlength(log_file));
dbms_lob.fileclose(log_file);
end;
/

form where should i call file_get.get_log

When i execute it from sql all i get is

19:05:18 [SYSADM@AKIVATST]> exec file_get.get_log(1);

PL/SQL procedure successfully completed.

I cant see the log file. Its not opening any browser.

I am not very good at web stuff. can you help me out.
Tom Kyte

Followup  

November 20, 2007 - 1:28 pm UTC

well, umm, you sort of need a web browser open...

and in that web browser you would put a URL...

which would include at the end of it:

.../file_get.get_log?p_log_id=1

sort of like you see in your browser right now, with ..../f?p=.....

f is my function
p is my parameter


you use a thing called MOD_PLSQL which requires some setup, it is run by Apache in between your browser and the database...

file upload

February 07, 2008 - 3:58 pm UTC

Reviewer: A reader

Tom:

I want to upload a TEXT file from web page to oracle 9iR2 using 10giAS application server (mod_plsql).

Reading the documents, I have to set up the DAD parameters

plsqlDocumentTablename
plsqldocumentpath
plsqldocumentprocedure
plsqluploadaslongraw

is this correct? what do you usually set those to. I know the first one is the document table name that is required.

2. Does the doument table name have to be like this:

NAME
MIME_TYPE
DOC_SIZE
DAD_CHARSET
LAST_UPDATED
CONTENT_TYPE
CONTENT

I want to save the file in my_table.file and it is CLOB. Is it going to save it the doucments table as BLOB?



3. The table i have is this. iam trying to save the file into my_file column. do i need to change anything here.

my_table
-------
who varchar2(10)
description varchar2(30)
my_file CLOB


Tom Kyte

Followup  

February 07, 2008 - 4:11 pm UTC

you just need to follow the documentation you are already reading, I cannot add to it - it is all documented there, you are in the right place.

you probably do not want "raw", so that would be a big "don't set that"

the rest are rather "personal", you are configuring us to upload stuff where you want it to go.


It will be a blob, you can convert into a clob and move into your own place if you like.

upload

February 07, 2008 - 11:04 pm UTC

Reviewer: A reader

Tom:

1. Why you cant upload a CLOB to database using mod_plsql?

2. WOuld the document be in my_table or Document_table. It looks like all documents uplaoded via mod_plsql can go to one table. What about the tables i create with all CLOBs. are these going to be pointers.
Tom Kyte

Followup  

February 08, 2008 - 7:27 am UTC

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14337/concept.htm#sthref62

because that is what it does - actually, that is my fault, I chose long raw back in the 7.3 days and they stuck with it. It is the only appropriate type since people can upload a word document (binary) or a notepad file (ascii text, or maybe unicode text - that needs a blob so we can convert it properly after the fact)

there can be multiple tables - they will just look the way we dictate, since you

a) want a clob
b) don't like our table

you can

c) read it out, convert it using the appropriate character set conversion, and put it where you like.

upload

February 08, 2008 - 1:41 pm UTC

Reviewer: A reader

om:

just a little confused:

1. the settings
PlsqlUploadAsLongRaw (Not setting this so everything will be stored as blob)

PlsqlDocumentPath docs

--WHat is "DOCS" this mean. Is this a package or procedure or what in the URL. what does this represent in the database.

PlsqlDocumentProcedure write_info (taking the example)

-- does his mean i can only have one procedure to upload files to the database.


2. Let me confirm i understand what you say.

Every file uploaded MUST go to the DOCUMENTS table set up in the DAD.

Then I have to copy this to my other table where the CLOB column is??

Then what i that insert statement in the example inserting to MyTABLE.

Can you show me how can i get the file into my_table as a CLOB.

procedure write_info (
who in varchar2,
description in varchar2,
file in varchar2) as
begin
insert into myTable values (who, description, file);
htp.htmlopen;
htp.headopen;
htp.title('File Uploaded');
htp.headclose;
htp.bodyopen;
htp.header(1, 'Upload Status');
htp.print('Uploaded ' || file || ' successfully');
htp.bodyclose;
htp.htmlclose;
end;
Tom Kyte

Followup  

February 08, 2008 - 3:38 pm UTC

1)

copy from documentation:

If the configuration for these parameters in a DAD is as follows:

PlsqlDocumentTablename scott.my_document_table
PlsqlUploadAsLongRaw html
PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.my_doc_download_procedure

then:

* mod_plsql will retrieve data from, or store to a database table called my_document_table in the scott schema.
* All file extensions except .html will be uploaded to the document table as BLOBs. All files with .html extension will be uploaded as Long Raw.
* All URLs which have the keyword docs immediately following the DAD location will result in invocation of the procedure scott.my_doc_download_procedure.

.... -- does his mean i can only have one procedure to upload files to the database. ...


No, you may have as many as you want. We upload them, put them into a table, and then invoke you passing you the primary key for that newly uploaded row.


2) ...
Every file uploaded MUST go to the DOCUMENTS table set up in the DAD....
yes.

... Then I have to copy this to my other table where the CLOB column is??....
no, that is YOUR CHOICE, most people leave them there.

.... Then what i that insert statement in the example inserting to MyTABLE....
parse error, unable to understand you.

.... Can you show me how can i get the file into my_table as a CLOB. ....
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i1020356

upload

February 08, 2008 - 4:20 pm UTC

Reviewer: A reader

Tom:

1.
My questions was related to these two parameters

PlsqlDocumentPath docs
PlsqlDocumentProcedure scott.my_doc_download_procedure

a. What is "docs" actually represent. Where does this maps to? is it just any name for me to put in and i can use in the URL to access the file.

b. Do I create the my_doc_download_procedure for downloading files?


2. OK I want to leave the text file into the DOCUMENTS table. Now do i change anything into my schema table? do i point this to the DOCUMENTS_TABLE. how does it work.

tests
------
test_no number(2)
test_date date
test_file CLOB

In the example they had this insert statement. I assume this is NOT the document table and they are taking a copy of he file and saving it in this other table.

insert into myTable values (who, description, file);

3. Is there really a need to convert the text file I upload to CLOB? when would i need to do that.

Tom Kyte

Followup  

February 11, 2008 - 11:45 am UTC

1) did you read the above?!?!

* All URLs which have the keyword docs immediately following the DAD location will result in invocation of the procedure scott.my_doc_download_procedure.


it is for downloading.

http ://yourserver/.../docs/dad/foobar.txt

will call your procedure scott.my_doc_download_procedure and tell it "we would like foobar.txt please, get it for us"


Please - give the docs a read through, they actually *do* cover this. Heck, they actually *have code and examples and everything* - really, it does, I just re-read it to make sure.

Everything you are asking is documented right there in the link to the docs I gave you above, repeated here:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14337/concept.htm#sthref62


2) you get the primary key to the documents table, store that primary key as a foreign key value in any table you want to.

3) when you want to store it as text - only you can answer that, do you have a specific need to store as a clob? If not, stop - you are done.

file

February 20, 2008 - 7:50 pm UTC

Reviewer: A reader

Tom:


DO you know why i when I submit a URL that saves info into a table that has one CLOB column I get this error.

I am passing a small string "this is a test" to that CLOB column. Do I treat it differently?

The insert from SQL*PLUS worked fine.


Bad name in the request: not a legal PLSQL identifer
DAD name: gmgd
PROCEDURE : save_info

Tom Kyte

Followup  

February 20, 2008 - 8:50 pm UTC

"this error"

ummmm

"what error" he asks.

upload file

February 20, 2008 - 11:17 pm UTC

Reviewer: A reader

Tom:

I read the "upload" file documentation several times but it is not very clear. Can you confirm the following and correct any missing thing

I want to upload a text file "asktom.txt" to a table in the database. Let us say this table is

tom_table
---------
who varchar2(20)
description varchar2(100)
file CLOB


this is what i need to do:

1. set up my DAD parameters as
PlsqlDocumentTablename my_document_table
plsqlDocumentPath docs
PlsqlDocumentProcedure download_file

and then create my_document table

CREATE TABLE MY_DOCUMENT_TABLE (
NAME VARCHAR(128) UNIQUE NOT NULL,
MIME_TYPE VARCHAR(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR(128),
CONTENT LONG RAW,
BLOB_CONTENT BLOB ;



is this correct so far?

2. I create this html file


<html>
<head>
<title>test upload</title>
</head>
<body>
<FORM enctype="multipart/form-data"
action="pls/mydad/write_info"
method="POST">
<p>Author's Name:<INPUT type="text" name="who">
<p>Description:<INPUT type="text" name="description"><br>
<p>File to upload:<INPUT type="file" name="file"><br>
<p><INPUT type="submit">
</FORM>
</body>
</html>


and I create this procedure

procedure write_info (
who in varchar2,
description in varchar2,
file in varchar2) as
begin
insert into tom_Table values (who, description, file);
htp.htmlopen;
htp.headopen;
htp.title('File Uploaded');
htp.headclose;
htp.bodyopen;
htp.header(1, 'Upload Status');
htp.print('Uploaded ' || file || ' successfully');
htp.bodyclose;
htp.htmlclose;
end;


Now I select the asktom.txt in the html form, hit submit.

Where is the file saved at? is it "tom_table" or "my_document_table".

My understanding is that everything goes into document table and then I store the primary key of that row into my_document_Table. This example does not seem to work that way.


3. My objective is to store this file into Oracle and then retrieve it in text using a link. Would storing as a BLOB support this or I have to convert it into CLOB in storage or access.

thank you,


Tom Kyte

Followup  

February 21, 2008 - 7:06 am UTC

we've had this discussion.

please, ask your DBA to set this up.


you will not upload into the table you say you want to. You will upload to our documents table. that is the way it works.

the document will of course be in my_document_table - THAT IS THE ONLY TABLE we know about.

You get passed the primary key - a short string - a name.


You can store it as a blob, the mime type is what is relevant to the browser for redisplaying this information.

upload

February 21, 2008 - 9:22 am UTC

Reviewer: A reader

tom_table
---------
who varchar2(20)
description varchar2(100)
file CLOB


are you saying to take out the "file" column in tom_table
and add "NAME" as an FK to the 128 character primary key
for my_document_table

basically do this

tom_table
---------
who varchar2(20)
description varchar2(100)
name FK


MY_DOCUMENT_TABLE
-----------------
NAME VARCHAR(128) UNIQUE NOT NULL,
MIME_TYPE VARCHAR(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR(128),
CONTENT LONG RAW,
BLOB_CONTENT BLOB ;

It seems to me NAME is the PK. this is a 128 byte.


2. Can you explain the write_info example what they are saving in this statement

insert into tom_Table values (who, description, file);


This is not the document table??
Tom Kyte

Followup  

February 21, 2008 - 11:15 am UTC

1) that is what I said, yes.

2) is it not obvious? from the documentation again:

<html>
   <head>
      <title>test upload</title>
   </head>
   <body>
   <FORM     enctype="multipart/form-data"
      action="pls/mydad/write_info"
      method="POST">
      <p>Author's Name:<INPUT type="text" name="who">
      <p>Description:<INPUT type="text" name="description"><br>
      <p>File to upload:<INPUT type="file" name="file"><br>
      <p><INPUT type="submit">
   </FORM>
   </body>
</html>

When a user clicks Submit on the form:

   1.

      The browser uploads the file listed in the INPUT type="file" element.
   2.

      The write_info procedure then runs.


see how they have a form with "who", "description" and "file", that is what your procedure will receive, whatever they typed in.

and yes, that is not the document table, it has the wrong structure

February 21, 2008 - 12:57 pm UTC

Reviewer: A reader

Tom:

This is where I am confused at?

Does the write_info need to be corrected to insert data into "my_document_table".

Based on a basic html form, can you write the correct insert statement to "my_documents_table" and "tom_table" where the pointer will be. do you need two inserts like this

insert into my_documents_table(....) values (...)
insert into tom_table(...) values (...)

thanks you,
Tom Kyte

Followup  

February 21, 2008 - 4:59 pm UTC

you need to do nothing to the document table.

we put the document in there
we give you the key to the row we just put there.

you do not insert into the document table
we did

and we give you the key to that row.


I'm running out of ways to say it.

you create a form. form has fields. you get those fields as inputs.

If there was a file, we put the uploaded file into the document table you told us to - and we send you the key to that row, not the file contents, the key.

You may then do with that whatever you like.

upload

February 21, 2008 - 6:08 pm UTC

Reviewer: A reader

You are right. I did both procedures.

Here is what I got


DOCUMENTS_TABLE

NAME MIME_TYPE DOC_SIZE DAD_CHARSE LAST_UPDA
-------------------- -------------------- ---------- ---------- ---------
CONTENT_TYPE
--------------------
F1290/b1.txt text/plain 0 ascii 21-FEB-08
BLOB



I only loaded b1.txt file and oracle added this key before it and saved it to the my_Table


MY_table

WHO DESCRIPTION FILE_NAME
-------------------- ------------------------------ --------------------
ABC DEF F1290/b1.txt


1. DO I need to make that MY_TABLE.FILE_NAME a foreign key to the documents table.

2. The documents table did not have a primary key. do i need to create one on "NAME".


3. I could not display the "BLOB_CONTENT" column in documents_table because it is BLOB type.
is this where the file is actually stored?

How do i display the contents of file in sql*plus?

4. Are you saying there is no need to store it as CLOB, and I can set the MIME type in the browser so it will be displayed as text file. My need is for user to click a link and see the contents. They do not care how it is stored in database.

Thank you,



Tom Kyte

Followup  

February 21, 2008 - 7:01 pm UTC

1) it would not hurt, it would give you what is known as data integrity :)

2) it has a unique constraint

3) yes, it is actually stored there, if you want to peek at a bit of it in sqlplus use utl_raw.cast_to_varchar2(dbms_lob.substr( blobcolumn, 4000, 1 ) ) in the select - or download it to your browser

4) the mime type is in the documents table, the download process will send that mime type to the browser and the browser will do the right thing based on that.

Upload and download using mod_plsql

February 27, 2008 - 4:08 am UTC

Reviewer: mmatar from Gaza

Tom:
I have created the documents table and configured the mod_plsql to upload into that table.
I could successfully upload files into this table and download the files from it.

This way, when any html form requests the HTTP server with the specified link for the upload procedures
through the secified dad - http://httpserver.mycomp.com:7778/pls/mydad/upload_proc - it successfully upload
the file even if the procedure has no sql statements;

I have two questions:
1. How may I make some validations to complete the upload, i.e, not any person can upload files?
2. If I need to upload into more than one table, do I configure more than one dad or there is another way?
Tom Kyte

Followup  

February 27, 2008 - 4:21 am UTC

1) do you mean "secure your application"? not really sure what you mean there. But, you would have a procedure invoked after the upload - and you can do whatever validation of anything you want. You can also make your URL require authentication - and only permit those that should be able to run it, actually run it of course as well.

2) we upload to that table, if you need it in another table, you move it - I would suggest you just leave it there - in the document table, and use the key we give you to "point" to it from your application tables.

upload

March 05, 2008 - 8:01 pm UTC

Reviewer: A reader

Tom:

Do you know what causes this error and how to fix it?
is it possible to see the whole file in sql*plus or there is limitation on size?

 
select utl_raw.cast_to_varchar2(dbms_lob.substr(blob_content,4000,1) ) from document_Table
SQL> /
ERROR:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1

Tom Kyte

Followup  

March 06, 2008 - 8:19 am UTC

well, that is a plsql error

and you show sql

so something very relevant is MISSING here.


like the actual code that gets the error, I'll guess that you have something wrong in your code.

To A reader

March 06, 2008 - 8:35 am UTC

Reviewer: Michel Cadot from France


Casting 4000 bytes raw data to varchar2 may be greater than 4000 bytes depending on your character set.

Regards
Michel

wpg_docload direct access from a browser

March 17, 2008 - 2:24 pm UTC

Reviewer: Ray DeBruyn from Ottawa, Canada

Hi Tom,

I've used the image upload and download capability for a number of years. It's quite useful and easy to use.

Recently, I've looked at adding security on the images. I don't fill out the documents path and dowload procedure on the DAD. Instead, I pass the id of a record that stores the filename and then I call wpg_docload passing the filename. I also pass other parameters for security (checksum) validation. It all works great.

I found that I can do a direct call to wpg_docload from the browser. I never touch the sys packages, but see no way around it to plug the hole. Do you see any problem adding a line like "IF UPPER(owa_util.get_cgi_env('PATH_INFO')) LIKE '%PG_DOCLOA%' THEN RAISE_APPLICATION_ERROR(..." to the wpg_docload package?

Tom Kyte

Followup  

March 24, 2008 - 8:54 am UTC

you can protect that URL at the application/web server level (making it require a password no one knows)

you can use mod_rewrite to rewrite the url to be YOUR url.


do NOT modify any of the supplied code, there are ways to prevent a URL from being executed

Having some issue using these

May 15, 2008 - 7:57 am UTC

Reviewer: Dibyendu Chakraborty from India

Hi Tom,
I configured mod_plsql. In the configuration I gave DAD name as MYDAD, authentication mode as single sign-on, session cookie name blank, Package/Session Management Type as Stateless(Reset Package state), enable connection pooling as yes, and other parameters like Default (Home) Page, Document Table, Document Access Path, Document Access Procedure, Extensions to be uploaded as Long Raw, Path Alias, Path Alias Procedure as blank. After that I wrote the same packages which you have given in the first comment. Then I was trying to open the link. But it gave me the following error.
Service Temporarily Unavailable
The server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.
--------------------------------------------------------------------------------

Oracle HTTP Server Powered by Apache/1.3.22 Server at CALV4B14.cts.com Port 80. the link was http://10.227.4.63/pls/MYDAD/image_get.gif?p_id=1
Could you please tell me what went wrong here? Thanks a lot.
Tom Kyte

Followup  

May 19, 2008 - 12:01 pm UTC

did you do the simple thing first - just try to run ANYTHING, like "hello world" routines to test your configuration?

Newer versions

May 26, 2008 - 3:42 pm UTC

Reviewer: Keith Hensel from Ontario, Canada

While I had no luck with the provided solutions, the following did work:

  select f.image into img_blob
  from demo f
  where f.f_id = p_arg;
    
  owa_util.mime_header('image/jpeg');
  htp.p('Content-Length: ' || dbms_lob.getlength(img_blob)); 
  owa_util.http_header_close;
    
  wpg_docload.download_file(img_blob);


I believe this only works in newer versions, though I'm not entirely sure.

saving xml

June 17, 2008 - 3:13 pm UTC

Reviewer: mike

Tom:

I am using 9iR2, and receiving MANY xml files and saving it into the documents table via mod_plsq

Do you see

1. Any negative impacts on leaving the XML file as a BLOB in documents table instead of sotring it as CLOB in another table.

2. Currently my requirement is to display the XML file stored via the web or power builder client screen. on web you set the mime type. on pb there must be a conversion function before display. do you see any issue for diaply purposes? What about in the future if i need to query the XML file tags using XPATH? Do i need to convert it to CLOB or XMLTYPE.

3. How do ou convert the BLOB file and save it to another table as CLOB or XMLTYPE?
Tom Kyte

Followup  

June 17, 2008 - 3:36 pm UTC

1) if you don't need to query it relationally /manipulate it in the database, store it however works best for you.

2) I know nothing about powerbuilder....

you can query the XML as a blob

3) dbms_lob has blob to clob conversion functions. You would just xmltype() it to conver to xmltype

file

June 17, 2008 - 5:52 pm UTC

Reviewer: A reader

Tom:

1.  For some reason I feel that storing the XML file as CLOB in the my_tests_table is cleaner than leaving it in the documents table as BLOB with all other documents.

Do you see any harm of doing that or it is waste of time.

2. Is this the correct function to use and why it is not working


 SQL> CREATE OR REPLACE FUNCTION BLOB2CLOB(L_BLOB BLOB) RETURN CLOB IS
  2     L_CLOB         CLOB;
  3     L_SRC_OFFSET      NUMBER;
  4     L_DEST_OFFSET  NUMBER;
  5     L_BLOB_CSID       NUMBER := DBMS_LOB.DEFAULT_CSID;
  6     V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
  7     L_WARNING         NUMBER;
  8     L_AMOUNT  NUMBER;
  9   BEGIN
 10     DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
 11     L_SRC_OFFSET     := 1;
 12     L_DEST_OFFSET := 1;
 13     L_AMOUNT := DBMS_LOB.GETLENGTH(L_BLOB);
 14     DBMS_LOB.CONVERTTOCLOB(L_CLOB,
 15                            L_BLOB,
 16                            L_AMOUNT,
 17                            L_SRC_OFFSET,
 18                            L_DEST_OFFSET,
 19                            1,
 20                            V_LANG_CONTEXT,
 21                            L_WARNING);
 22    RETURN L_CLOB;
 23    END;
 24  / 

Function created.

SQL> SELECT NAME,MIME_TYPE,BLOB2CLOB(BLOB_CONTENT) from DOCUMENTS_TABLE;
ERROR:
ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 653
ORA-06512: at "XXX.BLOB2CLOB", line 14


3.  Can you do the insert in one shot or you have to i

--insert into my_tests_table
--select the blob from my_documents_table
--update the record in my_tests_table with BLOB2CLOB value.

thanks,

Tom Kyte

Followup  

June 18, 2008 - 12:49 pm UTC

1) as long as your character set in the database supports the characterset needed by XML

2) I'd suspect an empty blob

ops$tkyte%ORA11GR1> create table t ( id int primary key, x blob );

Table created.

ops$tkyte%ORA11GR1> insert into t values ( 1, utl_raw.cast_to_raw( 'how now brown cow, hello world!' ) );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( 2, empty_blob() );

1 row created.

ops$tkyte%ORA11GR1> insert into t values ( 3, NULL );

1 row created.

ops$tkyte%ORA11GR1> select blob2clob( x ) from t where id = 1;

BLOB2CLOB(X)
-------------------------------------------------------------------------------
how now brown cow, hello world!

ops$tkyte%ORA11GR1> select blob2clob( x ) from t where id = 2;
ERROR:
ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 795
ORA-06512: at "OPS$TKYTE.BLOB2CLOB", line 14



no rows selected

ops$tkyte%ORA11GR1> select blob2clob( x ) from t where id = 3;
ERROR:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 795
ORA-06512: at "OPS$TKYTE.BLOB2CLOB", line 14



no rows selected



3) I would probably suggest:

for x in ( select * from old )
loop
insert into new values ( ... empty_clob() ) returning clob_col into l_clob;
dbms_lob.converttoclob( l_clob, old.blob );
end loop;


no temporary, just read old blob, write new clob. If table is large - do it in "parallel" with rowid ranges
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10498431232211

upload file

June 19, 2008 - 6:15 pm UTC

Reviewer: A reader

Tom:

I am not sure I did understand your technique with the loop but this is explains what i am trying to do.
Basically I have an html form that submits a bunch of xml files via mod_plsql
and i want to save it as CLOB in a test1 table. The name here is the pointer that oracle creates for the file uplaoded.

It seems the conversion fails at the 4K bytes xml file. The small one works fine.

is there a limit on the BLOBtoCLOB conversion and how would you write the code to get the updates required.



SQL> desc test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 X_NAME                                             VARCHAR2(128)
 X_FILE                                             CLOB
 Y_NAME                                             VARCHAR2(128)
 Y_FILE                                             CLOB
 Z_NAME                                             VARCHAR2(128)
 Z_FILE                                             CLOB

SQL> desc documents_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(128)
 MIME_TYPE                                          VARCHAR2(128)
 DOC_SIZE                                           NUMBER
 DAD_CHARSET                                        VARCHAR2(128)
 LAST_UPDATED                                       DATE
 CONTENT_TYPE                                       VARCHAR2(128)
 CONTENT                                            LONG RAW
 BLOB_CONTENT                                       BLOB



SQL> execute print_table('select * from test1');
ID                            : 3
X_NAME                        : F24098/x.xml.xml
X_FILE                        :
Y_NAME                        : F9283/y.xml.xml
Y_FILE                        :
Z_NAME                        : F14714/z.xml.xml
Z_FILE                        :
-----------------


I want to get the CLOB of each file into X_FILE, Y_FILE, Z_FILE

PROCEDURE SAVE_TEST1 (
     p_x_name              IN   VARCHAR2   DEFAULT   NULL,
     p_x_file              IN   VARCHAR2   DEFAULT   NULL,
     p_y_name              IN   VARCHAR2   DEFAULT   NULL,
     p_y_file              IN   VARCHAR2   DEFAULT   NULL,
     p_z_name              IN   VARCHAR2   DEFAULT   NULL,
     p_z_file              IN   VARCHAR2   DEFAULT   NULL)
AS

     v_clob                        clob;
     v_blob                        blob;
     v_dest_offset                 integer := 1;
     v_src_offset                  integer := 1;
     v_lang_context                integer := 0;
     v_warning                     integer;
     
     v_x_blob              blob;
     v_y_blob              blob;
     v_z_blob              blob;
     v_x_clob              clob;
     v_y_clob              clob;
     v_z_clob              clob;
     l_id                  integer;

BEGIN
   dbms_lob.createtemporary(v_x_clob,FALSE);
    dbms_lob.createtemporary(v_y_clob,FALSE);
     dbms_lob.createtemporary(v_z_clob,FALSE);

--insert the files - default is BLOB into documents table
   INSERT INTO TEST1(id,x_name,x_file,y_name,y_file,z_name,z_file)
       VALUES (test1_seq.nextval,p_x_name,p_x_file,p_y_name,p_y_file,p_z_name,p_z_file)
       returning id into l_id;
 
--get the blob from documents table
   SELECT BLOB_CONTENT into v_x_blob FROM documents_table WHERE name = trim(p_x_name);

   dbms_lob.converttoclob
       (v_x_clob, v_x_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,
        dbms_lob.default_csid, v_lang_context, v_warning );

   UPDATE TEST1
        SET x_file = v_x_clob WHERE id = l_id;

   SELECT BLOB_CONTENT into v_y_blob FROM documents_table WHERE name = p_y_name;

   dbms_lob.converttoclob
       (v_y_clob, v_y_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,
        dbms_lob.default_csid, v_lang_context, v_warning );

     UPDATE TEST1
        SET y_file = v_y_clob WHERE id = l_id;

   SELECT BLOB_CONTENT into v_z_blob FROM documents_table WHERE name = p_z_name;

   dbms_lob.converttoclob
       (v_z_clob, v_z_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,
        dbms_lob.default_csid, v_lang_context, v_warning );
 
 UPDATE TEST1
        SET z_file = v_z_clob WHERE id = l_id;

   COMMIT;
  htp.p('saved');

END;



Thu, 19 Jun 2008 21:57:21 GMT

ORA-22925: operation would exceed maximum size allowed for a LOB value
ORA-06512: at "SYS.DBMS_LOB", line 653
ORA-06512: at "SAVE_TEST1", line 46
ORA-06512: at line 64


Tom Kyte

Followup  

June 19, 2008 - 6:51 pm UTC

no create tables

no look

if I cannot compile and run your code and see what you see, I don't want to see it.


I am not a compiler.
I'll be glad to help you debug your code.

but you have to help me help you.

file upload

June 19, 2008 - 7:33 pm UTC

Reviewer: A reader

Tom:

sorry, my fault. I appreciate your help.
You have the code for stored procedure that saves the files in the previous post.

create table DOCUMENTS_TABLE (
NAME VARCHAR2(128) not null,
MIME_TYPE VARCHAR2(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR2(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR2(128),
CONTENT LONG RAW,
BLOB_CONTENT BLOB,
constraint PK_NLS_DOCUMENTS primary key (NAME)
)
/


create table test1 (
id number(38) not null,
x_name varchar2(128),
x_file clob,
y_name varchar2(128),
y_file clob,
z_name varchar2(128),
z_file clob )
/
I use this form to upload files using the web.

PROCEDURE TEST_TEST1
IS
BEGIN

htp.p('<HTML>');
htp.p('<HEAD>');
htp.p('</HEAD>');
htp.p('<BODY >');
htp.p('<span class="page-header"> Save Results </SPAN>');
htp.p('<HR COLOR="Brown">');
htp.p('<FORM enctype="multipart/form-data" action="save_test1" method="POST"> ');
htp.p('<TABLE width="100%" border=0 cellspacing=5>');
htp.p('<TR>');

htp.p('<TH>X_File</TH><TD><INPUT type="file" name="p_x_name"></TD></TR> ');
htp.p('<TH>Y_File</TH><TD><INPUT type="file" name="p_y_name"></TD></TR> ');
htp.p('<TH>Z_File</TH><TD><INPUT type="file" name="p_z_name"></TD></TR> ');

htp.p('</TABLE>');
htp.p('<HR COLOR="Brown">');

htp.p('<CENTER>');

htp.p(' <INPUT type="submit" value="Save Results" >');
htp.p(' </FORM>');
htp.p(' </body>');
htp.p(' </html> ');


END; -- Procedure
Tom Kyte

Followup  

June 20, 2008 - 10:10 am UTC

question: what is the PURPOSE of p_x/y/z_file??!?!?!
question: why are you using createtemporary
question: what is incomprehensible about:

for x in ( select * from old )
loop
  insert into new values ( ... empty_clob() ) returning clob_col into l_clob;
  dbms_lob.converttoclob( l_clob, old.blob );
end loop; 




anytime you have code that is block copied and a variable name changed - that means "I meant to use an array and a loop", or "I meant to use a procedure to which I pass the variable". Block copied code as you have in save_test1 is "really really bad"

I'll do a couple of things here. First, I am going to make it so others can actually run your test case - they take your documents_table and then:

ops$tkyte%ORA10GR2> declare
  2      type array is table of varchar2(30);
  3      l_files array := array('x','y','z');
  4      l_blob  blob;
  5  begin
  6      for i in 1 .. l_files.count
  7      loop
  8          insert into documents_table(name,blob_content) values ( l_files(i), empty_blob() ) returning blob_content into l_blob;
  9          for j in 1 .. 10
 10          loop
 11              dbms_lob.writeappend( l_blob, 32000, utl_raw.cast_to_raw( rpad(l_files(i),32000,l_files(i) ) ) );
 12          end loop;
 13      end loop;
 14      commit;
 15  end;
 16  /

PL/SQL procedure successfully completed.


we fill it with some data.

Next, we FIX your table, test1 will not have columns repeated like that (tomorrow, you'll be asking "how can I do this for four files"). We'll fix the table as such:
ops$tkyte%ORA10GR2> create table test1
  2  (
  3   id         number(38)  ,
  4   seq        number,
  5   name       varchar2(128),
  6   file_data  clob,
  7   constraint test1_pk primary key(id,seq)
  8  )
  9  /

Table created.


and now for the "convert" procedure. Notice: loops, insert returning, no temporaries, simple, small...



ops$tkyte%ORA10GR2> create or replace
  2  PROCEDURE SAVE_TEST1 (
  3       p_x_name              IN   VARCHAR2   DEFAULT   NULL,
  4       p_y_name              IN   VARCHAR2   DEFAULT   NULL,
  5       p_z_name              IN   VARCHAR2   DEFAULT   NULL
  6       )
  7  AS
  8      type vc_array is table of varchar2(30);
  9      l_files vc_array := vc_array( p_x_name, p_y_name, p_z_name );
 10
 11      l_clob clob;
 12      l_blob blob;
 13      l_warning number;
 14      l_id      number;
 15      l_src_offset number := 1;
 16      l_dest_offset number := 1;
 17      l_cs_id       number := dbms_lob.default_csid;
 18  BEGIN
 19      select test1_seq.nextval into l_id from dual;
 20      for i in 1 .. l_files.count
 21      loop
 22          insert into test1 ( id, seq, name, file_data )
 23          values ( l_id, i, l_files(i), empty_clob() )
 24          returning file_data into l_clob;
 25
 26          select blob_content into l_blob
 27            from documents_table
 28           where name = l_files(i);
 29
 30          l_src_offset := 1;
 31          l_dest_offset := 1;
 32          dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob), l_src_offset, l_dest_offset, 1, l_cs_id, l_warning );
 33      end loop;
 34  END;
 35  /

Procedure created.




and now....



ops$tkyte%ORA10GR2> select name, dbms_lob.getlength(blob_content) from documents_table;

NAME     DBMS_LOB.GETLENGTH(BLOB_CONTENT)
-------- --------------------------------
x                                  320000
y                                  320000
z                                  320000

ops$tkyte%ORA10GR2> exec save_test1( 'x', 'y', 'z' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select id, seq, name, dbms_lob.getLength(file_data) len,dbms_lob.substr(file_data,20,1) data
  2    from test1;

        ID        SEQ NAME            LEN DATA
---------- ---------- -------- ---------- ----------------------
         1          1 x            320000 xxxxxxxxxxxxxxxxxxxx
         1          2 y            320000 yyyyyyyyyyyyyyyyyyyy
         1          3 z            320000 zzzzzzzzzzzzzzzzzzzz




but, if you ask me, I'd just drop test1 altogether, leave the data in documents, you already have it - you do not need to do this.

file

June 20, 2008 - 4:54 pm UTC

Reviewer: A reader

Tom:

This is excellent and genius work. i see your point about the one-to-many relationship but unfortunately that old table/db sort of exists and there is a lot of "red tape" to get changes done. I know you do not like to hear this but it is a reality we have to live with sometimes.

1. I am little confused on how to use the DOCUMENTS_TABLE.

MOD_PLSQL by default uploads all documents to DOCUMENTS_TABLE and i am not supposed to add columns or delete columns to this table or do inserts (format specified by oracle docs)? It will automatically do it whenever you have a file upload for that schema and save the file pointer into the other table.

2. Does your code have any limit on the size of the BLOB to be converted.


3. I wrote this. It worked for one file without using a loop. The loop is creating an error.

do you see any issues or how you would you rewrite it in the brilliant ways you usually do.

PROCEDURE SAVE_TEST3 (
p_x_name IN VARCHAR2 DEFAULT NULL,
p_y_name IN VARCHAR2 DEFAULT NULL,
p_z_name IN VARCHAR2 DEFAULT NULL)
AS

type vc_array is table of varchar2(30);
l_files vc_array := vc_array( p_x_name, p_y_name, p_z_name );

l_blob blob;
x_clob clob;
y_clob clob;
z_clob clob;
l_warning number;
l_id number;
l_src_offset number := 1;
l_dest_offset number := 1;
l_cs_id number := dbms_lob.default_csid;
c_cnt integer;

TYPE clob_files IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
l_clob clob_files;


BEGIN
dbms_lob.createtemporary(x_clob,FALSE);
dbms_lob.createtemporary(y_clob,FALSE);
dbms_lob.createtemporary(z_clob,FALSE);


insert into test1(id,x_name,y_name,z_name)
values (test1_seq.nextval,p_x_name,p_y_name,p_z_name)
returning id into l_id;

for i in 1 .. l_files.count
loop
if (l_files(i) is not null) then

select blob_content into l_blob
from documents_table
where name = l_files(i);


l_src_offset := 1;
l_dest_offset := 1;
dbms_lob.convertToClob(l_clob(i), l_blob, dbms_lob.getLength(l_blob), l_src_offset,
l_dest_offset, 1, l_cs_id, l_warning );

end if;

end loop;

update test1
set x_file = l_clob(1),
y_file = l_clob(2),
z_file = l_clob(3)
where id = l_id;

COMMIT;
htp.p('saved');

END;


ORA-01403: no data found
ORA-06512: at "SAVE_TEST3", line 52
ORA-06512: at line 64

PROCEDURE : save_test3
URL : http://save_test3
PARAMETERS :
===========
p_x_name:
F24951/x.xml
p_y_name:
F26772/y.xml
p_z_name:
F7418/z.xml





Tom Kyte

Followup  

June 20, 2008 - 6:36 pm UTC

1) so, what is the point of confusion. I saw you say lots of facts - now - tell me what you cannot figure out?

2) only limited by the largest blob Oracle can hold.

3) I gave you functional code, I do not want you to use temporaries, you don't need or want them. take my code and work from there. I've already rewritten yours

file upload

June 20, 2008 - 6:53 pm UTC

Reviewer: A reader

Tom:

1. I misunderstood. I guess you have used documents_table to show me how data is loaded and you did not mean for me to insert data into it using the application. correct?

2. I was forced to use tempraries because i kept getting an error with UPDATE statement.
I did not know how to initalize the CLOB with UPDATE
your example was using INSERT and still can't uderstand the logic.

a) you inserted first an empty clob to initialize file_data
b) select the blob from documents_table
c) you did the conversion from blob to clob.

But how dow the CLOB data after conversion get back to test1 table. I can't figure that out.

Can you show me a small update example on how you do it.

Thanks a lot. You are truly great!
Tom Kyte

Followup  

June 20, 2008 - 9:15 pm UTC

1) the documents table is the standard table used by mod_plsql to perform file uploads into. We load it, you access it.

I loaded it with SAMPLE DATA in order to show you how to process the data it might contain. It was just a demo, we needed DATA in order to demo with.

2) update t set col = empty_clob() returning col into l_clob;


just like an insert.


22          insert into test1 ( id, seq, name, file_data )
 23          values ( l_id, i, l_files(i), empty_clob() )
 24          returning file_data into l_clob;
 25
 26          select blob_content into l_blob
 27            from documents_table
 28           where name = l_files(i);
 29
 30          l_src_offset := 1;
 31          l_dest_offset := 1;
 32          dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob), l_src_offset, 
l_dest_offset, 1, l_cs_id, l_warning );


you really couldn't understand that logic? Really?


You need to insert into your test1 anyway, just INSERT three empty_clobs(), use the returning clause to get them back and dbms_lob.convertToClob into them


insert into test1 (......, file1, file2, file3 ) values ( ...., empty_clob(), empty_clob(), empty_clob() ) returning ..........


upload

June 21, 2008 - 12:21 am UTC

Reviewer: A reader

Tom:

Does the conversion API write back to those empty CLOB variables and save them to the test1 table.

the return clause is like a select statement that is reading and not writing data?

I have been thinking about your design of the small table that holds all the files in one column instead of creating 10 columns for 10 files.

It is really good. i am gonna go through red tape for this one.

1. It sure makes things look easier from a maintenance, scalibility and query point in the future. correct? anything else.

2. I assume i can add another column for file_Type like "X" or "Y" or "Z" to be able to query by file type. right?just use an array to set the values in the routine.

SELECT file_data from test1 where file_Type='X'

instead of

selECT x_file from test1

3. If one column that is 2000 bytes that will be passed like a string can i still store it in this file as a CLOB since I will have one CLOB column for all results.

4. Would i be able in the same routine you had UPDATE or INSERT into another table in another database connected via db link that has this format

ID number(10)
X_FILE CLOB
Y_FILE CLOB
Z_FILE CLOB

thanks,

Tom Kyte

Followup  

June 21, 2008 - 4:32 pm UTC

... Does the conversion API write back to those empty CLOB variables and save them
to the test1 table.
...

you need to read about lobs - all about lobs - so that you understand what you are dealing with and how to use them.

The short answer to your question is: Yes, of course, it is after all exactly what I demonstrated in my example. When you retrieve a lob locator from the database, it is just like a FILE - just like you "opened a file", when you write to the lob locator, you are writing to the database. When you read from it, you are reading from the database. A lob locator is a whole lot like a file handle (big time like that)

1) it would be more succinct to say "it is the only correct way to implement this in a relational database - the 10 column approach makes us look bad and people will laugh at us"

2) this is called data modeling. Yes, you can add other attributes of interest and where on them, of course.

3) you need to read about clobs. You have a clob column - every row is a separate and distinct clob. One row could have a NULL clob, another an empty clob, another a clob with 2000 bytes and yet another with a clob holding 2gb.

Just like you can have different dates in different rows
or numbers in different rows
or names of radically different lengths in different rows

all using the same column..


4) you just lost me entirely. You can only copy a clob over a database link, you cannot retrieve a lob locator from a remote database and write to it.

upload

June 21, 2008 - 9:33 pm UTC

Reviewer: A reader

tom:

Sorry I did not explain it well.

The test1 table will be a child table to tests with 1 to many relationship.
The test results are sent via different files. One of the results "First_result" is string up to 2000 bytes.
would you stick in tests parent table OR just include as a CLOB in the test1 table and have everything there


tests
------
id PK,
book_no,
test_date,
test_result_PF,
.....

test1_files
---------
id number(38) PK,
seq number PK,
book_no number
name varchar2(128),
file_type varchar2(10),
file_data clob,


2. Concerning #4,my requirement is to save all results into test1_results in DATABASE A, then update the last result sent with a record in a table in DATABASE B. The table structure in DATABASE B is old and unnormalized.

TABLE_B
--------
book-No
file_x
file_y
file_z

Would i be able to save the CLOBS in the loop in save_test1 and then do an insert



TYPE t_clob IS TABLE OF VARCHAR2 INDEX BY BINARY_INTEGER;
v_clob t_clob;

LOOP
....
dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob), l_src_offset,
l_dest_offset, 1, l_cs_id, l_warning );
v_Clob(i) := l_clob;
END LOOP;

INSERT INTO TABLE_B@db_link(book_no,file_x,file_y,file_z) values (100,v_clob(1),v_clob(2),....)


Tom Kyte

Followup  

June 21, 2008 - 10:55 pm UTC

1)
do you have a data modeler at your job you can work through things like this with? If not, why not?

is first_result going to ever turn into "second first result", and "third (and probably final) first result" and then "sorry, I have a 4th first result"

Or is first_result an attribute of every 'test' and is always going to be <= 2000 bytes and will always be "singular or zero in cardinality"

2) yes, you can insert a clob over a database link - I said that right above "You can only copy a clob over a database link,"

but you won't use those LOB LOCATORS

you will

insert into table@remote
select ..... from local;


file

June 22, 2008 - 11:17 am UTC

Reviewer: A reader

<Or is first_result an attribute of every 'test' and is always going to be <= 2000 bytes and will always be "singular or zero in cardinality" >

yes this is correct statement. think of it this way.

I have a book. I test it by running software on it. The end result is:

1. a brief 2000 byte test result (xml)
2. a moderate test result file (xml)
3. a detail test result file (xml)

Then the same process will send other files like:

1. outline description file (xml)
2. index listing file (xml)
3. navigate list (xml)
etc.

Each time a test is conducted on that book a new set of data is sent and saved (saving a new record for audit trail)

What I am doing is store the high level test data (1-1) in TESTS table and the files in the TESTS_FILES table.

1. would you store the brief result in the parent table as 2000 byte field or as part of the CLOB column in the DETAILS/CHILD TESTS_FILES table.

2. Also, some modelers may look at this as

one book has one index, one book has one outline, one book has one navigation and may store everything in one table like this which you do not like.

TEST_NO
INDEX_FILE
OUTLINE_FILE
DETAIL_RESULT_FILE
SHORT_RESULT_FILE
.........


do you agree?
Tom Kyte

Followup  

June 22, 2008 - 9:38 pm UTC

... think of it this way. ...

all we needed was "yes". then it certainly sounds like an attribute of the parent table. every test has one, every test has at most one, every test will have one that is 2000 bytes or less.

upload

June 22, 2008 - 10:25 pm UTC

Reviewer: A reader

Tom:

Using the same data relationship logic you used (every test has ONE short result string), can't you say that every test has one INDEX file, one navigation file, one outline file and one detail result file and one moderate result file which implies you stick everything in the parent TESTS table and no need for a child table.
Tom Kyte

Followup  

June 23, 2008 - 7:29 am UTC

ugh. I just give up.


upload file

June 23, 2008 - 6:52 pm UTC

Reviewer: A reader

I implemented what you suggested. but one last question on how you do the insert or update over a DB link to the other table WITHOUT using lob locators.

Here are the scripts and code and error.

create table test1
(id number(38),
seq number,
bkno number(10),
name varchar2(128),
file_type vachar2(10),
file_data clob
constraint test1_pk primary key(id,seq)
)
/

This table is in a second database that has a link to the first one.

create table TEST_DATA
(bkno number(10),
x_file CLOB,
y_file CLOB,
z_file CLOB)

PROCEDURE SAVE_TEST1 (
p_x_name IN VARCHAR2 DEFAULT NULL,
p_y_name IN VARCHAR2 DEFAULT NULL,
p_z_name IN VARCHAR2 DEFAULT NULL)

AS
type vc_array is table of varchar2(30);
l_files vc_array := vc_array( p_x_name, p_y_name, p_z_name );
l_file_type vc_array := vc_array ('X','Y','Z');
l_clob clob;
l_blob blob;
l_warning number;
l_id number;
l_src_offset number := 1;
l_dest_offset number := 1;
l_cs_id number := dbms_lob.default_csid;
x_clob clob;
y_clob clob;
z_clob clob;
v_bkno number;

BEGIN
select test1_seq.nextval into l_id from dual;
for i in 1 .. l_files.count
loop
if (l_files(i) is not null) then
insert into test1 ( id, seq, name, file_type,file_data )
values ( l_id, i, l_files(i), l_file_type(i), empty_clob() )
returning file_data into l_clob;



select blob_content into l_blob
from nls_documents
where name = l_files(i);

l_src_offset := 1;
l_dest_offset := 1;
dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob), l_src_offset,
l_dest_offset, 1, l_cs_id, l_warning );


CASE l_file_type(i)
WHEN 'X' THEN x_clob := l_clob;
WHEN 'Y' THEN y_clob := l_clob;
WHEN 'Z' THEN z_clob := l_clob;
END CASE;

end if;
end loop;


------------Tom this is where i need your help at

SELECT bkno into v_bkno from test1 where id = l_id;

UPDATE test_data@db_link
x_file = x_clob,
y_file = y_clob,
z_file = z_clob
where bkno = v_bkno;

IF (SQL%rowcount=0) then

insert into test_data@db_link ( bkno, x_file,y_file,z_file )
values ( v_bkno, x_clob, y_clob, z_clob );

END IF;

-------------------------------


commit;
htp.p('saved');

END;

ORA-00942: table or view does not exist
ORA-02063: preceding line from DB_LINK
ORA-06512: at "SAVE_TEST1", line 64
ORA-06512: at line 64


Tom Kyte

Followup  

June 23, 2008 - 9:16 pm UTC

if you code a procedure that just does

begin
for x in (select * from test_data@db_link ) null; end loop;
end;

does it work. The problem here has nothing to do with clobs, everything to do with "table or view DOES NOT EXIST"

and I already told you - you cannot use lob locators over a dblink

yet, you are using x_clob, y_clob, z_clob - which are LOB LOCATORS and trying to send them OVER A DATABASE LINK

but I've already told you "that does not work"

and you seem to acknowledge that you heard me say that "but one last question on how you do the
insert or update over a DB link to the other table WITHOUT using lob locators.
"

but you are clearly doing that?!?!?!@?!?!?

file upload

June 23, 2008 - 11:55 pm UTC

Reviewer: A reader

Tom:

You gave me that hint before and i missed it. it works for one file. The problem is if i cant save the CLOB into variables ( and I thought variables would be the file and not the LOB locator) and use those in iNSERT statement how can i get all the files from 3 rows in TEST1 into 1 row in TEST_DATA. Some files may be null too.



insert into test_data(bkno,x_file)
select bkno,file_data from test1 where id = l_id and file_Type = 'X';

how do you modify this to do 3 or more files:

insert into test_data(bkno,x_file,y_file,z_file)
select ......

if record is there then i have to do
update test_data
set x_File = file_data (where file_Type = 'X'),
y_file = file_data (where filE_type = 'Y'),
......
where bkno = l_bkno;
Tom Kyte

Followup  

June 24, 2008 - 4:57 am UTC

write a query that returns all three??? that is all.

what is bkno, why is it in this table, what is id - I presume that is part of the key (id,file_type). Why is bkno in this table, this is a repeating group, bkno would be constant for a given ID???

just write a query that selects all three as you need them.

upload

June 24, 2008 - 9:22 am UTC

Reviewer: A reader

Tom:

id is just a sequence for test number. yes book number is constant for a given id in test1 table. You have:
1 Book can have many tests (this is not listed here)
1 test can have many files (this is test1 table)


test1
(id number(38) ---->Sequence for test_no (PK),
seq number ----->File Number assigned the value of variable i in loop (PK),
bkno number(10)---->Book Number the test was done for,
name varchar2(128)---->Name of BLOB file,
file_type vachar2(10) ---->Type of XML file stored(MODERATE, LONG, INDEX, TOC, NAVIGATION, etc)
file_data clob--------->the xml file
constraint test1_pk primary key(id,seq)
)
/

100,1,AB100,F1234/index.xml,INDEX,index_file
100,2,AB100,F1235/TOC.xml,TOC,toc_file
100,4,AB100,F1244/Long.xml,LONG,long_result_file


This is the old existing table in second database.

TEST_DATA
(bkno number(10) (PK),
x_file CLOB,
y_file CLOB,
z_file CLOB)

AB100,index_file,toc_file,long_result_file


You say just write a query to select all three. that is easy but then how you stick it as one row into the other table.

select * from test1 where id = l_id;

Is not this the job of PIVOT QUERY on TEST1?

--delete a record if it exists
delete from test_data@d_link where bkno = (select bkno from test1 where id = l_id)

--insert the record in the other DB
insert into test_data@db_link values (pivot query on test1)
Tom Kyte

Followup  

June 24, 2008 - 11:00 am UTC

ops$tkyte%ORA10GR2> select d.id, d.bkno,
  2         (select data from t1 where id = d.id and ftype='x') ,
  3         (select data from t1 where id = d.id and ftype='y') ,
  4         (select data from t1 where id = d.id and ftype='z')
  5    from (select id, bkno from t1 where id = :id and rownum = 1) d
  6  /

        ID       BKNO
---------- ----------
(SELECTDATAFROMT1WHEREID=D.IDANDFTYPE='X')
-------------------------------------------------------------------------------
(SELECTDATAFROMT1WHEREID=D.IDANDFTYPE='Y')
-------------------------------------------------------------------------------
(SELECTDATAFROMT1WHEREID=D.IDANDFTYPE='Z')
-------------------------------------------------------------------------------
         1         42
hello world 1
hello world 2
hello world 3


upload

June 24, 2008 - 4:28 pm UTC

Reviewer: A reader

Tom:

thanks a lot, this is really genius work. is this "inline view" or "pivot query" solution.

would you recommend to do the update record of test_data (if exists) before trying insert or just delete the record and insert a new one into test_data.

I do not even think the update with CLOBs over a DB will work. do you agree?
Tom Kyte

Followup  

June 24, 2008 - 5:43 pm UTC

this is scalar subqueries.

tell you what, TEST your last idea - see what happens.

upload

June 25, 2008 - 5:08 pm UTC

Reviewer: A reader

Tom:

This is really strange. When I do insert/select I get this lOB locator error again even though I am not selecting from remote DB. When I create a temp table and dump the file there in the same format as the one in he other DB the copy works. 

How do you explain this?

SQL> insert into test_data@db_link (id,x_file,y_file,z_file) 
  2  select d.id,(select file_data from test4 where id = d.id and file_type = 'X') X_FILE,
  3              (select file_data from test4 where id = d.id and file_type = 'Y') Y_FILE,
  4              (select file_Data from test4 where id= d.id and file_type = 'Z') Z_FILE
  5          from (select id from test4 where id = 70 and rownum = 1) d;
insert into test_data@db_link (id,x_file,y_file,z_file)
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-02063: preceding line from DB_LINK

SQL> create table t as select d.id,(select file_data from test4 where id = d.id and file_type = 'X'
) X_FILE,
  2              (select file_data from test4 where id = d.id and file_type = 'Y') Y_FILE,
  3              (select file_Data from test4 where id= d.id and file_type = 'Z') Z_FILE
  4          from (select id from test4 where id = 70 and rownum = 1) d;

Table created.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 X_FILE                                             CLOB
 Y_FILE                                             CLOB
 Z_FILE                                             CLOB
 
 
 SQL> insert into test_data@db_link select * from t;
 
 1 row created.
 
 SQL> commit;
 
Commit complete.

2.  How do you do the update of CLOB over a link? 

upload

June 25, 2008 - 6:37 pm UTC

Reviewer: A reader

Tom:

Is this how you do the update. it seems to work

SQL> update test_data@db_link 
       set x_File=(select file_Data from test4 where id = 70 and file_type = 'X'),
           y_file=(select file_Data from test4 where id=70 and file_type = 'Y'),
           z_file = (select file_data from test4 where id = 70 and file_type = 'Z')
       where id = 70;

1 row updated.

PICD> commit;

Commit complete.

Tom Kyte

Followup  

June 26, 2008 - 10:25 am UTC

you would use bind variables in real life of course.

upload

June 26, 2008 - 4:18 pm UTC

Reviewer: A reader

Tom:

yes of course, this was just a small test to see if the idea works or not.

1. Why did the insert into table@db_link (query) failed even though I am not selecting LOB column over a link (see error above).

do i have to create a TEMP_TEST_DATA in the local database with same structure for TEST_DATA and then copy from TEMP_TEST_DATA(local) to TEST_DATA(remote).

2. is it a good idea sometimes to use temp tables for copying data during the transaction and then deleting the data after you are done.
Tom Kyte

Followup  

June 26, 2008 - 4:44 pm UTC

it must have executed the scalar subqueries in a way I personally did not anticipate. I should have tested that, but did not.


upload

June 26, 2008 - 4:57 pm UTC

Reviewer: A reader

Tom:

I see.

1. would the pivot query be the same result or shall i try that?

2. Do you like the idea of creating a temp_test_data table locally to save results of query and then copy it over to the remote DB table and then delete it from temp table?
Tom Kyte

Followup  

June 26, 2008 - 5:14 pm UTC

I'd rather you just inserted without the files, then did your update to move the files over.

upload

June 26, 2008 - 5:44 pm UTC

Reviewer: A reader

Tom:

Do you mean like this (no need for temp table)?

update test_data@db_link
set col2=........
x_file = (select x_file from test4...)
y_file = (select y_file from test4....)


If (sql%rowcount=0) then

insert into test_data@db_link(col1,col2) values (v_col1,v_col2)
returning pk_col into l_pk_col;

update test_data@db_link
set x_file = (select x_file from test4 ...),
y_file = (select y_file from test4....);

end if;
Tom Kyte

Followup  

June 26, 2008 - 6:09 pm UTC

i mean

insert - but no clobs
followed by update


that is how you would "move" the clobs over - you would insert and then move then. You asked "how to do the insert", I responded "in this case, insert then update the clobs"

June 27, 2008 - 9:30 pm UTC

Reviewer: A reader

Tom:

it worked fine like you said.

1. With this update, I assume that I can't have more than one file of type X per test, otherwise the select will retrieve many files with ftype='X' and fail. correct?


SQL> update test_data@db_link 
       set x_File=(select file_Data from test4 where id = 70 and file_type = 'X'),
       where id = 70;

2.
I have an array that saves the ftype declared as follows:

  l_file_type vc_array := vc_array ('X','Y','Z');

This means, every time a new fie is introduced I will ahve to change the code. Since I have a validation table for thes file types (ftype,desc) wouldi be able to build the array from it? I think I have to number the file types to get the right sequence into the array that match the parameter names. correct?

thanks

  

Tom Kyte

Followup  

June 28, 2008 - 1:35 pm UTC

1) didn't understand what you were asking or what you are trying to do.


2) I don't know your "design" and what I know of the legacy design - I don't like (using columns where obviously rows should have been used - square peg, round hole problem)

why not let the DATABASE validate your data???

and don't you mean "every time the target database CHANGES IT'S SCHEMA, MY CODE WILL BE AFFECTED"? wouldn't they have to add a column??!?! You would just insert new rows (and a check constraint would verify that the file type is a valid one - no code change really - the only code changes you have to make is when they change THEIR BAD SCHEMA)

Blob & files

June 29, 2008 - 2:24 am UTC

Reviewer: Sawsan Anwar

Hi Tom,

I have this problem with blob

I create table demo
CREATE TABLE DEMO
(
ID INTEGER,
THEBLOB BLOB
)
TABLESPACE blobtabs
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
LOB (THEBLOB) STORE AS
( TABLESPACE blobtabs
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL;

create tow directories
CREATE OR REPLACE DIRECTORY FROMMY_FILES AS 'C:\DATA';
To get files from this directory

CREATE OR REPLACE DIRECTORY TOMY_FILES AS 'C:\DATA2';
To put files from db to this directory


and create procedure to insert files to blob field

CREATE OR REPLACE procedure LOAD_A_FILE
as
l_clob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theblob into l_clob;
l_bfile := bfilename( FROMMY_FILES' , 'ASP.PDF' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );

commit;

end LOAD_A_FILE;
/
it work and insert file into blob and size of file'ASP.PDF' in Windows =2.97 KB from properties of
file


and i do this proceure to get file from db


CREATE OR REPLACE procedure SAVE_TO_FILE
AS
vblob blob;
i2 number:=1;
amt number := 32676;
len number;
my_vr raw(32767);
l_output utl_file.file_type;
p_dir varchar2(30) default 'TOMY_FILES';
p_file varchar2(30) := 'TOASP.PDF';
Begin

l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
For l_cur in (SELECT theblob mylob FROM demo where id = 1)
Loop

len := DBMS_LOB.GETLENGTH(l_cur.mylob);
vblob := l_cur.mylob ;
dbms_output.put_line('Length of the Column : ' || to_char(len));

While (i2 < len) loop
dbms_output.put_line('i2 : ' || to_char(i2));
DBMS_LOB.READ(vblob,amt,i2,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);
i2 := i2 + amt ;

End loop;
dbms_output.put_line('len : ' || to_char(len));
utl_file.fclose(l_output);

End loop;

end SAVE_TO_FILE;
/
also
it work put FILE into myfolder
BUT THE size of file'TOASP.PDF' in Windows =3.11 KB from properties of file
I CAN'T OPEN FILE AT ALL

Tom what is the wrrong the procedure which i wrote it or the utl_file


Regards

upload

July 01, 2008 - 12:23 am UTC

Reviewer: A reader

Tom:

Is it normal to get an oracle error if you upload a zero byte file. it seems the BLOB to CLOB conversion fails since there is nothing to convert?

select blob_content into l_blob from documents_table
where name = l_files(i);
l_src_offset := 1;
l_dest_offset := 1;
dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob), l_src_offset,
l_dest_offset, 1, l_cs_id, l_warning );

*** ORA-01403: no data found
*** ORA-06512: at "SAVE_TEST4", line 123
*** ORA-06512: at line 64

Tom Kyte

Followup  

July 06, 2008 - 7:07 pm UTC

you are getting an error not from anything related to uploading.

you have an empty lob, nothing to process. No data to be found.

upload

July 08, 2008 - 8:25 pm UTC

Reviewer: A reader

Tom:

A client perl program using LWP is posting a few files (some 0 byte) and they are not getting saved into DOCUMENTS_TABLE. So it keeps getting a "No DATA Found" on this line when i try to select the BLOB file.

select blob_content into l_blob
from documents_table
where name = l_files(i);

The web server debug page tells me this is what gets passed

p_file1
/temp/_wr_twekkl

p_file2
/temp/Wk_erww

usually when I use HTML form I notice that oracle puts a sequence number before the file like

p_file
F1200/test.xml

I think they are not being sent as files? is there a way to confirm this at the oracle side or web server logs? This is secure call (https) so i cant sniff it and see the http message.

2. Do you think it could be the file name being invalid.
Tom Kyte

Followup  

July 08, 2008 - 8:40 pm UTC

if they are zero bytes....

are only zero byte files doing this - if so, well - they basically don't exist do they...

file upload

July 10, 2008 - 7:33 pm UTC

Reviewer: A reader

Tom:
It does have to do with 0 byte files. I created an html form and uploaded 0 byte files fine. I just skip the BLOB to CLOB conversion if file size is 0 and enter an empty CLOB

I think the client program was mixing/sending url parameters and form parameters and the files were not in the <form> tags. Can't oracle handle both type of parameters?

Is there a way to verify if files are moving from client to web server over an https link? I do not think they were.

Tom Kyte

Followup  

July 11, 2008 - 8:06 am UTC

clients do not post "form" tags.

Oracle does not process "form" tags.

Clients process form tags.

Client use the POST or GET protocol over http to send inputs to Oracle. Oracle processes those inputs. If a client doesn't send input, we don't process it.

We are back to my original point of long ago. It is a bad idea to use a simple "fling the stuff over http from a client not being driven by a human being" - as the ability to maintain, debug, see what is going on is very limited. You chose the most basic protocol with limited to no error handling, debugging, logging, etc capabilities.

file

July 11, 2008 - 9:13 pm UTC

Reviewer: A reader

Tom:

You are right. but i did not make that decision. I assume Web services is the standard protocol for application to application commuication.

The client has to do the error handling, debugging, logging, etc sort of same thing a human will do if he sits in front of a browser.

For light number of transactions it might work. Like if you are transfering files every night over http. I see a few applications do that without major issues.

Is there a way to see the files/data going from client to oracle http server over https. The sniffer would not show that.
Tom Kyte

Followup  

July 15, 2008 - 9:05 am UTC

a sniffer would show that. anything that can "dump packets from the network" would

download file

July 15, 2008 - 9:16 pm UTC

Reviewer: A reader

Tom:

upload works fine. I am having some issue with download

I have the following URL that calls a download procedure and I get an error on the WPG_DOCLoad. do you know why?


<a HREF= http://xxx.xx.xx/DAD/download_file >Download XML File</a>


PROCEDURE DOWNLOAD_FILE
is
l_lob clob;

begin
select my_file into l_lob
from temp_files
where rownum =1;

owa_util.mime_header( 'text/xml' );
wpg_docload.download_file(l_lob);
end;


ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.WPG_DOCLOAD", line 30
ORA-06512: at "XXX.DOWNLOAD_FILE", line 13
ORA-06512: at line 64


2. Would download only work from DOCUMENTS_TABLE or any other table that has CLOBs/BLOBs?

3. My DAD has doc_download for document access path variable. I used that and I did not get that working. Do i always have to use that in my URL for downloads.

Uploading and Downloading PDF's

July 16, 2008 - 6:24 am UTC

Reviewer: dinesh from Asia

Hi Tom,

I went through the entire post of uploading and downloading PDF's and the examples,explanation are fine.I need a solution for a scenario slightly related to it, hope so expecting the solution for the same.
SCENARIO:
I need to upload and download the PDF's through oracle way but the PDF's has to be stored and retrieved from the APP SERVER not the DATABASE. Let me know the way if you have the solution for the same.

Thanks,
Dinesh
Tom Kyte

Followup  

July 16, 2008 - 10:11 am UTC

then you won't be looking at the database for answers - think about it.....


you would have to refer to the documentation for your application server.


but - i would encourage you think long and hard about this. Data belongs in a database, not in a file system.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1011065100346196442

download

July 17, 2008 - 12:01 pm UTC

Reviewer: A reader

Tom:

Can you explain this.

When i have a procedure tha displays the CLOB like this it works fine.

PROCEDURE DOWNLOAD_FILE
is
l_lob clob;
begin
select my_file into l_lob
from temp_files
where rownum =1;
owa_util.mime_header( 'text/xml' );
htp.p(l_lob);
end;


On the other hand when I use wpg_docload I get an error.

PROCEDURE DOWNLOAD_FILE
is
l_lob clob;
begin
select my_file into l_lob
from temp_files
where rownum =1;
owa_util.mime_header( 'text/xml' );
wpg_docload.download_file(l_lob);
end;


ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.WPG_DOCLOAD", line 30
ORA-06512: at "XXX.DOWNLOAD_FILE", line 13
ORA-06512: at line 64



2. Some XML files stored in oracle reference a DTB document that can't be found because he DTD is not sent. The XML validation fails becuase of that.

is there a way around to display the XML format with these conditions (ie disregard the link to the DTD)?

If not, How can i check the file (if i is valid xml) and then tell the browser to display it as an XML other wise display it as a large string.


download

July 19, 2008 - 12:42 am UTC

Reviewer: A reader

Tom:

you do not have any small hint on the above. Would you use a print (htp.p) for a CLOB that has a file or use wpg_docload.
Tom Kyte

Followup  

July 19, 2008 - 6:37 am UTC

i have nothing to test with, so I cannot say why it is happening. I would guess once again that maybe you have a null or empty clob.

if you want me to look at things (and you do a lot, you are probably the "most posts by anyone"), you'll have to be very very explicit.

test cases = mandatory.

you can replicate this 100% for me, create a table, insert data and say "see, this happens"

Exporting data to pdf

August 13, 2008 - 7:21 am UTC

Reviewer: dinesh from Asia

Hi Tom,

i need your help regarding exporting data to pdf file from DB whether it will work as like with the following way.

owa_util.mime_header('application/pdf', FALSE);
htp.p ('Content-disposition: attachment; filename="Test.pdf"');
owa_util.http_header_close;
htp.p('Report');

if we use XL or WORD or PLAIN concepts its working fine.. but in case of PDF it doesnt.. can you please let me know the solution..

Thanks,
Dinesh
Tom Kyte

Followup  

August 18, 2008 - 9:14 am UTC

hah, "it doesn't work"

my car won't start


we are even now.


(you do know that: htp.p( 'Report' ) is not producing pdf right, you would sort of need to produce PDF if you say "I'm going to send you pdf now...")

dbms_lob.loadfromfile

October 09, 2008 - 11:53 am UTC

Reviewer: Nanthakumar from Kentucky USA

Tom,

Using dbms_lob.loadfromfile,I am not able to send a
0KB file. Oracle thorws ORA-21560 error.
My client needs even the 0kb file to be FTPed to them.
Could you please help me out?
Thanks,
Nanthakumar
Tom Kyte

Followup  

October 09, 2008 - 2:14 pm UTC

what does loadfromfile (which loads a file) have to do with ftp?

in any case, you could catch this error - verify the file is 0k, and just not load it. I presume you have inserted an empty_blob(), so just leave it as an empty blob - you are done.

dbms_lob.loadfromfile

October 09, 2008 - 4:25 pm UTC

Reviewer: Nanthakumar from Kentucky USA

Great!
Thanks a lot Tom, It works.

Is It Possible?

January 12, 2009 - 4:43 am UTC

Reviewer: S.Praveen Reddy from India

Hello Tom,
i wanted to store pdf file into oracle 10g database using my front end java (jsp or servlet). i am able to create a new pdf file with contents, but i don't know how to store that in oracle db. dear tom, please help me how to store pdf files in oracle. thank you very much in advance.
Tom Kyte

Followup  

January 12, 2009 - 9:05 pm UTC

you would use "file upload" and save it into a blob.

Now, since you want to write this in a java servlet/jsp - you'd have to read about how to do file upload to your application server

once your java code has the document, saving as a blob is fully documented - you just stream it into the database using a java stream

pseudostreaming media

January 25, 2009 - 9:29 am UTC

Reviewer: Grzesiek from Poland

Hi Tom,
I need to send open file from DB in my browser, but using wpg_docload i must download first a whole file, and using utl_raw generates problem with conversion of data. I want to open some audio file and play it until it is fully downloaded, but i don't want to use helix server or WMS. Is there any way to do it using only pl/sql?
thanks,

Grzesiek
Tom Kyte

Followup  

January 25, 2009 - 11:34 am UTC

If you want to do streaming video, that is, support things like:

a) start in the middle
b) let me slide back in time
c) let me skip to the end
d) let me pause

and so on, you need a protocol a bit "more feature rich" than http. In fact, you do not have to wait for the entire file to download to play it over http, you just need to find a viewer that reads an http stream - NOT a file on the file system.

read
http://www.mediacollege.com/video/streaming/overview.html


You can do "progressive streaming", but read the comment under "streaming"

True streaming video must be delivered from a specialized streaming server.

progressive streaming

January 25, 2009 - 4:13 pm UTC

Reviewer: Grzesiek from Poland

Hello Tom,
Thanks for fast and accurate reply, i managed to set it with those two methods. but i have huge problem. i was looking over the net how to solve it but i haven't found anything. i know it will be stupid question but i don't even know where to seek answer for it. when i start downloading file my connection to db is reseted after a couple of seconds. in both methods. my database have default configuration. only one thing i configured is listener.ora, and i have added dad, also on default configuration.
thanks,
Grzesiek
Tom Kyte

Followup  

January 26, 2009 - 1:13 am UTC

.. when i start downloading file my connection to db
is reseted after a couple of seconds. ...

define that a bit better, describe it more. I'm not sure I know what you mean. You said before you were able to download the entire file?

progressive streaming

January 26, 2009 - 7:42 am UTC

Reviewer: Grzesiek from Poland

Hello Tom,
I'm able to download files when connection speed is very fast(downloading from localhost), but when file is not downloaded within about 5sec for some reason my conection is reseted(i didn't tried earlier to download it with speed about 100kB/s, used only localhost where speed is about 100 times faster).
thanks,
Grzesiek

January 29, 2009 - 12:17 am UTC

Reviewer: sangeetha from India

I was trying to save a PDF file with the help of the example provided by you(demo table,image_get PKG,MY_FILES Directory).
I just followed the steps....
Now I have an entry in the table demo with ID as '1'.

I just tried to run the proc like this from toad ( dont know whether I am acting too Stupid !)

DECLARE
P_ID NUMBER;

BEGIN
P_ID := NULL;

IMAGE_GET.GIF ( 1 );
COMMIT;
END;


It's throwing the following Errors:-((...My oracle version si 10.2.0.1.0 ..
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 325
ORA-06512: at "SYS.OWA_UTIL", line 377
ORA-06512: at "SYS.IMAGE_GET", line 16
ORA-06512: at line 7
WHen i chked the PKG this line is throwing error
for i in 1..owa.num_cgi_vars
loop
if (owa.cgi_var_name(i) = upper_param_name)
then return(owa.cgi_var_val(i));
end if;
end loop;

Whether I need to configure something else too...pls advice

Thnx a ton
Sangeetha
Tom Kyte

Followup  

January 30, 2009 - 1:58 pm UTC

you sort of need to run this from a web browser. htp stuff - which is used - is part of mod_plsql.

Toad wouldn't have a clue what to do with this stuff.

How to run the package

March 17, 2009 - 12:18 pm UTC

Reviewer: Igor from MD USA

Tom,
I tried your code. It worked. I was able to store a pdf file in Oracle table which I created.
Then I modified your package to retrieve the pdf and ran it from SQL*PLUS to test it. It successfully completed.
However, I don't understand how to run the package to show the actual pdf file in a browser from Oracle forms.
Unfortunately, I have only few days to figure out how to do that.
Thanks a lot.
Igor


Tom Kyte

Followup  

March 17, 2009 - 12:24 pm UTC

You don't run a browser from forms.

If you want to use forms - you should use forms to store and retrieve the document. You might want to use the forums on otn.oracle.com, there you will find many people that use forms.

Where does the pdf file go?

March 18, 2009 - 1:40 pm UTC

Reviewer: Igor Sukhenko from MD USA

Tom, thanks a lot for your response.
Actually, our application is a web based application and it does open pdf files in a browser from Oracle forms using web.show_document Oracle built-in. However, it opens only previously stored pdf files from a server, but we need to find a way to open a pdf file directly from a database.
I¿ll try otn.oracle.com, but thought maybe you can explain to me what happens with a pdf file when it is retrieved from a database with your procedure. Where does it go? Is it physically stored to some place on a server?

Thank you very much,

Igor.

Tom Kyte

Followup  

March 19, 2009 - 10:13 am UTC

do you have mod_plsql configured and installed and working

if not, this entire thread does not apply, you use mod_plsql.

Where does the pdf file go?

March 23, 2009 - 2:55 pm UTC

Reviewer: Igor Sukhenko from Rockville, MD USA

Tom,

I am not familiar with this name "mod_plsql", so I am not sure if I have mod_plsql configured and installed and working on my machine. Is there any way to check this?
Thank you,
Igor
Tom Kyte

Followup  

March 26, 2009 - 1:31 pm UTC

you would know, if you are the administer, mod_plsql is an apache module that converts urls into stored procedure calls (it is what I use here on asktom).



March 24, 2009 - 3:31 pm UTC

Reviewer: pvb from Pittsburhg, PA

Hi Tom,

I have pdf file stored in oracle table column (blob). I need to print that pdf file with some other information. is it possible to print pdf using oracle report 6i (RDF)?

Thanks
Tom Kyte

Followup  

March 29, 2009 - 10:58 am UTC

please try out the forums on otn.oracle.com -> developer. I haven't touched forms/reports since 1995.


But, I doubt it, RDF is not a "printer tool", it is a report generator (that can then submit the reports it generates to a printer). Seems you would just want something that can print a pdf file.

how about a simplification?

July 22, 2009 - 2:34 am UTC

Reviewer: Jelena from Germany

Hi Tom, 

in which oracle version can we expect something like this to solve this problem?

SQL> select to_file(blob_column, 'c:/my_file.xyz') from my_blob_containing_table;


I dont understand how come it is so complicated for oracle to produce such simple function? or am i missing something?
I guess you could make that feature in less time then you need to answer all kind of "how do i save a pdf" questions here...

Tom Kyte

Followup  

July 26, 2009 - 6:33 am UTC

well, the addition of any feature/function to a large set of software is a potentially destabilizing event. There is no such thing as a "tiny thing".

http://www.contrast.ie/blog/there-are-no-small-changes/
(I liked that article)


But it really depends on your programming environment how to answer this question and if you ctl-f for

Another (standard) method December 13, 2002

You'll find a way easy way to do this with mod_plsql that has been out there for many many many years....

Simplification Part II

July 27, 2009 - 5:56 am UTC

Reviewer: Jelena from Germany

Hi Tom,

yes i agree that any new feature is potential risk, but the number of new features in Oracle10g, or Oracle 11 is huge... And i'm not sure that so many people need it, as if you would go for simplification/better usability of existing stuff - example: SqlPlus is a tool 1990 style, and a lot of people still use it (me inclusive), but up-arrow still doesnt work - dont you miss it? I definetelly do.
The same with PDF. I see much more risk in changing how Oracle Optimizer work then in adding download function to standard sql functions?
For me your solution doesnt work, as on Production DB i dont have create Procedure permission. I found the way, but i dont still think Oracle can let itself be so 'out of date'.
Tom Kyte

Followup  

July 27, 2009 - 6:07 am UTC

Jelena -

I think you missed entirely my ultimate point....

This was added over seven years ago, did you see the link? It has been there for a long long long long long time.

or I am entirely missing what you meant - and therefore you would need to be more clear what you meant.



(I use rlwrap on unix, I have an up arrow, if you want fancy things - sqldeveloper is out there - sqlplus is sort of just a script running tool and nothing more).

July 27, 2009 - 9:15 am UTC

Reviewer: Jelena from Germany

hi Tom, i think i did understand your point. But "It has been there for a long long long long long time" and the fact that 7 years later it's still that difficult - i rather find as a disadvantage.

My point is:
People have trouble accessing BLOBs since 2002 - and oracle doesnt improve existing stuff.
You have to use linux tools because Oracle can not implement up-arrow in one of its most popular tools. Copy-Paste in SqlPlus is as confortable as it was with DOS OS in 1990's.

I used linux before and arrow-up worked, now i (have to)use windows. In SQL Developer on Vista there is a bug that you can not download BLOB, that's why i start searching here thinking i'll get it fast with sqlplus - but i still have same problem as 10 years ago.

So, my point is that it's hard to believe on which level usability of Oracle tools in 2009 still is.

Tom Kyte

Followup  

July 27, 2009 - 7:40 pm UTC

define what you mean by "people have trouble accessing blobs since 2002???!?!?!?!

I showed you how, in the year 2002 - a long time ago - we basically did what you asked for as far as I can tell. Please explain.


sqlplus is an OPTION, a choice, it is what it is. You want a fancy environment with new stuff - you use the tools that are "fancy environments with new stuff". sqlplus is sort of old school, it obviously is not the focus, SQLDeveloper is - it has what you want. Sorry, but sqlplus is just what it is and probably isn't going to advance in any shape or form in the future.


In windows, you already and always had the up arrow. sqlplus.exe runs in cmd.exe and the up arrow always worked.

An Alternative Solution

August 12, 2009 - 8:44 am UTC

Reviewer: Darragh Duffy from Ireland.

Hi All, I thought I would post an alternative solution that I have workinging, this is using a newer package wpg_docload which now comes part of the Oracle DB. I am not sure which version is became available, I have this working in Oracle DB 9.X +

I assume the blob is already in your DB.

select document_contents into v_pdf_blob
from cp_portal_pdf
where document_id = p_id;

v_length := dbms_lob.getlength(v_pdf_blob);
htp.init; -- extremely important for the browser
owa_util.mime_header('APPLICATION/PDF', false);
htp.p('Content-length: '||v_length);
htp.p('Content-Disposition: filename="'||'doc_name.pdf'||'"');
owa_util.http_header_close;
wpg_docload.download_file(v_pdf_blob);

works quite nicely. if you have any questions feel free to send email to darragh.duffy@cork.core-com.ie

I will do my best to answer.

By the Way this does not generate PDF, instead we us Oracle BI Publisher to create the PDF's and they are stored in the DB for security and are displayed using above. (NB I have not included my security layer here for obvious reasons) this is not a production release simply an alternative solution - apologies if it has already been mentioned.

Darragh.


BLOB

March 01, 2010 - 1:21 pm UTC

Reviewer: A reader

Tom:

I need your help with this issue.

Do you know if there is a bug in 9iR2 in CONVETTOCLOB subroutine.

I am uploading an XML file in MOD_PLSQL with some diacritics like

ALT+0224
ALT+0232
ALT+0225

one tag has "new word àèáñ"

The binary file seems to be OK I can see those). but when i run the code below to convert BLOB to CLOB and store it in another table, I get "new word 1haq".

both xml file and database are set at ISO-8859-1

Do you see anything wrong with the code or API.


DECLARE
l_clob CLOB;
l_blob BLOB;
l_warning NUMBER;


l_id NUMBER;
l_src_offset NUMBER := 1;
l_dest_offset NUMBER := 1;
l_cs_id NUMBER := dbms_lob.default_csid;
l_doc_size NUMBER;


BEGIN


UPDATE mylog_table
SET message_recv = empty_clob(),
name = p_file_name
WHERE message_no = v_message_no
returning message_recv INTO l_clob;

SELECT blob_content,doc_size INTO l_blob,l_doc_size
FROM documents_table
WHERE name = p_file_name;

l_src_offset := 1;
l_dest_offset := 1;
IF (dbms_lob.getLength(l_blob) > 0) THEN
dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob), l_src_offset,
l_dest_offset, 1, l_cs_id, l_warning );
END IF;
Tom Kyte

Followup  

March 02, 2010 - 6:41 am UTC

there is, but it isn't documented/supported in that release.


You would need to know the character set of the incoming data. The default character set isn't it apparently. What characterset is the blob data in exactly.

BLOB

March 01, 2010 - 11:07 pm UTC

Reviewer: A reader

Tom:

I noticed 2 things:

1. the 9iR2 does not have CONVERTOCLOB in it.

I do not understand how it even works

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lob2.htm#998404

2. Is not the order of parameters wrong. CSID should be the 6th parameter IN while in your statement it is the 7th.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i1020356
Tom Kyte

Followup  

March 02, 2010 - 7:20 am UTC

it does not exist officially until 10gr2, and correct on the parameters.

blob

March 02, 2010 - 8:43 am UTC

Reviewer: A reader

Tom:

Thanks for the reply.

The client is a C++ application and posting an XML file in ISO-88591 to oracle via HTTP. I think most of these clients work in UNICODE but it should not matter if the XML file is ISO-88591-1.

IT seems it works when i do this. is this the correct call format for CONVERTOLOB and values for CSID.

dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob),
l_dest_offset, l_src_offset,
l_cs_id, l_context, l_warning );

Tom Kyte

Followup  

March 02, 2010 - 12:39 pm UTC

10g has documentation for that API, check it out.

BLOB

March 05, 2010 - 3:06 pm UTC

Reviewer: sam

Tom:

The second statement works fine.
dbms_lob.convertToClob( l_clob, l_blob, dbms_lob.getLength(l_blob),
l_dest_offset, l_src_offset,
l_cs_id, l_context, l_warning );

I am afraid the incorrect statement might open a can of worms for me. I should have verified the parameter orders using the docs before.

I processed over 50,000 XML files (BLOB to CLOB) using that in production.

Here is my question:

I still have the original BINARY file in the DOCUMENTS_TABLE.

How can i verify that the CLOB converted was correct (All letters match).

Shall i convert the CLOB back to BLOB and do a MD5 hash check with the oringinal BLOB and if they match it is ok and if they do not then I need to convert again and update the CLOB.

Can you advice or if you already had code for this can you let me know the article.

THanks,


fix clob

March 20, 2010 - 1:35 pm UTC

Reviewer: sam

Tom:

I need a hint from you reagarding possible bad converted CLOBs due to poassing incorrect parameters.

Can i do this.

create another table with NEW_CLOB column.

Then convert the source BLOB into CLOB and store in the above table. this time using correct parameters passed.

Then I can jon the tables or do

select count(*) from table1, table 2 where table1.pk=t2.fk
and table2.new_clob<>table1.clob

All the ones that do not match would need to be fixed.

Let me know if you have a better way,

open file

April 20, 2010 - 3:56 am UTC

Reviewer: ahmad from columbus,oh usa

hi tom
how can i open blob file saved in database ,
i want to open it using oracle 6i

blob

April 21, 2010 - 1:15 am UTC

Reviewer: john

hi,
how to open blob(.txt,.doc,.xls,.pdf, image....etc)file from database without using path....???
ex:
i make table has blob(column) and i insert image or text file or any thing
how can i open the file in oracle forms 6i
i think the idea like send attach mail (get object)...
thanks a lot...
Tom Kyte

Followup  

April 21, 2010 - 8:50 am UTC

please use the forums on otn.oracle.com to ask questions about forms, I haven't touched forms in 15 years now...



wpg_docload

April 29, 2010 - 1:16 pm UTC

Reviewer: A reader

Tom:

very quick question.

<<3) haven't tried it, but you might look at wpg_docload -- it works with BLOBS, not CLOBS but might
be useful (it streams) >>

1) If i want to download a file stored in NCLOB using mod_plsql do i have to convert NCLOB to BLOB first.

2) does it make more sense to store the variable(file) in a BLOB or not even store it at all and just print the variable to a web page using a DAD set at UTF8.

my requirement is to render the web page on demand so i can live without storing it.

please advice.




Tom Kyte

Followup  

April 29, 2010 - 2:05 pm UTC

1) describe the package, it takes a blob. not a clob, not a nclob - a blob.

binary.

You'll miss out on the requisite character set translation that needs to take place.

2) Sam, your requirements change and fluctuate with the wind. Every time - it changes just a little bit.

And your sentence in #2 there doesn't even begin to make sense.

a) store the variable (file) in a blob (whatever that means, I don't know what a "variable" that is also "a file" is.

b) not even store it at all and just print the variable to a web page using a DAD

if it isn't stored, what are you printing?!?!?!?

WPG_DOCLOAD

April 29, 2010 - 2:30 pm UTC

Reviewer: A reader

Tom:

sorry, i was not clear. Let me try to make it clear what i am doing.

I have this NOTE table in ISO88591 9.2 DB.
USER wants to query the table and get a web page (xml format) in unicode format.

NOTE
-------
SEQ NUMBER(10)
TO VARCHAR2(100)
FROM VARCHAR2(100)
HEADING VARCHAR2(100)
BODY VARCHAR2(1000)
UNICODE_XML NCLOB




<?xml version="1.0" encoding="UTF-8" ?>
- <note>
<seq>1</seq>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>

I have a stored procedure that queries the table and then build this variable
MY_VAR VARCHAR2(32000) which basically stores the XML file above.


Then

1) I write this MY_VAR to UNICODE_XML column so oracle converts and stores in unicode format.

2) I display to the user a URL that uses a DAD (client) with UNICODE setting. WHen he clicks on it
selects the UNICODE_XML column and displays it on unicode encoded web page.

The problem here is that WPG_DOCLOAD only downloads a BLOB input. It wont take NCLOB.

This means I need to CONVERT NCLOB to BLOB or not store the web page in the table and stream it directly to mod_plsql.

basically just creating a web page that does this using the UNICODE DAD

create MY_VAR
htp.p(MY_VAR)

What do you think is the best thing to do?

I hope i was clear this time.
Tom Kyte

Followup  

April 29, 2010 - 2:54 pm UTC

we've had this discussion - you and I have - ad nauseum. I do not wish to resume it.

wpg

April 29, 2010 - 4:18 pm UTC

Reviewer: A reader

yes, you were right with the client setting.

i setup a DAD just for unicode stuff and i get unicode webpage. other stuff works with the win1252 stuff as usual.

I originally thought the mod_plsql had one settng for character set but it turns out you can set it for each DAD separately.

pdf issue

May 18, 2010 - 11:16 pm UTC

Reviewer: jay from India

Tom,
My issue is that i have a pdf attachment as a blob in my oracle db. But when i download it in my desktop and open it, it is showing that "not the correct format or the data may be corrupted while sending as a mail attachment". Also i noted that the size of the file is 0kb. Can u enlighten me with this is issue as it is very important in my application.

Tom Kyte

Followup  

May 24, 2010 - 10:36 am UTC

not unless you give us step by step ways to reproduce (you know, tell us HOW you are getting it down)

and also how you verified that the blob in the database is identical to the original pdf - you could have corrupted it on the way into the database or on the way to the database.

i am using your package

May 20, 2010 - 12:53 pm UTC

Reviewer: rahul pardeshi

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


But i am getting the error what might be the problem

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.OWA_UTIL", line 415
ORA-06512: at "APPS.IMAGE_GET", line 16

--------------------------------------------------------------
FRM-40735: SPECIAL26 trigger raised unhandled exception ORA-06502.
Tom Kyte

Followup  

May 24, 2010 - 12:12 pm UTC

you are in forms, this is for mod_plsql only.

It is likely the call to owa_util, you haven't initialized the OWA (oracle web agent, what mod_plsql was called before being called mod_plsql) and it is throwing an exception.

This code would be entirely inappropriate in Oracle forms - what are you trying to do??

reading the PDf stored ion the database

May 20, 2010 - 1:21 pm UTC

Reviewer: rahul pardeshi

regarding your follow up for the similar kind of error

Followup January 30, 2009 - 1pm Central time zone:

you sort of need to run this from a web browser. htp stuff - which is used - is part of mod_plsql.

Toad wouldn't have a clue what to do with this stuff.


but i am click on the menu botton in oracle forms
and once i click on the menu it is calling a trigger SPECIAL27 and i am calling the package to read the pDF document stored

so i am not accessing it through TOAD

-----------------------------------------

any updates please let me kno as i got stuck i have to read this file from a form menu
Tom Kyte

Followup  

May 24, 2010 - 12:12 pm UTC

please use the forums on otn.oracle.com, there is a forum for forms - I don't do forms at all.

Displaying

May 26, 2010 - 3:05 pm UTC

Reviewer: rahul pardeshi

How to launch a window showing a pdf file from pl/SQL, instead of forms,

in forms i am just calling the procedure to sall the pdf document in the window

CREATE OR REPLACE PROCEDURE download_my_file(p_file in number) AS
v_mime VARCHAR2(48);
v_length NUMBER;
v_file_name VARCHAR2(2000);
Lob_loc BLOB;

BEGIN
SELECT 'application/pdf', doc, id name ,DBMS_LOB.GETLENGTH(doc)
INTO v_mime,lob_loc,v_file_name,v_length
FROM my_files
WHERE id = p_file;
--
-- set up HTTP header
--
-- use an NVL around the mime type and
-- if it is a null set it to application/octect
-- application/octect may launch a download window from windows
owa_util.mime_header('application/octet', FALSE );
-- set the size so the browser knows how much to download
htp.p('Content-length: ' || v_length);
-- the filename will be used by the browser if the users does a save as
htp.p('Content-Disposition: attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
-- close the headers
owa_util.http_header_close;
-- download the BLOB
wpg_docload.download_file( Lob_loc );
end download_my_file;
/

Tom Kyte

Followup  

May 27, 2010 - 7:11 am UTC

you would have to fire up a browser and send it a URL to it that retrieves a pdf file.


You cannot just "call the stored procedure", the application server/web server has to call it - get the output from it and send it back using the http protocol.

convert function

May 29, 2010 - 2:30 pm UTC

Reviewer: A reader

dear tom:

quick question.

Can you use oracle convert function to convert a string stored in CLOB column from ISO8859 to UTF-8 and then convert that UT-8 encoded string into BLOB and store it in a BLOB column.

If i understand correctly, oracle does not change the original encoding of data when you store it in BLOB field so when I download the data via mod_plsql it will stay UTf-8.

I think it is doable but i thought i would confirm that with the oracle guru first.
Tom Kyte

Followup  

May 30, 2010 - 6:56 am UTC

convert doesn't work on clobs, there are api's in dbms_lob to do that.

download

May 30, 2010 - 5:36 pm UTC

Reviewer: A reader

thanks Tom:

I think you are refering to CONVERTTOBLOB procedure which unfortunately is not avaiable with the 9i version iam using for now.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i1020355

but the string is small so i can select the first 32000 charcters of the CLOB into pl/sql variable type VARCHAR2(32000) and then do the character set conversion to UTF-8 and then store into BLOB.

Would that work as i think? oracle should keep the original encoding of UTF-8 data.

How to send mail with BLOB without attach

August 11, 2010 - 2:33 am UTC

Reviewer: Lorena from Spain

Hi Tom,

I want to send email with a picture, but I want to send this in the body of the message not like as attachment. I work with utl_stmp. Can you help me?

Thank you


Tom Kyte

Followup  

August 18, 2010 - 11:50 pm UTC

it would still be sent as an attachment - but you would be sending some HTML (plain text doesn't have the ability to do anything fancy) that referenced it.

And no, I don't know the exact specification of what that would all look like. You'd have to research that on a non database site....

documents table PK

September 29, 2010 - 6:42 pm UTC

Reviewer: A reader

Tom:

Could this be due to an internal oracle database bug or mod_plsql bug?

I have this document table and mod_plsql is configured to upload to it.

create table DOCUMENTS_TABLE (
NAME VARCHAR2(128) not null,
MIME_TYPE VARCHAR2(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR2(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR2(128),
CONTENT LONG RAW,
BLOB_CONTENT BLOB,
constraint PK_DOCUMENTS_TABLE primary key (NAME)
)


We have a client application that keeps uploading xml files to the database. They are always same name "file.xml". But oracle genrates its own key and appends that to the filename to generate a unique PK and insert into name field.

Recently, users have been getting a PK violation on this table. Could oracle be generating duplicate keys? I am using oracle 10 app server with oracle 9iR2.0.4 database.

Do you also advise on archiving this table every month or so?


HTTP/1.1 404 Not Found
Date: Wed, 28 Sep 2010 15:26:11 GMT
Server: Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server
Keep-Alive: timeout=15, max=100
Connection: Keep-Alive
Transfer-Encoding: chunked
Content-Type: text/plain

c2f
Wed, 28 Sep 2010 15:26:12 GMT

ORA-00001: unique constraint (TCC.PK_DOCUMENTS_TABLE) violated


Tom Kyte

Followup  

September 30, 2010 - 7:00 am UTC

well, we should not be generating the same number - I've asked someone to peek at that to confirm.

I cannot answer the archiving question - only you can. Do you desire to archive every month or so? It is your data - only you know if you

a) can
b) want to

Documents table

September 30, 2010 - 8:30 am UTC

Reviewer: A reader

Thanks, Tom. Please let me know as soon as you find out if there is a bug/patch for this.

My application copies the file from documents table so I can archive it for now. But it would be serious bug if oracle can't gurantee a UNIQUE Primary Key for each file uploaded.

The oracle db version for this is at 9.2.0.2.
The Oracle Application Server 10g Release 3 10.1.3.0.0.
Tom Kyte

Followup  

September 30, 2010 - 1:45 pm UTC

turns out they are using a random number there so there is a small opportunity for it to have a collision - but it would be like winning the lottery to have that happen.

So - could it happen? Yes
Will it happen? odds are really against it


Upon uploading - we do call your routine - it is a common practice for you to name the document according to your system conventions. So the most practical solution would be for your upload routine to generate a truly unique name that means something to you. The name we assign was to be "temporary" in nature.

seq no

October 02, 2010 - 9:34 pm UTC

Reviewer: A reader

Tom:

I wish i hit the lottery instead of this duplicate sequence number. i would not bother you after that!

I only had 40,000 files in the table and started seeing dups. That is not really a high number.

Did oracle change this on oracle 11g for guranteed unique number generation?

Are you saying that the client should change the name of the file posted every time it submits(i.e append a sequence number) insteaf of using a fixed name.
Tom Kyte

Followup  

October 04, 2010 - 2:03 am UTC

SAM -

Now that you know how it is assigned (random number - which can be duplicated) and since I've already said "it is a common practice for you to name the document according to your system conventions. So the most practical solution would be for your upload routine to generate a truly unique name that means something to you. The name we assign was to be "temporary" in nature. "


I'll leave it up to your power of deduction to see if I was saying "the client should change the name of the file posted" or not.


documents table

October 04, 2010 - 4:53 pm UTC

Reviewer: A reader

create table DOCUMENTS_TABLE (
NAME VARCHAR2(128) not null,
MIME_TYPE VARCHAR2(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR2(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR2(128),
CONTENT LONG RAW,
BLOB_CONTENT BLOB,
constraint PK_DOCUMENTS_TABLE primary key (NAME)
)


<<<Upon uploading - we do call your routine - it is a common practice for you to name the document according to your system conventions. So the most practical solution would be for your upload routine to generate a truly unique name that means something to you. The name we assign was to be "temporary" in nature. >>>>



Is this the oracle server routine for the HTML form handler. The user/http client keys in the filename, then my routine does notdo anything with DOCUMENTS_TABLE other than selecting the *NAME* which is automatically inserted by *ORACLE* into the DOCUMENT_TABLE>

Now, when i look at DOCUMENT_TABLE I have name entries like this:

F9123/myfile.xml
F1342/myfile.xml
F31346/myfile.xml

Oracle takes the file name posted by client(before it gets to my routine) and inserts the NAME into the documents table.


Can you explain how i can control that name being stored in the table?
Tom Kyte

Followup  

October 05, 2010 - 12:03 pm UTC

Sam,

please just read what I've written. Do you know how to issue an UPDATE? If you do, you'll have your answer.


documents table

October 05, 2010 - 8:00 pm UTC

Reviewer: A reader

Tom:

Ah, i see. You want me to UPDATE the name that oracle inserts into that field. basically update a primary key -something you dont recommend usually)

UPDATE documents_table
SET name = new_sequence_no
WHERE name = oracle_uploaded_name;

right?
Tom Kyte

Followup  

October 06, 2010 - 4:49 am UTC

SAM -

(we do this a lot don't we, say the same thing over and over...)



it is your key. they expected you to take it over, I wrote that above.


... Do you know how to issue an UPDATE? ....



... So the most practical solution would be for your upload routine to generate a truly unique name that means something to you. The name we assign was to be "temporary" in nature ...

...it is a common practice for you to name the document according to your system conventions. So the most practical solution would be for your upload routine to generate a truly unique name that means something to you. The name we assign was to be "temporary" in nature. ....


documents table

October 06, 2010 - 4:15 pm UTC

Reviewer: A reader

Tom:

I just need a unique key for the record - name does not really mean anything for me. A sequence number generator will always be unique and i will never run into this situation again. just to confim is this what i only need to do in my routine

UPDATE documents_table
SET name = document_table_seq_no.netval
WHERE name = oracle_key_name;

Tom Kyte

Followup  

October 07, 2010 - 2:10 am UTC

SAM -

you are a programmer
you are an oracle programmer
you have been doing oracle programming for a couple of years (you've been asking me lots of questions for that long about oracle programming in any case)


You should well be able to answer your question of "is this what I need to do".

If it is xml, you probably want it to look like an xml file name - but use whatever convention makes you happy.

How to retrive/display Multi-page tiff using form 6i

November 10, 2010 - 5:08 am UTC

Reviewer: Wazir from KSA

Hi Tom, i have successfully store image into DB with LongRow Datatype. but i am unable to display multi-page tiff on form as i think form 6i not support multi-page tiff..

we can do it by using external image viewer like irfan or windows viewer etc.. but i dnt know how we can retrieve image from 10g using form 6i.. can you please give some guide line...

regards

i got an error running your code

September 01, 2011 - 12:18 pm UTC

Reviewer: Laura from Bolivia

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
ORA-06512: at "SYS.OWA_UTIL", line 413
ORA-06512: at "ASOTO.PDF_GET", line 14
ORA-06512: at line 7
Tom Kyte

Followup  

September 01, 2011 - 2:23 pm UTC

probably you are doing this without using MOD_PLSQL so the web environment isn't set up.

ops$tkyte%ORA11GR2> exec owa_util.mime_header( 'image/gif' );
BEGIN owa_util.mime_header( 'image/gif' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.OWA_UTIL", line 415
ORA-06512: at line 1


ops$tkyte%ORA11GR2> @owainit
ops$tkyte%ORA11GR2> declare
  2          nm      owa.vc_arr;
  3          vl      owa.vc_arr;
  4  begin
  5          nm(1) := 'WEB_AUTHENT_PREFIX';
  6          vl(1) := 'WEB$';
  7          owa.init_cgi_env( nm.count, nm, vl );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec owa_util.mime_header( 'image/gif' );

PL/SQL procedure successfully completed.




If you "pretend" to be mod plsql and set up a little cgi environment - it'll work for you in sqlplus.

i got an error running your code

September 01, 2011 - 12:21 pm UTC

Reviewer: Laura from Bolivia

Hi Tom, im running your code from toad and i got this error

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
ORA-06512: at "SYS.OWA_UTIL", line 413
ORA-06512: at "ASOTO.PDF_GET", line 14
ORA-06512: at line 7

do you know what can it be? i will appreciate any solution thanks in adavance

Laura
Tom Kyte

Followup  

September 01, 2011 - 2:23 pm UTC

see above

Displaying PDF files stored in the Database

March 23, 2012 - 8:41 am UTC

Reviewer: Dany from Lebanon

Dear Tom,

Thank you for all your helpful replies,
Now since 11g has secure files in it i guess its really fast to insert pdfs as blobs to the database.My question is :does any of the coding change?and how can we view the pdf thanks in advance

Nothing Happens

April 28, 2016 - 4:38 pm UTC

Reviewer: A reader

Hi,

I compiled and got the following
Package IMAGE_GET compiled
Package body IMAGE_GET compiled

But nothing pops up or I don't see my gif.
Apologies, I am probably missing something glaringly obvious.
Any ideas?

Chris Saxon

Followup  

May 01, 2016 - 10:46 am UTC

OK, we need to take a step back.

The database is running on a remote server presumably. So to get a file from the database to *your* machine, you need a transport mechanism, and http is a natural choice.

That means the database must be able to serve up http to you.

Take a read here

https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_we.htm

which should help you understand the concept of where the 'image_get' procedure sits in the whole picture.

September 27, 2016 - 9:16 am UTC

Reviewer: Santosh from 1


PDF_REPORT

November 19, 2016 - 12:42 pm UTC

Reviewer: Pierre

Hey Tom,
It works fine.
Thank you

Need help to run the code, sorry for the naivity

June 14, 2017 - 3:53 pm UTC

Reviewer: Debdatta Mandal from India

Hi Tom,

I have executed the sample code successfully to the point where the package body got compiled successfully.

But, getting error while trying to run the code.
Please help.

Here is what I am executing
============================================
create table debo 
( id int primary key, 
theBlob blob 
) 
/ 
<i>table created</i>

create or replace directory debo_files as 'E:\COMPANY\Deb\RESUME'; 

<i>Directory created</i>

declare 
l_blob blob; 
l_bfile bfile; 
begin 
insert into DEBO values ( 1, empty_blob() ) 
returning theBlob into l_blob; 

l_bfile := bfilename( 'DEBO_FILES', 'SYSAUD01_467600.PDF' ); 
dbms_lob.fileopen( l_bfile ); 

dbms_lob.loadfromfile( l_blob, l_bfile, 
dbms_lob.getlength( l_bfile ) ); 

dbms_lob.fileclose( l_bfile ); 
end; 
/ 
<i>anonymous block completed
</i>

create or replace package image_get1 
as 
procedure pdf( p_id in debo.id%type ); 
end; 
/ 

<i>PACKAGE IMAGE_GET1 compiled</i>

create or replace package body image_get1
as 

procedure pdf( p_id in debo.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 debo 
where id = p_id; 

-- make sure to change this for your type! 
owa_util.mime_header( 'pdf' ); 

begin 
loop 
dbms_lob.read( l_lob, l_amt, l_off, l_raw ); 

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; 
/ 
<i>PACKAGE BODY IMAGE_GET1 compiled</i>
BEGIN
IMAGE_GET1.PDF(1);
END;
/

<i>BEGIN
IMAGE_GET1.PDF(1);
END;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.OWA_UTIL", line 415
ORA-06512: at "SYSADM.IMAGE_GET1", line 16
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:</i>

Connor McDonald

Followup  

June 15, 2017 - 12:41 am UTC

If you are wanting to download/display a file, just do:

wpg_docload.download_file(l_lob);

much easier

More to Explore

DBMS_LOB

More on PL/SQL routine DBMS_LOB here