Hello, I am trying to find a way to write a file into the sub folder of an Oracle Directory. I can write into the base of the oracle directory but not into the sub folders.
To keep it simple, This is what we have that currently works, after that is what we need.
V_BLOB_LENGTH := DBMS_LOB.GETLENGTH (V_BLOB);
V_OUT_FILE := UTL_FILE.FOPEN (P_ORA_DIR, P_FILE_NAME, 'wb', V_CHUNK_SIZE);
WHILE V_BLOB_POSITION <= V_BLOB_LENGTH
LOOP
IF V_BLOB_POSITION + V_CHUNK_SIZE - 1 > V_BLOB_LENGTH THEN
V_CHUNK_SIZE := V_BLOB_LENGTH - V_BLOB_POSITION + 1;
END IF;
DBMS_LOB.READ (V_BLOB, V_CHUNK_SIZE, V_BLOB_POSITION, V_BUFFER);
UTL_FILE.PUT_RAW (V_OUT_FILE, V_BUFFER, TRUE);
V_BLOB_POSITION := V_BLOB_POSITION + V_CHUNK_SIZE;
END LOOP;
UTL_FILE.FCLOSE (V_OUT_FILE);The only difference is adding the '/subfolder/' to the filename in the FOPEN
V_BLOB_LENGTH := DBMS_LOB.GETLENGTH (V_BLOB);
V_OUT_FILE := UTL_FILE.FOPEN (P_ORA_DIR, '/subfolder/'||P_FILE_NAME, 'wb', V_CHUNK_SIZE);
WHILE V_BLOB_POSITION <= V_BLOB_LENGTH
LOOP
IF V_BLOB_POSITION + V_CHUNK_SIZE - 1 > V_BLOB_LENGTH THEN
V_CHUNK_SIZE := V_BLOB_LENGTH - V_BLOB_POSITION + 1;
END IF;
DBMS_LOB.READ (V_BLOB, V_CHUNK_SIZE, V_BLOB_POSITION, V_BUFFER);
UTL_FILE.PUT_RAW (V_OUT_FILE, V_BUFFER, TRUE);
V_BLOB_POSITION := V_BLOB_POSITION + V_CHUNK_SIZE;
END LOOP;
UTL_FILE.FCLOSE (V_OUT_FILE);Reason for this: We have dynamic projects, each one contains around 5000+, 2 gigs of files per sub folder. We can read the files when placed in the sub folders using BFILE, but we need to create an html upload system instead of FTP accounts.
So basically, is their a way to make the following work:
UTL_FILE.FOPEN (P_ORA_DIR, '/subfolder/'||P_FILE_NAME, 'wb', V_CHUNK_SIZE)
Currently when we append a folder name to the file, it just ignores it and places it in the root of the Oracle Directory. Any suggestions would be appreciated.
This is a security restriction. Because otherwise, an Oracle directory object created for (say) "/u01" would have potentially read/write access to the all folders underneath it, and that could be catastrophic.
You could dynamically handle this with a wrapper to create a directory if required, eg
SQL> grant create any directory to mcdonac;
Grant succeeded.
SQL> create or replace directory TEMP as 'c:\temp';
Directory created.
SQL>
SQL> create or replace
2 procedure my_fopen(fname varchar2) is
3 subdir varchar2(100);
4 filename varchar2(100);
5 dirhash number;
6 direxists int;
7 begin
8 if fname like '%\%' then
9 subdir := substr(fname,1,instr(fname,'\',-1,1));
10 filename := substr(fname,1+instr(fname,'\',-1,1));
11 dbms_output.put_line('Directory = '||subdir);
12 dbms_output.put_line('Filename = '||filename);
13
14 select ora_hash(subdir) into dirhash from dual;
15 select count(*)
16 into direxists
17 from all_directories
18 where directory_name = 'TEMP_'||dirhash;
19
20 if direxists = 1 then
21 dbms_output.put_line('Directory already exists as '||'TEMP_'||dirhash);
22 else
23 dbms_output.put_line('Creating new directory '||'TEMP_'||dirhash);
24 dbms_output.put_line('create directory '||'TEMP_'||dirhash||' as ''c:\temp\'||subdir||'''');
25 execute immediate 'create directory '||'TEMP_'||dirhash||' as ''c:\temp\'||subdir||'''';
26 end if;
27 else
28 dbms_output.put_line('Directory = TEMP');
29 dbms_output.put_line('Filename = '||fname);
30 end if;
31 end;
32 /
Procedure created.
SQL> sho err
No errors.
SQL>
SQL> set serverout on
SQL> exec my_fopen('abc.dat');
Directory = TEMP
Filename = abc.dat
PL/SQL procedure successfully completed.
SQL> exec my_fopen('subdir\abc.dat');
Directory = subdir\
Filename = abc.dat
Creating new directory TEMP_3814585973
create directory TEMP_3814585973 as 'c:\temp\subdir\'
PL/SQL procedure successfully completed.
SQL> exec my_fopen('subdir\abc.dat');
Directory = subdir\
Filename = abc.dat
Directory already exists as TEMP_3814585973
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
so you are creating a directory for any required subfolder as required when you first open the file.