Forum Stats

  • 3,751,319 Users
  • 2,250,340 Discussions
  • 7,867,382 Comments

Discussions

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

Amit Bhandari
Amit Bhandari Member Posts: 8
edited Jun 5, 2012 3:23AM in SQL & PL/SQL
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

Best Answer

  • ShankarViji
    ShankarViji Member Posts: 498
    edited Jun 5, 2012 12:20AM Accepted 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

Answers

  • Igor.M
    Igor.M Member Posts: 391
    edited Jun 4, 2012 10:51AM
    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
  • >
    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<em>;
    <pre class="jive-pre"><code class="jive-code">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
    ShankarViji Member Posts: 498
    edited Jun 5, 2012 12:20AM Accepted 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
  • Amit Bhandari
    Amit Bhandari Member Posts: 8
    edited Jun 5, 2012 2:17AM
    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
    Amit Bhandari Member Posts: 8
    edited Jun 5, 2012 3:23AM
    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
This discussion has been closed.