Table Blob to Oracle Directory
How to save table images to oracle directory?
Files from table to Oracle directory
1DECLARE
2 l_file UTL_FILE.file_type;
3 l_buffer RAW (32767);
4 l_amount BINARY_INTEGER := 32767;
5 l_position INTEGER := 1;
6 l_blob_length INTEGER;
7BEGIN
8 FOR l_cnt IN (SELECT filename, product_image FROM demo_product_info)
9 LOOP
10 l_file := UTL_FILE.fopen ('MY_DIR', l_cnt.filename, 'WB');
11
12 -- Start sending file to Oracle directory l_blob_length := DBMS_LOB.getlength (l_cnt.product_image);
13 WHILE l_position < l_blob_length
14 LOOP
15 DBMS_LOB.read (l_cnt.product_image,
16 l_amount,
17 l_position,
18 l_buffer);
19 UTL_FILE.put_raw (l_file, l_buffer, TRUE);
20 l_position := l_position + l_amount;
21 END LOOP;
22
23 UTL_FILE.fclose (l_file);
24
25 IF UTL_FILE.is_open (l_file)
26 THEN
27 UTL_FILE.fclose (l_file);
28 END IF;
29 END LOOP;
30EXCEPTION
31 WHEN OTHERS
32 THEN
33 IF UTL_FILE.is_open (l_file)
34 THEN
35 UTL_FILE.fclose (l_file);
36 END IF;
37END;
How to Send all files in ZIP?
1DECLARE
2 l_file UTL_FILE.file_type;
3 zip BLOB;
4 l_buffer RAW (32767);
5 l_amount BINARY_INTEGER := 32767;
6 l_position INTEGER := 1;
7 l_blob_length INTEGER;
8BEGIN
9 l_file := UTL_FILE.fopen ('MY_DIR', 'Products.zip', 'WB');
10
11 -- Zipping the files FOR l_cnt IN (SELECT filename, product_image FROM demo_product_info) LOOP apex_zip.add_file (p_zipped_blob => zip, p_file_name => l_cnt.filename, p_content => l_cnt.product_image); END LOOP;
12
13 -- FInish Zip File apex_zip.finish (p_zipped_blob => zip);
14
15 -- Start sending file to Oracle directory l_blob_length := DBMS_LOB.getlength (zip);
16 WHILE l_position < l_blob_length
17 LOOP
18 DBMS_LOB.read (zip,
19 l_amount,
20 l_position,
21 l_buffer);
22 UTL_FILE.put_raw (l_file, l_buffer, TRUE);
23 l_position := l_position + l_amount;
24 END LOOP;
25
26 UTL_FILE.fclose (l_file);
27
28 IF UTL_FILE.is_open (l_file)
29 THEN
30 UTL_FILE.fclose (l_file);
31 END IF;
32EXCEPTION
33 WHEN OTHERS
34 THEN
35 IF UTL_FILE.is_open (l_file)
36 THEN
37 UTL_FILE.fclose (l_file);
38 END IF;
39END;