ORACLE APEX and UTL_FILE - creating a file on the server where database is installed.

Question

Hi all,

I'm not sure if what I'm trying to do is possible. I'm calling a procedure from an APEX application (or from the SQL workshop). This procedure should write to the server using UTl_FILE. However, it doesn't work. I don't get any errors, all the appropriate messages are displayed and the updates are done. However, the output file is not created. When I run the process from the SQL prompt on the server it works! Since APEX lives in the database on the server, I would have thought that I can call a process to write to the server. I've done all the necessary things like creating a directory and granting it the necessary priviliges. I've even tried using the wrong directory name and I got the error I expected so the directory is being acknowledged. I'm running Oracle 10g version 10.2.0.3.0 and APEX version 3.2.0.00.27. Oracle is running on Redhat Linux. All the appropriate objects are in my schema. Is this possible or do I need to find another way of doing this? I've granted READ and WRITE priviliges on the directory to the APEX_PUBLIC_USER? I'd really appreciate some help on this because it's becoming really frustrating! I use UTL_FILE for a lot of things so I'm quite familiar with how it works.

I did 2 things

1) I created a directory object to point to my local C drive, as expected I got the following message:

ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation

2) I ran the following code from the SQL Workshop :

declare

v_get boolean;
v_size number;
v_block binary_integer;


begin

utl_file.fgetattr('SQUID_TEST','settlement_dets_20000106.csv',
                  v_get,v_size,v_block);
if v_get then
dbms_output.put_line('It exists');
else
dbms_output.put_line('Shame!');
end if;
end;

And the output was as follows :

" Shame!

Statement processed.

0.00 seconds "

I ran the same code from SQL plus on the server and the result was :

"It exists

PL/SQL procedure successfully completed. "

Which is correct as the file exists. This is really baffling, I'm not getting any errors and I've now granted READ and WRITE to my Oracle directory to APEX_PUBLIC_USER and APEX_030200.