Can you please help me in Oracle for writing the table contents in to a file using UTL functions

Question

While writing the SQL procedure for creating a CSV file from the contents of the table, I am stuck at the error below:

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at "DSI_DPIPE.BUSSHOURS", line 28
ORA-06512: at "DSI_DPIPE.BUSSHOURS", line 55
ORA-06512: at line 1

First line is:

file_http UTL_FILE.file_type;

Same error is coming even i am using the statement:

file_http sys.UTL_FILE.file_type;

Thanks for your valuable solution in advance.

Answer 1

Has the oracle user access privileges for writing in the directory that you want to put the file?

EDIT: If you're in a Unix enviroment, you should manually login as the oracle user, go to the path that you're trying to create the file and use the command "touch" to create a file. If you can't create a file in this manner, you know that the problem is about "access privileges" in the operating sistem, else the problem is in ORACLE. (will reduce the search in 50% ;D )

EDIT 2:
If UTL_FILE_DIR = * then database permissions are disable and all files and directories are accessible to all user. But I think this applies only in a database level, so the operating system has also something to say. If the oracle user don't have a write access to some directory, then the users of the database can't adquire this right, no matter that UTL_FILE_DIR says.

Answer 2

Check the directory path is correct and you have the right permissions set (i.e. Oracle is allowed by the operating system to write to that location).

Answer 3

Use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.

UTL_FILE_DIR = * is dangerous because is allows database users that have access to the utl_file package to read/write files on the database server with the permissions of the user oracle (datafiles,init.ora, listener.ora etc.).

Do you have the right permissions to write/read to the directory. User oracle on the server needs read/write permission.