Friday, November 18, 2011

Remove OS file using oracle pl/sql: dbms_backup_restore/utl_file

1.    Dbms_backup_restore function to delete a file test.txt from OS.
$ ls -ltr
-rw-r--r-- 1 oracle oinstall 0 Nov 18 09:25 test.txt

$ sqlplus / as sysdba
SQL> begin
dbms_backup_restore.deletefile('/home/oracle/BKP/test.txt');
end;
 /
PL/SQL procedure successfully completed.
Now check the file.

$ ls -ltr
total 0
Gone!!!!
  
2.    utl_file.fremove to remove file from Oracle directory. 
CREATE DIRECTORY DIR AS '/oracle/admin';

ls -ltr
-rw-r--r--  1 oracle oinstall    0 Nov 18 09:35 directory_test.txt

exec utl_file.fremove('DIR','directory_test.txt');
PL/SQL procedure successfully completed.
Check the file again…..
$ ls -ltr
total 0

Gone!!!

No comments: