2 Replies Latest reply on Feb 18, 2013 7:49 PM by user1983440

    Reg : txt Files in Server directory (UTL_FILE)

    ranit B
      Hi Experts,

      Suppose, I've placed 5 +.txt+ files (procedure/table creation scripts) in a db server directory.
      Is there any way I can loop through each of the files?

      I need to pull the content of each file into oracle table using a procedure. So, I'm trying dynamically trying to use External Tables with Execute Immediate inside my proc. Will package UTL_FILE help in this?

      Please give some suggestions.
      Let me know if you have any concerns.

      Ranit B.
        • 1. Re: Reg : txt Files in Server directory (UTL_FILE)
          Read at AskTom and This too

          Between, you could also think of having standard File names - And use a shell script with cronjob or with some standard scheduling utilities to have the files renamed (Hope your db server is UNIX family one)

          Edited by: jeneesh on Feb 18, 2013 3:29 PM
          1 person found this helpful
          • 2. Re: Reg : txt Files in Server directory (UTL_FILE)
            As an alternative solution, have you considered using external LOB's? Using this approach could save significant overhead vs. the external tables solution because it doesn't require any schema objects for accessing the txt files (if you use an SQL script instead of a stored procedure).


            Below is an excerpt from the link the Oracle docs above; this could be used as a starting point. (Substitute your directory name/file name for the BFILENAME arguments.)
            /* This file is installed in the following path when you install */
            /* the database: $ORACLE_HOME/rdbms/demo/lobs/plsql/fdisplay.sql */
            /* Displaying BFILE data.  */
            /* Procedure displayBFILE_proc is not part of DBMS_LOB package: */ 
               file_loc BFILE := BFILENAME('MEDIA_DIR', 'monitor_3060.txt');
               Buffer   RAW(1024); 
               Amount   BINARY_INTEGER := 200; 
               Position INTEGER        := 1; 
               DBMS_OUTPUT.PUT_LINE('------------ BFILE DISPLAY EXAMPLE ------------');
               /* Opening the BFILE: */ 
               DBMS_LOB.OPEN (file_loc, DBMS_LOB.LOB_READONLY); 
                  DBMS_LOB.READ (file_loc, Amount, Position, Buffer); 
                  /* Display the buffer contents: */ 
                  DBMS_OUTPUT.PUT_LINE(substr(utl_raw.cast_to_varchar2(Buffer), 1, 250));
                  Position := Position + Amount; 
               END LOOP; 
               /* Closing the BFILE: */ 
               DBMS_LOB.CLOSE (file_loc); 
               WHEN NO_DATA_FOUND THEN 
                  DBMS_OUTPUT.PUT_LINE('End of data'); 
            SHOW ERRORS;