Problem with BLOBS, UTL_RAW, OWS 2.1, NLS

From: <Richard_Hirsch_at_hotmail.com>
Date: Wed, 16 Sep 1998 11:00:07 GMT
Message-ID: <6to5nm$kme$1@nnrp1.dejanews.com>

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: .....

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).

--

What is interesting is that during database creation, we use the default NLS values which are 'AMERICAN_AMERICA.WE...'. But when I look at the values in the table containing the actual NLS values, 'GERMANY_GERMAN.WE...' are present. I have tried both values (German and American) in the OWS 2.1 Configuration File and nothing seems to help. Do you or anyone else have any idea what the problem could be.

Thanks

Dick

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 16 1998 - 06:00:07 CDT