Re: compute md5-hash from file

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 19 Mar 2007 15:06:39 +0100
Message-ID: <45FE98EF.3030009@gmail.com>

Thomas Peter schrieb:
> hi,
> i try to compute a md5 checksum of a file with a pl/sql procedure.
> and i'm a pl/sql noob...
>
> when i use UTL_FILE.FOPEN to open the file
> and UTL_FILE.GET_RAW for reading,
> i get the md5-hash via dbms_obfuscation_toolkit.MD5 and passing the
> value from GET_RAW
> but i experience a limit at 32767byte, which is the max buffersize of
> utl_file (right?)
>
> reading the content line-by-line is not appropiate for computing a
> chechsum of the whole file
>
> so i opended the file as blob:
>
> b_file BFILE;
> b_file_length BINARY_INTEGER;
> dst_blob BLOB;
>
> DBMS_LOB.CREATETEMPORARY(dst_blob,true);
> b_file := BFILENAME('DIR','test_utl_file.txt');
> DBMS_LOB.OPEN(b_file,DBMS_LOB.LOB_READONLY);
> b_file_length := dbms_lob.getlength(b_file);
> Dbms_Lob.loadfromfile(dst_blob, b_file, b_file_length);
> checksum := dbms_obfuscation_toolkit.MD5(input => dst_blob);
> dbms_lob.fileclose(b_file);
>
> but i get an ORA-06502 at the line of calling dbms_obfuscation_toolkit.MD5
>
> any hint what i do wrong?
>
> thnx in advance
> thomas
>

How you declared checksum variable?
For me your example works (i hope, your test_utl_file don't exceed 2Gb, in that case you would get however ORA-01426) :

SQL> declare

   2  b_file                BFILE;
   3  b_file_length BINARY_INTEGER;
   4  dst_blob             BLOB;

   5 checksum raw(16);
   6 begin

   7          DBMS_LOB.CREATETEMPORARY(dst_blob,true);
   8          b_file := BFILENAME('HOME','oracle/test_utl_file.txt');
   9          DBMS_LOB.OPEN(b_file,DBMS_LOB.LOB_READONLY);
  10          b_file_length := dbms_lob.getlength(b_file);
  11          Dbms_Lob.loadfromfile(dst_blob, b_file, b_file_length);
  12          checksum := dbms_obfuscation_toolkit.MD5(input => dst_blob);
  13          dbms_lob.fileclose(b_file);
  14          dbms_output.put_line(rawtohex(checksum));

  15 end;
  16 /
CBC354D76F739291739A1A94B7B3B702 PL/SQL procedure successfully completed.

SQL> !cat test_utl_file.txt|md5sum
cbc354d76f739291739a1a94b7b3b702 -

Best regards

Maxim Received on Mon Mar 19 2007 - 09:06:39 CDT