Skip to Main Content

Clob to .msg file -

User_I94XLMar 23 2022

I did manage to create two procedure which is creating .msg file but it is not exporting all the data to the file - Not sure what to do
--------------------------------------
Declare
Cursor c_emp_files
is
SELECT id, clob_content
FROM table;

Begin

for c in c_emp_files loop
clob_to_file('c:\temp\', 'F'||c.file_id||'.msg', c.clob_content);
end loop;
End;

--------------------------------------------------------
Procedure

create or replace PROCEDURE blob_to_file (i_dir IN VARCHAR2,
i_file_name IN VARCHAR2,
i_clob IN CLOB)
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_clob);

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

WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.read (i_clob,
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;
END blob_to_file;

Comments
Post Details
Added on Mar 23 2022
1 comment
14 views