I Have an oracle database with a table have a blob field have images store in it
how I can export these images stored in blob field back to a file in a folder on my computers harddisk oracle installed on server.
What I have tried:
CREATE OR REPLACE PROCEDURE SAVE_ATTACHMENT (
p_directory IN VARCHAR2
)
IS
v_blob BLOB;
v_start NUMBER := 1;
v_bytelen NUMBER := 2000;
v_len NUMBER;
v_raw RAW (2000);
v_x NUMBER;
v_output UTL_FILE.file_type;
v_file_name VARCHAR2 (200);
BEGIN
FOR i IN (SELECT DBMS_LOB.getlength (FILE_ATTACHED) v_len, '1.jpeg' as v_file_name,
FILE_ATTACHED v_blob
FROM GATEPASS_ATTACHMENT WHERE ROWNUM=1)
LOOP
v_output := UTL_FILE.fopen (p_directory, i.v_file_name || '.JPG', 'wb', 32760);
v_x := i.v_len;
v_start := 1;
v_bytelen := 2000;
WHILE v_start < i.v_len AND v_bytelen > 0
LOOP
DBMS_LOB.READ (i.v_blob, v_bytelen, v_start, v_raw);
UTL_FILE.put_raw (v_output, v_raw);
UTL_FILE.fflush (v_output);
v_start := v_start + v_bytelen;
v_x := v_x - v_bytelen;
IF v_x < 2000
THEN
v_bytelen := v_x;
END IF;
END LOOP;
UTL_FILE.fclose (v_output);
END LOOP;
END SAVE_ATTACHMENT;
tried the above code but getting error
Error starting at line : 1 in command -
EXECUTE SAVE_ATTACHMENT('/IMAGES')
Error report -
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "GPCS.SAVE_ATTACHMENT", line 20
ORA-06512: at "GPCS.SAVE_ATTACHMENT", line 20
ORA-06512: at line 1
29280. 00000 - "invalid directory path"
*Cause: A corresponding directory object does not exist.
*Action: Correct the directory object parameter, or create a corresponding
directory object with the CREATE DIRECTORY command.
Where the file will be downloaded? in my local PC ? or in Oracle server ?
exactly in which Drive the file will be downloaded