SQL & PL/SQL

Hello,

I want to write data to a file from a PL/SQL block.The file name should be generated based on the data.

This is the first time I working on file usage.

I have the following doubts regarding the same.Please give feedback on these..

The "create or replace directory dbdir as 'C:\TEMP\LOC1\';" - will create a directory on server or client.
The "Grant read on directory dbdir to xyz;" will give the user a read access.Is it possible for the user to write to the directory with this privilege.
How can the user view the files.
Is it possible to generate the filename within the block based on the data?

Is there any setting that needs to be done on the server.

Does this file write operation affect the server performance.


Thanks in advance,
Aghil


Below is the sample code which I tried for file write.Please tell me whether the steps I followed are correct or not.


I logged to SQL/PLUS as user having DBA privilege

SQL> show parameter utl


NAME TYPE VALUE

------------------------------------ ----------- ---------------------

utl_file_dir string


create or replace directory dbdir as 'C:\TEMP\LOC1\';

Grant read on directory dbdir to xyz;

SQL> select * from dba_directories;



OWNER DIRECTORY_NAME DIRECTORY_PATH

---------- --------------- ---------------------------------

SYS DBDIR C:\TEMP\LOC1\


Logged in has xyz.Then I created the following procedure.

Create or Replace procedure UTLTEST as

f1 utl_file.file_type;

begin

-- Open a file in write mode

f1 := utl_file.fopen('DBDIR','NEWFILE.TXT','W');

-- Write a line to a file

utl_file.put_line(f1,'1. This is a test of UTL_FILE packages');

utl_file.put_line(f1,'2. Oracle has added a new procedures in the package');

utl_file.put_line(f1,'3. We will see all the procedure one by one');

-- Close a file

utl_file.fclose(f1);

end;

/

when I tried to execute the procedure,I got the following error:
Procedure created.

SQL>
SQL>
SQL> execute utltest;
BEGIN utltest; END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "REG315_SER.UTLTEST", line 9
ORA-06512: at line 1