Home » SQL & PL/SQL » SQL & PL/SQL » BMP not correctly formated with UTF8 Characterset (Database 12c)
BMP not correctly formated with UTF8 Characterset [message #684727] Thu, 05 August 2021 05:22 Go to next message
Usman Tabassum
Messages: 2
Registered: November 2019
Junior Member
Hi experts,
I am making QR code from MAKE_QR package and save on directory with this procedure
create or replace PROCEDURE blob_to_file (i_dir    IN VARCHAR2,
                                          i_file   IN VARCHAR2,
                                          i_blob   IN BLOB)
AS
   l_file       UTL_FILE.file_type;
   l_buffer     RAW (32767);
   l_amount     BINARY_INTEGER := 32767;
   l_pos        INTEGER := 1;
   l_blob_len   INTEGER;
BEGIN
   l_blob_len := DBMS_LOB.getlength (i_blob);

   l_file :=
      UTL_FILE.fopen (i_dir,
                      i_file,
                      'WB',
                      32767);

   WHILE l_pos < l_blob_len
   LOOP
      DBMS_LOB.read (i_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
      l_pos := l_pos + l_amount;
   END LOOP;

   UTL_FILE.fclose (l_file);
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      RAISE;
END blob_to_file;

BMP file opening fine when database character set is WE8MSWIN1252 in database 12C, but it fails when database character set is UTF8 .

Can i fix it with UTF8?
Re: BMP not correctly formated with UTF8 Characterset [message #684730 is a reply to message #684727] Thu, 05 August 2021 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68040
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given you work on RAW and BLOB and correctly use PUT_RAW, character set should not matter, so I tend to think it is a bug.

However, I'm not sure the code is correct if the BLOB size is not an exact multiple of 32767. DBMS_LOB usually doesn't like when the actual length is less than the asked one on DBMS_LOB.read.

I'd change the code to something like:
   WHILE l_pos+32767 <= l_blob_len
   LOOP
      DBMS_LOB.read (i_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
      l_pos := l_pos + l_amount;
   END LOOP;
   l_amount := l_blob_len - l_pos;
   IF l_amount > 0 THEN
      l_buffer := null;  -- Clean the buffer
      DBMS_LOB.read (i_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
   END IF;
Re: BMP not correctly formated with UTF8 Characterset [message #684733 is a reply to message #684730] Fri, 06 August 2021 00:57 Go to previous messageGo to next message
Usman Tabassum
Messages: 2
Registered: November 2019
Junior Member
Thanks Michel,

I have done with no luck

create or replace PROCEDURE blob_to_file (i_dir    IN VARCHAR2,
                                          i_file   IN VARCHAR2,
                                          i_blob   IN BLOB)
AS
   l_file       UTL_FILE.file_type;
   l_buffer     RAW (32767);
   l_amount     BINARY_INTEGER := 32767;
   l_pos        INTEGER := 1;
   l_blob_len   INTEGER;
BEGIN
   l_blob_len := DBMS_LOB.getlength (i_blob);

   l_file :=
      UTL_FILE.fopen (i_dir,
                      i_file,
                      'WB',
                      32767);

  WHILE l_pos+32767 <= l_blob_len
   LOOP
      DBMS_LOB.read (i_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
      l_pos := l_pos + l_amount;
   END LOOP;
   l_amount := l_blob_len - l_pos;
   IF l_amount > 0 THEN
      l_buffer := null;  -- Clean the buffer
      DBMS_LOB.read (i_blob,
                     l_amount,
                     l_pos,
                     l_buffer);
      UTL_FILE.put_raw (l_file, l_buffer, TRUE);
   END IF;

   UTL_FILE.fclose (l_file);
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      RAISE;
END blob_to_file;
begin
blob_to_file ('QR',
                        'XXX123456X',
                                  MAKE_QR.QR_BIN('XXX123456X'));
end;

Re: BMP not correctly formated with UTF8 Characterset [message #684736 is a reply to message #684733] Fri, 06 August 2021 04:16 Go to previous message
Michel Cadot
Messages: 68040
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So, if it is not this it is, I think, the first option: a bug then only Oracle or one who encountered the same issue can help.
If you have a MOS account then raise a SR, I didn't find anything about this problem on my side.

Hoping you will find a solution, and share it with us.

Previous Topic: Question about databses
Next Topic: Table type collection in SELECT
Goto Forum:
  


Current Time: Sat Nov 27 05:38:06 CST 2021