Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to get the all file names from the folder and move it to another folder

Amit BhandariJun 4 2012 — edited Jun 5 2012
Hello,
How do I read files from a certain directory with PL/SQL, without knowing the exact name ? My application interface with another systems which puts files in a specific directory on the server. UTL_FILE method reads a file when you know the name of the file, but I don't know the name in advance. Is it possible to figure-out the file name ? I also used Tom's method "http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584" (related to Java stored procedure) but am getting error for procedure "ORA-00900: invalid SQL statement". Below is the code that i have used from Tom's forums -

CREATE OR REPLACE AND compile java source named "DirList"
AS
import java.io.*;
import java.sql.*;

public class DirList
{
public static void getList(String directory)
throws SQLException
{
File path = new File( directory );
String[] list = path.list();
String element;

for(int i = 0; i < list.length; i++)
{
element = list;
#sql { INSERT INTO DIR_LIST (FILENAME)
VALUES (:element) };
}
}

}
/
**********************************************************************
CREATE OR REPLACE PROCEDURE get_dir_list(p_directory in VARCHAR2) AS language java
name 'DirList.getList(java.lang.String) htp.p(p_directory);';
**********************************************************************
exec get_dir_list( '/CERT_XML' );
**********************************************************************

i would like to know is there any alternative approach to retrieve the all file names from the server folder and move those files to another folder?

Thanks
Amit Bhandari
This post has been answered by ShankarViji on Jun 5 2012
Jump to Answer

Comments

Igor.M
check this (as sys) 
set serveroutput on
DECLARE
ns VARCHAR2 (1024);
v_directory VARCHAR2 (1024);
BEGIN
v_directory := 'D:\USERS';
SYS.DBMS_BACKUP_RESTORE.searchfiles (v_directory, ns);

FOR xx IN (SELECT fname_krbmsft AS NAME
FROM x$krbmsft)
LOOP
DBMS_OUTPUT.put_line (xx.NAME);
END LOOP;
END;
/
or external table + PREPROCESSOR

http://www.oracle-developer.net/display.php?id=513

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3021213500346960197
unknown-7404
>
Below is the code that i have used from Tom's forums
>
That IS NOT identical to the code from the forum article you linked. Use cut & paste and be careful not to alter the code.

For example this is the original line #18 from Tom's code 
18          element = list;

And this is the line from what you posted
element = list;

Tom's code is reference ONE item from the array but your code references the entire array because you left off the subscript. Then when you reference 'element' in the query you will get an error because you need to provide a single item but you are providing an array.
{quote}
i would like to know is there any alternative approach to retrieve the all file names from the server folder and move those files to another folder? 
{quote}
No - just use the code Tom provided. You can modify it to do the 'move' also. There won't be anything better than one short, simple procedure.
ShankarViji
Answer
Hi,

This is a limitation of UTL_FILE in Oracle which we can do with the Java Stored Procedures in Oracle.

We can read the Server Directory Files Using the Below Steps:

Please Follow the Steps below :

1. Creating a Type of Varchar2 Type.
  CREATE OR REPLACE TYPE file_list AS TABLE OF VARCHAR2(255);
2. Next we need to create a Java library file
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListVirtualDirectory" AS
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  public class ListVirtualDirectory {
    public static ARRAY getList(String path) throws SQLException, AccessControlException {
 
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
    File directory = new File(path);
 
    ArrayDescriptor arrayDescriptor = new ArrayDescriptor("FILE_LIST",conn);
 
    ARRAY listed = new ARRAY(arrayDescriptor,conn,((Object[])directory.list()));
  return listed; }}
The more advanced method overrides exception handling by suppressing information about the java.properties settings. You can do it by catching the natively thrown exception and rethrow it or ignore it. The example rethrows it.

3. Next we need to create a Wrapper Function:
CREATE OR REPLACE FUNCTION list_files(path VARCHAR2) RETURN FILE_LIST IS
LANGUAGE JAVA
NAME 'ListVirtualDirectory.getList(java.lang.String) return oracle.sql.ARRAY';
4. Grant Permissions to the Driectory:
BEGIN
  DBMS_JAVA.GRANT_PERMISSION('USER_NAME'
                             ,'SYS:java.io.FilePermission'
                             ,'C:\JavaDev\images'
                             ,'read');
  END;
5. Next, You can read the contents of the Directory as,
SELECT column_value FROM TABLE(list_files('C:\JavaDev\images'));
Which displays all the Files in the Directory Mentioned.

Thanks,
Shankar
Marked as Answer by Amit Bhandari · Sep 27 2020
Amit Bhandari
Hi rp0428,

I used the same code from the Tom's forum but still getting same error "ORA-00900: invalid SQL statement" when executing the procedure. - I have used the same line that you had mentioned but when i had pasted that here that line showing only the element = list; instead of element = list of i ;


Thanks
Amit Bhandari
Amit Bhandari
Hi Shankar,

Thanks for the reply, the code is working for me ,i would also like to know that is there any approach to move those files to another folder?


Thanks
Amit Bhandari

Edited by: 935671 on Jun 4, 2012 11:21 PM
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 3 2012
Added on Jun 4 2012
5 comments
13,216 views