SQL & PL/SQL

We have a scenario to convert long data to files i.e long raw data type from an Oracle 8i is to be extracted as .DOC file (Word doc). Close to 8000 .doc files are expected to be created. Is there any automated batch script in PL/SQL which we can avail of to do this mass extraction.

Given below is the code, but we are unable to fully fetch all the records.

Guess it might be due to Huge memory size of Blob Objects.

The error code is:

ORA-21560: argument 2 is null, invalid, or out of range.

is there any method to clear memory of lob after writing a chunk of data.



DECLARE

output_file UTL_FILE.file_type;

chunk_size CONSTANT PLS_INTEGER := 4096;

buf raw(4096); -- Must be equal to chunk_size

written_sofar NUMBER := 0;

--(avoid PLS-00491: numeric literal required)

bytes_to_write PLS_INTEGER := 4096; --BINARY_INTEGER;

lob_len NUMBER;
lob1 BLOB;
filenm VARCHAR2 (12);
doc1 BLOB;
bflag boolean := true;
lob2 raw(32767);
a number(10);
--loc varchar2(25) := 'D:\oracle\temp\';



-- cursor to fetch task_id

cursor c_openfile is

SELECT task_id

FROM BLOB

WHERE blob_obj is not null;

BEGIN

FOR doc IN c_openfile

LOOP

-- selection of blob_obj into a variable(lob2) corresponding to task_id

select blob_obj into lob2
from blob where
task_id = doc.task_id;

-- getting the length of the blob_obj

lob_len := LENGTH (lob2);

dbms_output.put_line('Task #'||doc.task_id);
DBMS_OUTPUT.put_line ('length'||lob_len);

-- generating the file name from task_id
filenm := TO_CHAR (doc.task_id) || '.doc';
dbms_output.put_line(filenm);

-- opening file in the server Directory(MYDIR2)
output_file := UTL_FILE.fopen ('MYDIR2', filenm,'W',32767);
dbms_output.put_line('file opened');

-- Implicit conversion of Long_raw to blob
lob1 := lob2;

BEGIN
WHILE written_sofar + chunk_size < lob_len
LOOP

dbms_output.put_line('processing for task #'||doc.task_id);
dbms_output.put_line('Written so far '||written_sofar);

-- Reading the Blob data to buffer

DBMS_LOB.READ (lob1, bytes_to_write, written_sofar + 1, buf);

DBMS_OUTPUT.PUT_LINE('FILE READ');

--Writing from buffer to output file
UTL_FILE.put_raw(output_file, buf, bflag);

written_sofar := written_sofar + chunk_size;

dbms_output.put_line(written_sofar);

DBMS_OUTPUT.PUT_LINE('FILE WRITTEN');

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND
THEN

UTL_FILE.fclose (output_file);

DBMS_OUTPUT.PUT_LINE('CLOSE FILE');

END;

bytes_to_write := lob_len - written_sofar;

DBMS_OUTPUT.put_line ('bytes to write'||bytes_to_write);

dbms_output.put_line('processing for task #'||written_sofar);

-- to write the next chunk value into buffer

DBMS_LOB.READ (lob1, bytes_to_write, written_sofar+ 1, buf);

DBMS_OUTPUT.put_line ('bytes to write'||bytes_to_write);

UTL_FILE.put_raw(output_file, buf, bflag);

written_sofar := 0;

DBMS_OUTPUT.PUT_LINE('CLOSE FILE');

UTL_FILE.fclose (output_file);

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND
THEN

UTL_FILE.fclose (output_file);

DBMS_OUTPUT.PUT_LINE('CLOSE FILE');

WHEN OTHERS THEN

DBMS_OUTPUT.put_line (sqlerrm);
DBMS_OUTPUT.put_line ('Error');

END;