Re: Issues with writing to server-side log file.
From: ddf <oratune_at_msn.com>
Date: Fri, 6 Feb 2009 13:34:14 -0800 (PST)
Message-ID: <0e1d4e4a-e617-4619-817d-8b95eabd45c5_at_z28g2000prd.googlegroups.com>
On Feb 6, 2:55�pm, seannakasone <snaka..._at_flex.com> wrote:
> Hello, on the database server running Oracle 9.2, I want to write to a log
> file. �I unsuccessfully tried the following:
>
> DECLARE
> � � � f UTL_FILE.FILE_TYPE;
> BEGIN
> � � � f := UTL_FILE.FOPEN('d:\temp', 'log.txt', 'w');
> END;
> /
>
> It says, 'ORA-29280: invalid directory path' even if that folder exists
> and the permissions allow everyone to write to it.
>
> Someone told me to create a directory object so I can grant permissions to
> it, as follows:
>
> CREATE OR REPLACE DIRECTORY dirobj as 'd:\temp';
> GRANT READ,WRITE ON DIRECTORY dirobj to snakason;
> CONN snakason/mypass_at_hedev2
> DECLARE
> � � � f UTL_FILE.FILE_TYPE;
> BEGIN
> � � � f := UTL_FILE.FOPEN(dirobj, 'log.txt', 'r', 5000);
> END;
> /
>
> But now I'm getting this error "PLS-00201: identifier 'DIROBJ' must be
> declared". �I'm guessing dirobj is not in scope of the PL/SQL block, but
> how to I remedy that? �If I put those lines within the PL/SQL block, I get
> errors. �Sorry, I'm an Oracle newbie so I don't what works or does not
> work in a PL/SQL block.
9 END;
10 /
Yadda yadda yadda
Date: Fri, 6 Feb 2009 13:34:14 -0800 (PST)
Message-ID: <0e1d4e4a-e617-4619-817d-8b95eabd45c5_at_z28g2000prd.googlegroups.com>
On Feb 6, 2:55�pm, seannakasone <snaka..._at_flex.com> wrote:
> Hello, on the database server running Oracle 9.2, I want to write to a log
> file. �I unsuccessfully tried the following:
>
> DECLARE
> � � � f UTL_FILE.FILE_TYPE;
> BEGIN
> � � � f := UTL_FILE.FOPEN('d:\temp', 'log.txt', 'w');
> END;
> /
>
> It says, 'ORA-29280: invalid directory path' even if that folder exists
> and the permissions allow everyone to write to it.
>
> Someone told me to create a directory object so I can grant permissions to
> it, as follows:
>
> CREATE OR REPLACE DIRECTORY dirobj as 'd:\temp';
> GRANT READ,WRITE ON DIRECTORY dirobj to snakason;
> CONN snakason/mypass_at_hedev2
> DECLARE
> � � � f UTL_FILE.FILE_TYPE;
> BEGIN
> � � � f := UTL_FILE.FOPEN(dirobj, 'log.txt', 'r', 5000);
> END;
> /
>
> But now I'm getting this error "PLS-00201: identifier 'DIROBJ' must be
> declared". �I'm guessing dirobj is not in scope of the PL/SQL block, but
> how to I remedy that? �If I put those lines within the PL/SQL block, I get
> errors. �Sorry, I'm an Oracle newbie so I don't what works or does not
> work in a PL/SQL block.
You need to submit your directory name in CAPS and in single quotes:
SQL> CREATE OR REPLACE DIRECTORY dirobj as 'c:\temp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dirobj to bing;
Grant succeeded.
SQL> CONN bing/*&*&*&*&*&*&*&*&*&
Connected.
SQL> set serveroutput on size 1000000
SQL> DECLARE
2 f UTL_FILE.FILE_TYPE;
3 filebuf varchar2(4000);
4 BEGIN
5 f := UTL_FILE.FOPEN('DIROBJ', 'log.txt', 'R',
4000);
6 utl_file.get_line(f, filebuf);
7 dbms_output.put_line(filebuf);
8 utl_file.fclose(f);
9 END;
10 /
Yadda yadda yadda
PL/SQL procedure successfully completed.
SQL> David Fitzjarrell Received on Fri Feb 06 2009 - 15:34:14 CST