Re: Problem with BLOBS, UTL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 16 Sep 1998 14:13:51 GMT
Message-ID: <3603c756.81326741@192.86.155.100>

A copy of this was sent to Richard_Hirsch_at_hotmail.com (if that email address didn't require changing) On Wed, 16 Sep 1998 11:00:07 GMT, you wrote:

>I have a problem with BLOBs that this is driving me crazy.
>
>Our configuration:
> Oracle 8.04
> OWS 2.1
>
>So... here is the question. We store gifs / jpgs in a blob column in a
>table. I stole code from Tom Kyte (again) to load the files from a bfile as
>well as display them using utl_raw. The problem is that the files are somehow
>distorted by the utl_raw call. The code from Tom Kyte follows: .....
>

2 things.

if they are on NT (if the WEBSERVER is on NT --don't care WHERE the database is), i've discovered that the Oracle 8 client install writes a registry entry NLS_LANG all over the place (i found three of them in my registry). It defaulted in my case to AMERICAN_AMERICA.WE8ISO8859P1 which is inconsistent with my database which was AMERICAN_AMERICA.US7ASCII. Even tho I specified in the DCD/DAD configuration to use AMERICAN_AMERICA.US7ASCII, NLS kicked in and changed my mind for me (the registry setting overrides all else).

This killed file download on NT -- the downloaded file would have the high bits stripped all of the time in my case. The solution in this particular case was to reset the NLS_LANG in the registry to be the same as the database. The correct registry setting for them would be the output of the query:

SQL> l
1 select a.value || '_' || b.value || '.' || c.value nls_lang

  2  from nls_database_parameters a,
  3       nls_database_parameters b,
  4       nls_database_parameters c
  5  where a.parameter = 'NLS_LANGUAGE'

6 and b.parameter = 'NLS_TERRITORY' 7* and c.parameter = 'NLS_CHARACTERSET' SQL> / NLS_LANG

AMERICAN_AMERICA.US7ASCII this could also explain the download file size differences as there might be double byte issues with the language mismatches.

additionally, if they are not on NT, they need to make sure the above string gotten via that query is the nls_lang setting in the owa.cfg file for the DCD in question. If not -- no go...

>create or replace procedure get_blob( id in varchar2 )
>is
> l_lob blob;
> l_amt number default 4096;
> l_off number default 1;
> l_raw raw(4096);
>begin
> begin
> select b_lob into get_blob.l_lob
> from blobs
> where id = get_blob.id;
> exception
> when no_data_found then
> owa_util.status_line( 404, 'Not Found' );
> end;
>
> begin
> owa_util.mime_header( 'image/gif' );
> 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;
>
>I think the problem is in the conversion using utl_raw.cast_to_varchar2 call.
>Checking the l_amt parameter shows that the dbms_lob.read call returns the
>correct amount but the browser receives the wrong amount of data (566 instead
>of 559 bytes for example). Sometimes the first half of picture appears and
>then garbage. I assume that the NLS conversion that Tom mentions in his
>newsgroup post is changing my data.
>
>-----
>
>>Bear in mind that the character set for the PL/SQL cartridge must be >the same
>
>as the character set of the database for this to work (we >are dealing with
>RAWs here, can't let any NLS conversions happen).

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 16 1998 - 09:13:51 CDT

Re: Problem with BLOBS, UTL_RAW, OWS 2.1, NLS