1

I am trying to identify all files in a particular directory and delete them. I know that there is a Java procedure for this but I am looking for something in pure PL/SQL.

If I am not able to get the list of files and delete them, then I would not mind deleting/dropping the directory and creating it again. If someone can guide me on this it will be of great help.

Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
Shashi337
  • 11
  • 1
  • 1
  • 2
  • 1
    Welcome to Stack Overflow :-) Have you seen http://oracle-documentation.com/plsql/browsing-directories-with-plsql ? It suggests you might be struggling if you want to do this in pure PL/SQL with no Java, unfortunately. – James Green May 30 '13 at 16:30
  • 1
    As James has mentioned there isn't a pure PL/SQL method to get a directory listing. As you are on 11g it may be worth looking at the feature called "external table preprocessor" which was mentioned in the top voted answer to this question: http://stackoverflow.com/questions/3529273/how-to-move-a-text-file-using-oracle. which in turn mentions this article: http://www.oracle-developer.net/display.php?id=513 – Ian Carpenter May 30 '13 at 21:49

2 Answers2

1

I have used the following not too long ago and it worked very well for me

http://plsqlexecoscomm.sourceforge.net/

It is as easy as this

select os_command.exec_clob('/bin/ls -la /home/oracle') COMMAND from dual

You could write a PL/SQL routine that scans line by line through the received clob of the ls command and issue a rm shell command.

hol
  • 8,255
  • 5
  • 33
  • 59
1

You can use a scheduler job for creating a directory listing: http://www.oracle-base.com/articles/11g/scheduler-enhancements-11gr1.php#returning_stdout_and_stderr

Toru
  • 905
  • 1
  • 9
  • 28