5 Replies Latest reply on Jun 5, 2012 7:23 AM by Amit Bhandari

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

    Amit Bhandari
      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
        • 1. Re: How to get the all file names from the folder and move it to another folder
          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
          • 2. Re: How to get the all file names from the folder and move it to another folder
            >
            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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: How to get the all file names from the folder and move it to another folder
              ShankarViji
              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
              • 4. Re: How to get the all file names from the folder and move it to another folder
                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
                • 5. Re: How to get the all file names from the folder and move it to another folder
                  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