Monday, 4 February 2013

SQL-Create UTL_FILE Directory And Assign Grants On That


Step1:- Create UTL_FILE Directory And Assign Grants On That

Logon to SYSTEM/MANAGER

select * from all_directories --Here u can find all existing directories in your database 

--Give directory path where do u want to create your files.

Syntax:- Create or replace directory <directory_name> as <directory_path>;

ex:- create or replace directory TEST_DIR AS '/home/epxinstall/test';

--Give Grants to a specific user.

grant read,write on directory test_dir to SCOTT

exit from system/manager.

Logon to SCOTT/TIGER

ORA-29283: invalid file operation


As soon as you get this error just go and check your utl_file directory path permissions on operating system.
For example here you have given directory path as AS '/home/epxinstall/test', so it should have both read and wrote permissions.otherwise u would get this error.

To check the permissions on linux machine

Cd  /home/epxinstall/test
Ls – ltr
Chmod –R 777