Load data from file OS (txt, csv) using UTL_FILE

Написал процедуру LOADFROMFILE используя функционал пакета UTL_FILE, позволялющий быстро и практично загружать данные из текстового файла ОС (txt, csv) с разделителями (delimeters) в указанную таблицу. Процедура имеет несколько важных проверок, которые сократят ваше время, например, она сама считает количество столбцов в файле и сравнивает с количеством столбцов в таблице; конвертирует записи в тип DATE, где это нужно; возвращает сообщения об ошибках связанных с неверностью входных параметров, отсутствия файла и т.п.

 

CREATE OR REPLACE PROCEDURE LOADFROMFILE(dir_name VARCHAR2, file_name VARCHAR2, owner_name VARCHAR2, tab_name VARCHAR2, delim CHAR, exception_msg out VARCHAR2)
AS
/*
dir_name - Oracle директория расположения файлов
file_name - имя текстового файла
owner_name - имя владельца таблицы БД Oracle
tab_name - имя таблицы БД Oracle
delim - разделитель (delimeter) данных в файле (например, ';')
exception_msg - это возвращаемое сообщение о состоянии выполнения процедуры, позволяющзая понять, где мы допустили ошибку
*/
V_LINE CLOB;
F1 UTL_FILE.FILE_TYPE;
i INT;
colmax INT;
DATA_TYPE VARCHAR2(50);
TYPE arr_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
arr arr_table;
STRCOM CLOB;
str CLOB;
tabnamechek INT;
cnt INT DEFAULT 0;
csvfields INT;

BEGIN

    -- получаем количество столбцов в таблице
    select nvl((SELECT count(*) 
    FROM all_tables t, all_tab_columns c 
    WHERE t.owner=upper(owner_name) AND T.TABLE_NAME = C.TABLE_NAME AND t.TABLE_NAME = upper(tab_name) 
    GROUP BY t.TABLE_NAME),0) INTO colmax from dual;

  IF colmax > 0 THEN
    -- открываем файл для чтения
    F1 := UTL_FILE.FOPEN(dir_name, file_name, 'R');

    -- проверяем открылся ли файл
    IF UTL_FILE.IS_OPEN(F1) THEN
       exception_msg:='File '|| file_name ||' is Open';
    ELSE
       exception_msg:='File '|| file_name ||' is not Open';
    END IF;
    -- считываем построчно записи из файла
    LOOP
        cnt:=cnt+1;

       BEGIN
            UTL_FILE.GET_LINE(F1,V_LINE);
            -- получаем количество столбцов в одной строке файла
            csvfields:=regexp_count(regexp_replace(V_LINE,'.*?'),''||delim||'') + 1;
            -- проверяем совпадает ли количество столбцов таблицы и файле
            IF csvfields = colmax THEN
                -- получаем запись для каждого столбца строки
               FOR i IN 1..csvfields
               LOOP
                    -- заносим записи N-строки в массив
                    arr(i):=trim(REGEXP_SUBSTR(V_LINE, '[^'||delim||']+', 1, i));
                    
                    SELECT C.DATA_TYPE INTO DATA_TYPE
                    FROM all_tables t, all_tab_columns c 
                    WHERE t.owner=upper(owner_name) AND T.TABLE_NAME = C.TABLE_NAME AND t.TABLE_NAME = upper(tab_name)
                    AND C.COLUMN_ID = i;
                        
                    -- составляем часть кода динамического sql
                    -- если тип столбца DATE, то конвертируем полученную запись в дату
                    IF DATA_TYPE = 'DATE' THEN
                        str:=str||'to_date('''||arr(i)||''',''dd.mm.yyyy hh24:mi:ss'')'||',';
                    ELSE 
                        str:=str||''''||arr(i)||''''||',';
                    END IF;

                    -- вставляем полученную строку в таблицу
                    IF i = csvfields THEN

                            BEGIN
                                STRCOM:='INSERT INTO '||owner_name||'.'||tab_name||' values('||regexp_replace(str,',$','')||')';

                                EXECUTE IMMEDIATE STRCOM;
                                COMMIT;

                                str:=NULL;
                                arr.DELETE;
                            END;
                                              
                    END IF;
                    
               END LOOP;
               
            ELSE
                exception_msg:='Wrong number of columns '||file_name||', because csv('||csvfields||') <> tab('||colmax||').';
                EXIT;
                UTL_FILE.FCLOSE(F1);
            END IF;
       EXCEPTION WHEN No_Data_Found THEN EXIT;
       END;
                    
    END LOOP;
    -- закрываем файл
    UTL_FILE.FCLOSE(F1);
   exception_msg:=exception_msg || chr(13) || 'Data load!'; 
 ELSE
   exception_msg:='Table name OR owner name is wrong!';
 END IF;     

    EXCEPTION 
    WHEN utl_file.invalid_operation THEN exception_msg:='File '|| file_name ||' is Not Open.';
    WHEN UTL_FILE.INVALID_PATH THEN exception_msg:='Invalid file '|| file_name ||' path.';
    WHEN UTL_FILE.READ_ERROR THEN exception_msg:='Read error file '|| file_name ||'.';
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN exception_msg:='File ' || file_name || ' handle was Invalid.';
    WHEN UTL_FILE.INTERNAL_ERROR THEN exception_msg:='An unspecified error in PL/SQL.';
    WHEN OTHERS THEN exception_msg:=('ERROR: '||DBMS_UTILITY.FORMAT_ERROR_STACK||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    
END;

 

Вызов процедуры свыполняется следующим образом:

declare
    outrep varchar2(4000);
begin
    LOADFROMFILE('DBSTAT','apex_stat_sga_16052014_14.csv','SDUSER','STAT_SGA',';',outrep);
    dbms_output.put_line(outrep);
end;

--dbms_output--
File apex_stat_sga_16052014_14.csv is Open.
Data load!