How To Write Out Multiple Clob Fields > 32k To A File in 4K chunks? (Doc ID 358781.1)

DECLARE
  l_output UTL_FILE.file_type;
  l_amt    NUMBER DEFAULT 4000;
  l_offset NUMBER DEFAULT 1;
  position INTEGER := 1;
  contador INTEGER;
  l_length NUMBER :=0 ;
  x        VARCHAR2(32000);
  v_Clob   VARCHAR2(4000);
  t_Clob CLOB;
  CURSOR c1
  IS
    SELECT clob_field
    FROM clob_table;
  -- New Variable to monitor the 32K limit
  v_32k_monitor NUMBER := 0;
BEGIN
  contador:=0;
  OPEN C1;
  l_output := UTL_FILE.fopen ('MY_DIR2', 'outputfile2.txt', 'w', 32760);
  LOOP
    FETCH C1
    INTO t_clob;
    EXIT WHEN C1%NOTFOUND;
    l_length := DBMS_LOB.GETLENGTH(T_CLOB);
    DBMS_OUTPUT.PUT_LINE(l_length);
    position := 1;
    l_offset := 1;
    l_amt    := 4000;
    --Reset the v_32k_monitor value for each row.
    v_32k_monitor  := 0;
    WHILE (l_offset < l_length)
    LOOP
      IF (l_amt > (l_length - l_offset)) THEN
        l_amt  := l_length  - l_offset + 1;
      END IF;
      dbms_output.put_line ('l_amt: ' || l_amt);
      dbms_output.put_line ('l_offset: ' || l_offset);
      dbms_lob.read (t_clob, l_amt, l_offset, x);
      utl_file.putf(l_output, x);
      l_offset := l_offset + l_amt;
      position := position + 4000;
      x        := NULL;
      --Since data is written in 4K chunks v_32k_monitor increase by 4K
      v_32k_monitor := v_32k_monitor + 4000;
      --There has to be a Since data is written in 4K chunks v_32k_monitor
      -- increase by 4K
      IF (v_32k_monitor >= 32000) THEN
        -- A new line has to be added every 32k
        DBMS_OUTPUT.PUT_LINE('A new line added');
        utl_file.new_line(l_output);
        v_32k_monitor := 0;
      END IF;
    END LOOP;
    utl_file.new_line(l_output);
    utl_file.putf (l_output, '\n');
  END LOOP;
  UTL_FILE.fclose (l_output);
END;
/
**********************************************************************
DECLARE
  l_output UTL_FILE.file_type;
  l_amt    NUMBER DEFAULT 4000;
  l_offset NUMBER DEFAULT 1;
  position INTEGER := 1;
  contador INTEGER;
  l_length NUMBER :=0 ;
  x        VARCHAR2(32000);
  v_Clob   VARCHAR2(4000);
  t_Clob CLOB;
  CURSOR c1
  IS
    SELECT clob_field
    FROM clob_table;
BEGIN
  contador:=0;
  OPEN C1;
  l_output := UTL_FILE.fopen ('MY_DIR2', 'outputfile102.txt', 'wb', 32760);
  LOOP
    FETCH C1
    INTO t_clob;
    EXIT WHEN C1%NOTFOUND;
    l_length := DBMS_LOB.GETLENGTH(T_CLOB);
    DBMS_OUTPUT.PUT_LINE(l_length);
    position       := 1;
    l_offset       := 1;
    l_amt          := 4000;
    WHILE (l_offset < l_length)
    LOOP
      IF (l_amt > (l_length - l_offset)) THEN
        l_amt  := l_length  - l_offset + 1;
      END IF;
      dbms_output.put_line ('l_amt: ' || l_amt);
      dbms_output.put_line ('l_offset: ' || l_offset);
      dbms_lob.read (t_clob, l_amt, l_offset, x);
      UTL_FILE.PUT_raw(l_output, utl_raw.cast_to_raw(x), TRUE);
      l_offset := l_offset + l_amt;
      position := position + 4000;
      x        := NULL;
    END LOOP;
    UTL_FILE.PUT_raw(l_output, utl_raw.cast_to_raw(CHR(10)), TRUE);
  END LOOP;
  UTL_FILE.fclose (l_output);
END;

/

PLSQL Script to load the data from Flat file into Oracle Table dynamically

1. create below log table to capture the error records details .

CREATE TABLE XX_BAD_DATA_LOG_TBL
(
  FILE_NAME                      VARCHAR2(250 BYTE),
  CREATION_DATE            DATE,
  CREATED_BY                   NUMBER,
  ERROR_MESSAGE           VARCHAR2(2000 BYTE),
  FILE_DATA                        VARCHAR2(2000 BYTE)
);

2. Create the following function.

CREATE OR REPLACE FUNCTION apps.xx_upload_data_to_tbl (
   p_table                              IN   VARCHAR2
 , p_dir                                IN   VARCHAR2
 , p_filename                           IN   VARCHAR2
 , p_delimiter                          IN   VARCHAR2 DEFAULT ','
)
   RETURN NUMBER
IS
   l_utl_file                                   UTL_FILE.file_type;
   l_cursor                                     INTEGER DEFAULT DBMS_SQL.open_cursor;
   l_buffer                                     VARCHAR2 (4000);
   l_data                                   VARCHAR2 (4000);
   l_status                                     INTEGER;
   l_column_cnt                                 NUMBER DEFAULT 0;
   l_count                                      NUMBER DEFAULT 0;
   l_sep                                        CHAR (1) DEFAULT NULL;
   l_error_message                              VARCHAR2 (4000);
   l_column_names                               VARCHAR2 (4000);
   l_num                                        NUMBER;
BEGIN
   l_num                                := 10;
   l_utl_file                           := UTL_FILE.fopen (p_dir
                                                         , p_filename
                                                         , 'r'
                                                          );
   l_num                                := 20;
   l_buffer                             := 'INSERT INTO ' || p_table || ' VALUES ( ';
   l_num                                := 30;

   FOR z IN (SELECT column_name
               FROM dba_tab_columns
              WHERE table_name = p_table)
   LOOP
      IF l_column_names IS NULL
      THEN
         l_column_names                       := z.column_name;
      ELSE
         l_column_names                       := l_column_names || ',' || z.column_name;
      END IF;
   END LOOP;

   l_num                                := 40;
   DBMS_OUTPUT.put_line (l_num || ' ' || l_column_names);

   BEGIN
      l_column_cnt                         := LENGTH (l_column_names) - LENGTH (REPLACE (l_column_names
                                                                                       , ','
                                                                                       , ''
                                                                                        )) + 1;
      l_num                                := 45;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (l_num || ' Error ' || SQLERRM);
   END;

   DBMS_OUTPUT.put_line (l_column_cnt);
   l_num                                := 50;

   FOR i IN 1 .. l_column_cnt
   LOOP
      l_buffer                             := l_buffer || l_sep || ':b' || i;
      l_sep                                := ',';
   END LOOP;

   l_num                                := 60;
   l_buffer                             := l_buffer || ')';
   l_num                                := 70;
   DBMS_SQL.parse (l_cursor
                 , l_buffer
                 , DBMS_SQL.native
                  );
   l_num                                := 80;

   LOOP
      BEGIN
         UTL_FILE.get_line (l_utl_file, l_data);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

      l_num                                := 90;
      l_buffer                             := l_data || p_delimiter;
      l_num                                := 100;

      FOR i IN 1 .. l_column_cnt
      LOOP
         DBMS_SQL.bind_variable (l_cursor
                               , ':b' || i
                               , SUBSTR (l_buffer
                                       , 1
                                       , INSTR (l_buffer, p_delimiter) - 1
                                        )
                                );
         l_buffer                             := SUBSTR (l_buffer, INSTR (l_buffer, p_delimiter) + 1);
         l_num                                := 110;
      END LOOP;

      BEGIN
         l_status                             := DBMS_SQL.EXECUTE (l_cursor);
         l_count                              := l_count + 1;
         l_num                                := 120;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_message                      := SQLERRM;

            INSERT INTO xx_bad_data_log_tbl
                 VALUES (p_filename
                       , SYSDATE
                       , fnd_global.user_id
                       , l_error_message
                       , l_data
                        );
      END;
   END LOOP;

   DBMS_SQL.close_cursor (l_cursor);
   UTL_FILE.fclose (l_utl_file);
   l_num                                := 130;
   COMMIT;
   DBMS_OUTPUT.put_line (l_num || ' ' || l_count);
   RETURN l_count;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (l_num || ' Unexpected Error ' || SUBSTR(SQLERRM,1,250));
      RETURN 0;
END xx_upload_data_to_tbl;
/

How to Copy Any File using UTL_FILE

While UTL_FILE.FCOPY can be used to copy valid text files, it does not copy binary files or invalid text files.  An example of an invalid text file would be one that contains a character sequence that cannot be generated by the characters on your keyboard (i.e. the sequence of two CR's in a row on a Windows platform rather than CRLF, CRLF) and hence may be considered a binary file.

NOTE: As UTL_FILE first supported the reading and writing of binary files in 10gR1, this note can only be used with Oracle Database versions 10gR1 and later.

Solution

1. Create two directory objects for the IN and OUT file locations.

CREATE OR REPLACE DIRECTORY in_file_loc AS 'C:\temp\in';
CREATE OR REPLACE DIRECTORY out_file_loc AS 'C:\temp\out';

NOTE: You must have the CREATE ANY DIRECTORY privilege to execute these statements.

2. Create the procedure CopyFile.

-- Procedure to COPY a Text or Binary file

CREATE OR REPLACE PROCEDURE copyfile (
   in_filename                IN             VARCHAR2
 , out_filename               IN             VARCHAR2
)
IS
   in_file                                           UTL_FILE.file_type;
   out_file                                          UTL_FILE.file_type;
   buffer_size                              CONSTANT INTEGER := 32767;   -- Max Buffer Size = 32767
   buffer                                            RAW (32767);
   buffer_length                                     INTEGER;
BEGIN
   -- Open a handle to the location where you are going to read the Text or Binary file from
   -- NOTE: The 'rb' parameter means "read in byte mode" and is only available
   --       in the UTL_FILE package with Oracle 10g or later
   in_file                                                  :=
                                                              UTL_FILE.fopen ('IN_FILE_LOC', in_filename, 'rb', buffer_size);
   -- Open a handle to the location where you are going to write the Text or Binary file to
   -- NOTE: The 'wb' parameter means "write in byte mode" and is only available
   --       in the UTL_FILE package with Oracle 10g or later
   out_file                                                 :=
                                                            UTL_FILE.fopen ('OUT_FILE_LOC', out_filename, 'wb', buffer_size);
   -- Attempt to read the first chunk of the in_file
   UTL_FILE.get_raw (in_file, buffer, buffer_size);
   -- Determine the size of the first chunk read
   buffer_length                                            := UTL_RAW.LENGTH (buffer);

   -- Only write the chunk to the out_file if data exists
   WHILE buffer_length > 0
   LOOP
      -- Write one chunk of data
      UTL_FILE.put_raw (out_file, buffer, TRUE);

      -- Read the next chunk of data
      IF buffer_length = buffer_size
      THEN
         -- Buffer was full on last read, read another chunk
         UTL_FILE.get_raw (in_file, buffer, buffer_size);
         -- Determine the size of the current chunk
         buffer_length                                            := UTL_RAW.LENGTH (buffer);
      ELSE
         buffer_length                                            := 0;
      END IF;
   END LOOP;

   -- Close the file handles
   UTL_FILE.fclose (in_file);
   UTL_FILE.fclose (out_file);
EXCEPTION
   -- Raised when the size of the file is a multiple of the buffer_size
   WHEN NO_DATA_FOUND
   THEN
      -- Close the file handles
      UTL_FILE.fclose (in_file);
      UTL_FILE.fclose (out_file);
END;
/

3. Test procedure CopyFile with both Text and Binary files.

-- Test a TEXT file
EXEC CopyFile('original_textfilename.txt', 'new_textfilename.txt')

-- Test a BINARY File
EXEC CopyFile('original_binaryfilename.jpg', 'new_binaryfilename.jpg') 

Check the IN_FILE_LOC and OUT_FILE_LOC and verify that the files were successfully copied.  Compare the actual size in bytes to make sure they are an exact copy. 

Rate this document 

Top of Form

Bottom of Form

Script To Check the File On Database Server(Unix) Using UTL_FILE

Below Script is used to verify the file exists on the Server using PLSQL UTL_FILE functionality.

DECLARE
   l_fexists         BOOLEAN;
   l_file_length   NUMBER;
   l_block_size    NUMBER;
   l_file_name       VARCHAR2 (2000); -- Name if the file that needs to check on server
BEGIN
   UTL_FILE.fgetattr ('/usr/tmp'
                    , l_file_name
                    , l_fexists
                    , l_file_length
                    , l_block_size
                     );
   IF l_fexists
   THEN
      DBMS_OUTPUT.put_line ('The File exists');
    ELSE
      DBMS_OUTPUT.put_line ('The File dosent exists');
   END IF;
END;