|
|
| Re: How can I delete all files from folder specified e.g. like *.dat [message #125597 is a reply to message #125553] |
Mon, 27 June 2005 21:51  |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In SQL you can just use the HOST command, but from PL/SQL I believe you will need a combination of PL/SQL and java or C or some such thing. What is your objection to java? I have posted a simple java method below for the benefit of others who may have the same question. It loads the filenames into an Oracle temporary table, then loops through a cursor of the .dat files and remove them using fremove. I have separated the response into two parts, first the stuff that you only need to run once from SQL*PLUS to crate the java and Oracle stuff that you will need, then a simple PL/SQL block to do what you want.
-- stuff you run only once to create what you need:
scott@ORA92> CONNECT system AS SYSDBA
Connected.
scott@ORA92> @ login
scott@ORA92> SET ECHO OFF
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys@ORA92
sys@ORA92> GRANT JAVAUSERPRIV to scott
2 /
Grant succeeded.
sys@ORA92> CONNECT scott
Connected.
sys@ORA92> @ login
sys@ORA92> SET ECHO OFF
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott@ORA92
scott@ORA92> create global temporary table DIR_LIST
2 ( filename varchar2(255) )
3 on commit delete rows
4 /
Table created.
scott@ORA92> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23
24 }
25 /
Java created.
scott@ORA92> create or replace
2 procedure get_dir_list( p_directory in varchar2 )
3 as language java
4 name 'DirList.getList( java.lang.String )';
5 /
Procedure created.
scott@ORA92> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle'
2 /
Directory created.
-- what you would run to remove all the .dat files:
scott@ORA92> BEGIN
2 get_dir_list ('c:\oracle');
3 FOR r IN
4 (SELECT filename
5 FROM dir_list
6 WHERE LOWER (filename) LIKE '%.dat')
7 LOOP
8 UTL_FILE.FREMOVE ('MY_DIR', r.filename);
9 END LOOP;
10 COMMIT;
11 END;
12 /
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
