SQL & PL/SQL

Hello All,

My requirement is to store an XML in a BLOB column of an Oracle table and then write the XML to a file.

I was able to achieve the above by the following code.

Create the table and directory and then the PL/SQL procedure follows:

To put the XML in a BLOB

create or replace
PROCEDURE BLOB_PROCEDURE AS
src_lob BFILE := BFILENAME('XML_DIR', 'IAD11886ATL.xml');
dest_lob BLOB;
BEGIN
DBMS_OUTPUT.PUT_LINE('1');
INSERT INTO lob_table VALUES(5, EMPTY_BLOB())
RETURNING doc INTO dest_lob;
DBMS_OUTPUT.PUT_LINE('2');

DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
DBMS_OUTPUT.PUT_LINE('3');
DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
SRC_LOB => src_lob,
AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
DBMS_OUTPUT.PUT_LINE('Done');
DBMS_LOB.CLOSE(src_lob);

Exception
when others then
DBMS_OUTPUT.PUT_LINE('Exception');

COMMIT;
END BLOB_PROCEDURE;



To write to a file system

DECLARE
t_blob BLOB;
t_len NUMBER;
t_file_name VARCHAR2(100);
t_output UTL_FILE.file_type;
t_TotalSize number;
t_position number := 1;
t_chucklen NUMBER := 4096;
t_chuck raw(4096);
t_remain number;
BEGIN
-- Get length of blob
SELECT DBMS_LOB.getlength (doc),'IAD11886ATL.xml'
INTO t_TotalSize, t_file_name FROM lob_table where rownum = 1;
t_remain := t_TotalSize;
-- The directory TEMPDIR should exist before executing
t_output := UTL_FILE.fopen ('XML_DIR','def.gif', 'wb', 32760);
-- Get BLOB
SELECT doc
INTO t_blob
FROM lob_table
WHERE rownum = 1;
-- Retrieving BLOB
WHILE t_position < t_TotalSize
LOOP
DBMS_LOB.READ (t_blob, t_chucklen, t_position, t_chuck);
UTL_FILE.put_raw (t_output, t_chuck);
UTL_FILE.fflush (t_output);
t_position := t_position + t_chucklen;
t_remain := t_remain - t_chucklen;
IF t_remain < 4096
THEN
t_chucklen := t_remain;
END IF;
END LOOP;
END;

The above code is for all the folks who are new to BLOB/CLOBS and reading/writing to a file system.

Is there any better way to achieve my requirement?