Home » SQL & PL/SQL » SQL & PL/SQL » output blob to HTML page
output blob to HTML page [message #263364] Wed, 29 August 2007 20:38 Go to next message
rkaula
Messages: 22
Registered: December 2005
Junior Member
How can an image (jpg) file saved as a blob be output/displayed in a Web page using PL/SQL procedure. The hpt.prn does not work.

Thanks.
Re: output blob to HTML page [message #263418 is a reply to message #263364] Thu, 30 August 2007 00:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What you should use, PHP/JAVA/ASP.

Search on that.
Re: output blob to HTML page [message #263696 is a reply to message #263418] Thu, 30 August 2007 15:32 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
-- load image from file using dbms_lob
http://www.orafaq.com/forum/t/54790/0

-- or get it from a table
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:232814159006

It's very important to have your characterset correctly set in the connection (DAD) in general else you'll see garbage...
Re: output blob to HTML page [message #263710 is a reply to message #263418] Thu, 30 August 2007 18:10 Go to previous messageGo to next message
rkaula
Messages: 22
Registered: December 2005
Junior Member
Andrew,

I tried the first approach (load image from file using dbms_lob) and it works great. The second approach from asktom did not work. Is it possible to modify your approach so that it can accept input parameter ie. id of the blob in the table. I tried to change it, but it failed.

By the way how do you ensure the characterset is correctly set in DAD?

Thanks a bunch.
Re: output blob to HTML page [message #263711 is a reply to message #263364] Thu, 30 August 2007 18:13 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Here's an example that I use; it reads the data from a table.
CREATE OR REPLACE PROCEDURE view_img
 ( p_name IN VARCHAR2 )
IS
  img_blob     BLOB;
  img_type     VARCHAR2(30);
  len          INTEGER;
  amount       BINARY_INTEGER := 32000;
  offset       INTEGER := 1;
  buffer       RAW(32000);
BEGIN
  SELECT image, type 
  INTO img_blob, img_type
  FROM images 
  WHERE name = p_name;
  
  owa_util.mime_header(img_type);

  len := dbms_lob.getlength(img_blob);
  WHILE offset < len LOOP
    dbms_lob.read
     ( lob_loc => img_blob
     , amount  => amount
     , offset  => offset
     , buffer  => buffer );
 
    htp.p(utl_raw.cast_to_varchar2(buffer));
    offset := offset + amount;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END view_img;


In case you are curious, the table looks like this:
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(30)
 IMAGE                                              BLOB


TYPE indicates the type of image (image/gif, image/jpeg, etc)

And just to be complete, use the following html
<img src="view_img?p_name=test">


I'm pretty sure I originally found the example at AskTom
Re: output blob to HTML page [message #264089 is a reply to message #263711] Fri, 31 August 2007 13:40 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
The characterset is a property of the DAD setup screens (on 9iAS, it's on one of the advanced properties page)

It doesn't have to match the database characterset exactly, but that's a good starting point. e.g. Oracle 8i's "utf8" is probably compatible with 9i and later's "al32utf8"

I forget the exact format needed - but this will give you an idea of the worst case (how NLS_LANG is made up)...
select * from v$nls_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

NLS_LANGUAGE         AMERICAN
NLS_TERRITORY        AMERICA
NLS_CHARACTERSET     AL32UTF8

Corresponding NLS_LANG is determined as follows:
NLS_LANG=<NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>
i.e.
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
Re: output blob to HTML page [message #264107 is a reply to message #264089] Fri, 31 August 2007 16:23 Go to previous messageGo to next message
rkaula
Messages: 22
Registered: December 2005
Junior Member
Thanks Andrew. Your solution works great.

By the way I also found there is a wpg_docload.download_file statement in mod_plsql documentation. The code is short, but does not work. Curious if you have tried it? Is it for APEX? Code is as follows:

create or replace procedure download_blob(name in varchar2) is
myblob blob;
begin
select blob_data into myblob from mytable where blob_name = name;
owa_util.mime_header('text/html', FALSE);
htp.p('Content-Length: ' || dbms_lob.getlength(myblob));
owa_util.http_header_close;
wpg_docload.download_file(myblob);
end;

Thanks.

[Updated on: Fri, 31 August 2007 16:25]

Report message to a moderator

Re: output blob to HTML page [message #264115 is a reply to message #264107] Fri, 31 August 2007 22:01 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The procedure you created is meant to be called by directly from a web browser as indicated in Invoking mod_plsql.

Can you elaborate on "does not work"? Are you getting specific errors?
Previous Topic: Problem with updating
Next Topic: displaying rows into columns
Goto Forum:
  


Current Time: Sat Dec 10 06:57:12 CST 2016

Total time taken to generate the page: 0.07351 seconds