2 Replies Latest reply: Feb 18, 2013 1:49 PM by user1983440 RSS

    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)
          jeneesh
          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
          • 2. Re: Reg : txt Files in Server directory (UTL_FILE)
            user1983440
            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).

            http://docs.oracle.com/cd/B14117_01/appdev.101/b10796/adlob_bf.htm#1010878

            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: */ 
            
            CREATE OR REPLACE PROCEDURE displayBFILE_proc IS 
               file_loc BFILE := BFILENAME('MEDIA_DIR', 'monitor_3060.txt');
               Buffer   RAW(1024); 
               Amount   BINARY_INTEGER := 200; 
               Position INTEGER        := 1; 
            BEGIN 
               DBMS_OUTPUT.PUT_LINE('------------ BFILE DISPLAY EXAMPLE ------------');
               /* Opening the BFILE: */ 
               DBMS_LOB.OPEN (file_loc, DBMS_LOB.LOB_READONLY); 
               LOOP 
                  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); 
               EXCEPTION 
               WHEN NO_DATA_FOUND THEN 
                  DBMS_OUTPUT.PUT_LINE('End of data'); 
            END;
            /
            SHOW ERRORS;