Tuesday, April 15, 2008

How to Remove a File From PL/SQL

Way1:
----------
begin
dbms_backup_restore.deletefile('/oracle/a.txt');
end;


But don't use dbms_backup_restore.deletefile. It does not guarantee about deletion of the file. Whether the operation completed or not it always return PL/SQL procedure successfully completed.

Way2: Using Utl_file Package
------------------------------
1)Create directory where file resides.
SQL> !touch /export/home/oracle/Arju/1.txt
SQL> create directory ddel as '/export/home/oracle/Arju';

Directory created.

2)Grant permission to the user who will do the operation.
SQL> grant read,write on directory ddel to public;

Grant succeeded.

3)Execute the procedure.
SQL> exec utl_file.fremove('DDEL','1.txt');

PL/SQL procedure successfully completed.

SQL> !ls /export/home/oracle/Arju

1 comment: