SQL & PL/SQL

Greetings experts,

I had posted this error last week but I was asked by one of the experts to show exact error but unfortunately the server was shutdown before I could run it again.


Here is the error I am getting:

ORA-01465: invalid hex number
ORA-06512: at "User.GETFile", line 32
ORA-06512: at line 8

Here is the code. Your help is GREATLY appreciated.

create or replace
PROCEDURE Getfile(pfname VARCHAR2, display_name IN VARCHAR2)
IS
vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
v_name VARCHAR2(100);
lv_str_len NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
--lv_str_len := Length(pfname);

--v_name := display_name||upper(substr(pfname,lv_str_len-3,lv_str_len));
v_name := display_name;

l_output := utl_file.Fopen('TheDIR', v_name, 'w', 32760);

-- get length of blob
SELECT dbms_lob.Getlength(FILE_NAME)
INTO len
FROM GFILE
WHERE file_name = pfname;

-- dbms_output.put_line('Length: '||len);
-- save blob length
x := len;

-- select blob into variable
SELECT FILE_NAME
INTO vblob
FROM GFILE
WHERE file_name = pfname;

-- if small enough for a single write
IF len < 32760 THEN
-- dbms_output.put_line('Single write ');
utl_file.Put_raw(l_output, vblob);

utl_file.Fflush(l_output);
ELSE -- write in pieces
-- dbms_output.put_line('multi write '||vstart);
vstart := 1;

WHILE vstart < len LOOP
dbms_lob.READ(vblob, bytelen, vstart, my_vr);

utl_file.Put_raw(l_output, my_vr);

utl_file.Fflush(l_output);

-- set the start position for the next cut
vstart := vstart + bytelen;

-- set the end position if less than 32000 bytes
x := x - bytelen;

IF x < 32000 THEN
bytelen := x;
END IF;
END LOOP;
END IF;

dbms_output.Put_line('End');

utl_file.Fclose(l_output);
END getfile;