Re: UTL FILE
Date: Wed, 16 Sep 2009 06:24:53 -0600
I'm on a Windows box also (actually several). In my case, I have some dynamic PL/SQL routines the users can run, where I need to issue OS commands on-the-fly (so the scheduler wouldn't work). I'm basically generating KML (Google Earth files) on the fly from the database server, then I need to move them over to a directory on my web server so the user can get them.
What I stumbled across was the following little java stored procedure: http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php
I think this version was written for back in the 8i days, but I'm on 11 now and it still works. Starting with version 11 though, I did experience some errors that turned out to be related to a tightened up Oracle security model.
In order for this to run in 11g (at least under Windows), you must set the database service and the listener service to run as a named user account. Running these as the default local system account won't work, since the local system account doesn't know about the environment variables, drive mappings, etc. that a named user account would.
So other than the new security restriction with 11 on Windows, it's fairly simple. All the instructions are on the web page.
-- -- Bill Ferguson On Tue, Sep 15, 2009 at 4:35 PM, Kenneth Naim <kennaim_at_gmail.com> wrote:Received on Wed Sep 16 2009 - 07:24:53 CDT
> I write some finance interface files using utl_file from 1 10r2 database
> running on windows server onto another windows server using the UNC name and
> it works fine. Our internal audit would like us to make the file read only.
> I have googled but havenít been able to find a way of changing a fileís
> attribute using utl_file or PL/SQL. Anyone know how to accomplish this feat?