Hello,
I have a stored procedure for a report which collates data from various tables, creates a csv out of it, zips it up and writes it to the file system.
CREATE OR REPLACE PROCEDURE reg_extract (
p_filepath IN VARCHAR2
)
IS
xfh UTL_FILE.file_type;
xfilename VARCHAR2 (50);
BEGIN
/* OPEN THE FILE */
xfilename :=
'Reg_Exract_'|| TO_CHAR (SYSDATE, 'DD-MON-YYYY-HH24MISS')|| '.ZIP';
xfh := UTL_FILE.fopen (p_filepath, xfilename, 'WB', 16384);
UTL_FILE.put_raw (xfh,UTL_COMPRESS.lz_compress (UTL_RAW.cast_to_raw ('TEST')));
UTL_FILE.fflush (xfh);
COMMIT;
UTL_FILE.fflush (xfh);
UTL_FILE.fclose (xfh);
END;
/
I now need to write this file DIRECTLY to a BLOB column instead of the file system. I found one option where I can continue to write the file to the file system first and then load it into the BLOB column using BFILE and DBMS_LOB packages and then finally delete the file but I think there should be a better way to go about it.
ALSO I am having trouble setting the extension of the file within the zipped file, currently it gets created without one, any tips for that ?
Thanks in advance