INSERT INTO s_image VALUES (1369, 'JTIFF', 'Y', 'Stglove.tif', NULL);
INSERT INTO s_image VALUES (1480, 'JTIFF', 'Y', 'Cabbat.tif', NULL);
INSERT INTO s_image VALUES (1482, 'JTIFF', 'Y', 'Pucbat.tif', NULL);
INSERT INTO s_image VALUES (1486, 'JTIFF', 'Y', 'Winbat.tif', NULL);
INSERT INTO s_image VALUES (1500, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1502, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1504, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1506, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1508, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1510, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1512, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1514, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1516, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1518, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1520, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1522, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1524, 'JTIFF', 'Y', 'No_Image.tif', NULL);
INSERT INTO s_image VALUES (1526, 'JTIFF', 'Y', 'No_Image.tif', NULL);
COMMIT;
/* ******************************************************** */
connect system/manager@t2
create or replace directory b_path as '/home2/teach2/COUNTRY_PIC';
grant all on directory b_path to PUBLIC;
/* ******************************************************** */
CREATE OR REPLACE PROCEDURE Load_blob
IS
BLOB_ONE BLOB;
bfile_one BFILE := NULL;
v_image_id NUMBER;
v_length NUMBER := 0;
v_number NUMBER := 0;
v_sqlmess VARCHAR2(300);
CURSOR file_cursor IS
SELECT id, filename
FROM s_image;
BEGIN
dbms_output.put_line ( 'Starting Load ....' );
FOR file_rec IN file_cursor LOOP
BEGIN
UPDATE s_image
SET image = empty_blob()
WHERE id = file_rec.id
-- RETURNING image INTO BLOB_ONE;
SELECT image
INTO BLOB_ONE
FROM s_image
WHERE id = file_rec.id;
bfile_one := BFILENAME( 'B_PATH', file_rec.filename ); -- Obtain BFILE locator
v_length := dbms_lob.getlength ( bfile_one ); -- Obtain size of BLOB
dbms_lob.fileopen( bfile_one ); -- Load BFILE into BLOB
dbms_lob.loadfromfile( BLOB_ONE, bfile_one, v_length );
dbms_lob.fileclose( bfile_one );
UPDATE s_image
SET image = BLOB_ONE
WHERE id = file_rec.id;
dbms_output.put_line ('Image updated in s_image for file: ' ||file_rec.filename||' File Size: '||v_length);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line ( 'Error...no_data_found' );
WHEN others THEN
v_sqlmess := TO_CHAR( sqlcode ) || ' : ' || sqlerrm ;
dbms_output.put_line ( v_sqlmess );
END;
END LOOP;
COMMIT;
END load_blob;
/