Загрузчик разрабатывался в соответствии с требованием – “грузим все как есть в базу, построчно и оттуда уже разбираем”, поэтому для полноценной работы надо сначала загрузчиком загрузить все в базу, а потом уже разобрать то что загрузилось.
Таблицы в базе с которыми работает загрузчик: utl_load_files; - сами загрузчики utl_load_files_after_load; - инфо о состоянии загрузок по каждому файлу utl_load_files_archive; - архиваторы с которыми работает загрузчик utl_load_files_data; - данные из текстовых файлов, построчно utl_load_files_data_xml; - данные из Excel файлов (построчно, 255 колонок) utl_load_files_log; - лог работы загрузчиков utl_load_files_mail; - список почтовых адресов на которые рассылается сообщение о неудачной загрузке utl_load_files_mail_name; - таблица для связи многие ко многим загрузчика и почтового адреса utl_load_files_mes; - сообщения о состоянии и процессе загрузки загрузчиков
create table UTL_LOAD_FILES_DATA_XML ( log_id NUMBER, file_name VARCHAR2(1000), num_row NUMBER, load_date DATE, column1 VARCHAR2(4000), column2 VARCHAR2(4000), column3 VARCHAR2(4000), column4 VARCHAR2(4000), column5 VARCHAR2(4000), column6 VARCHAR2(4000), column7 VARCHAR2(4000), column8 VARCHAR2(4000), column9 VARCHAR2(4000), column10 VARCHAR2(4000), column11 VARCHAR2(4000), column12 VARCHAR2(4000), column13 VARCHAR2(4000), column14 VARCHAR2(4000), column15 VARCHAR2(4000), column16 VARCHAR2(4000), column17 VARCHAR2(4000), column18 VARCHAR2(4000), column19 VARCHAR2(4000), column20 VARCHAR2(4000), column21 VARCHAR2(4000), column22 VARCHAR2(4000), column23 VARCHAR2(4000), column24 VARCHAR2(4000), column25 VARCHAR2(4000), column26 VARCHAR2(4000), column27 VARCHAR2(4000), column28 VARCHAR2(4000), column29 VARCHAR2(4000), column30 VARCHAR2(4000), column31 VARCHAR2(4000), column32 VARCHAR2(4000), column33 VARCHAR2(4000), column34 VARCHAR2(4000), column35 VARCHAR2(4000), column36 VARCHAR2(4000), column37 VARCHAR2(4000), column38 VARCHAR2(4000), column39 VARCHAR2(4000), column40 VARCHAR2(4000), column41 VARCHAR2(4000), column42 VARCHAR2(4000), column43 VARCHAR2(4000), column44 VARCHAR2(4000), column45 VARCHAR2(4000), column46 VARCHAR2(4000), column47 VARCHAR2(4000), column48 VARCHAR2(4000), column49 VARCHAR2(4000), column50 VARCHAR2(4000), column51 VARCHAR2(4000), column52 VARCHAR2(4000), column53 VARCHAR2(4000), column54 VARCHAR2(4000), column55 VARCHAR2(4000), column56 VARCHAR2(4000), column57 VARCHAR2(4000), column58 VARCHAR2(4000), column59 VARCHAR2(4000), column60 VARCHAR2(4000), column61 VARCHAR2(4000), column62 VARCHAR2(4000), column63 VARCHAR2(4000), column64 VARCHAR2(4000), column65 VARCHAR2(4000), column66 VARCHAR2(4000), column67 VARCHAR2(4000), column68 VARCHAR2(4000), column69 VARCHAR2(4000), column70 VARCHAR2(4000), column71 VARCHAR2(4000), column72 VARCHAR2(4000), column73 VARCHAR2(4000), column74 VARCHAR2(4000), column75 VARCHAR2(4000), column76 VARCHAR2(4000), column77 VARCHAR2(4000), column78 VARCHAR2(4000), column79 VARCHAR2(4000), column80 VARCHAR2(4000), column81 VARCHAR2(4000), column82 VARCHAR2(4000), column83 VARCHAR2(4000), column84 VARCHAR2(4000), column85 VARCHAR2(4000), column86 VARCHAR2(4000), column87 VARCHAR2(4000), column88 VARCHAR2(4000), column89 VARCHAR2(4000), column90 VARCHAR2(4000), column91 VARCHAR2(4000), column92 VARCHAR2(4000), column93 VARCHAR2(4000), column94 VARCHAR2(4000), column95 VARCHAR2(4000), column96 VARCHAR2(4000), column97 VARCHAR2(4000), column98 VARCHAR2(4000), column99 VARCHAR2(4000), column100 VARCHAR2(4000), column101 VARCHAR2(4000), column102 VARCHAR2(4000), column103 VARCHAR2(4000), column104 VARCHAR2(4000), column105 VARCHAR2(4000), column106 VARCHAR2(4000), column107 VARCHAR2(4000), column108 VARCHAR2(4000), column109 VARCHAR2(4000), column110 VARCHAR2(4000), column111 VARCHAR2(4000), column112 VARCHAR2(4000), column113 VARCHAR2(4000), column114 VARCHAR2(4000), column115 VARCHAR2(4000), column116 VARCHAR2(4000), column117 VARCHAR2(4000), column118 VARCHAR2(4000), column119 VARCHAR2(4000), column120 VARCHAR2(4000), column121 VARCHAR2(4000), column122 VARCHAR2(4000), column123 VARCHAR2(4000), column124 VARCHAR2(4000), column125 VARCHAR2(4000), column126 VARCHAR2(4000), column127 VARCHAR2(4000), column128 VARCHAR2(4000), column129 VARCHAR2(4000), column130 VARCHAR2(4000), column131 VARCHAR2(4000), column132 VARCHAR2(4000), column133 VARCHAR2(4000), column134 VARCHAR2(4000), column135 VARCHAR2(4000), column136 VARCHAR2(4000), column137 VARCHAR2(4000), column138 VARCHAR2(4000), column139 VARCHAR2(4000), column140 VARCHAR2(4000), column141 VARCHAR2(4000), column142 VARCHAR2(4000), column143 VARCHAR2(4000), column144 VARCHAR2(4000), column145 VARCHAR2(4000), column146 VARCHAR2(4000), column147 VARCHAR2(4000), column148 VARCHAR2(4000), column149 VARCHAR2(4000), column150 VARCHAR2(4000), column151 VARCHAR2(4000), column152 VARCHAR2(4000), column153 VARCHAR2(4000), column154 VARCHAR2(4000), column155 VARCHAR2(4000), column156 VARCHAR2(4000), column157 VARCHAR2(4000), column158 VARCHAR2(4000), column159 VARCHAR2(4000), column160 VARCHAR2(4000), column161 VARCHAR2(4000), column162 VARCHAR2(4000), column163 VARCHAR2(4000), column164 VARCHAR2(4000), column165 VARCHAR2(4000), column166 VARCHAR2(4000), column167 VARCHAR2(4000), column168 VARCHAR2(4000), column169 VARCHAR2(4000), column170 VARCHAR2(4000), column171 VARCHAR2(4000), column172 VARCHAR2(4000), column173 VARCHAR2(4000), column174 VARCHAR2(4000), column175 VARCHAR2(4000), column176 VARCHAR2(4000), column177 VARCHAR2(4000), column178 VARCHAR2(4000), column179 VARCHAR2(4000), column180 VARCHAR2(4000), column181 VARCHAR2(4000), column182 VARCHAR2(4000), column183 VARCHAR2(4000), column184 VARCHAR2(4000), column185 VARCHAR2(4000), column186 VARCHAR2(4000), column187 VARCHAR2(4000), column188 VARCHAR2(4000), column189 VARCHAR2(4000), column190 VARCHAR2(4000), column191 VARCHAR2(4000), column192 VARCHAR2(4000), column193 VARCHAR2(4000), column194 VARCHAR2(4000), column195 VARCHAR2(4000), column196 VARCHAR2(4000), column197 VARCHAR2(4000), column198 VARCHAR2(4000), column199 VARCHAR2(4000), column200 VARCHAR2(4000), sheetname VARCHAR2(1000) )
create table UTL_LOAD_FILES ( name VARCHAR2(4000), path VARCHAR2(4000), id_archive NUMBER, name_id NUMBER, procedures VARCHAR2(200), isactive VARCHAR2(10), dir_templatedate1 VARCHAR2(10), dir_templatetextbetweend1d2 VARCHAR2(50), dir_templatedate2 VARCHAR2(10), dir_templatetextbetweend2d3 VARCHAR2(50), dir_templatedate3 VARCHAR2(10), txtdestfile VARCHAR2(500), file_templatedate1 VARCHAR2(10), file_templatetextbetweend1d2 VARCHAR2(50), file_templatedate2 VARCHAR2(10), file_templatetextbetweend2d3 VARCHAR2(50), file_templatedate3 VARCHAR2(10), isarchive VARCHAR2(10), schedlselect VARCHAR2(100), send_teschedl_hours VARCHAR2(10), send_teschedl_minutes VARCHAR2(10), selfiletype VARCHAR2(10), created_user VARCHAR2(1000), created_date DATE, what_date VARCHAR2(30), execute_user VARCHAR2(1000), execute_password VARCHAR2(100), archive_string_ind VARCHAR2(100), stinterval_hours VARCHAR2(10), stinterval_minutes VARCHAR2(10), schedlweek VARCHAR2(6), schedlmonthworkday VARCHAR2(30), schedlmonthday VARCHAR2(2), what_date_expr VARCHAR2(4000) ); create table UTL_LOAD_FILES_ARCHIVE ( id_archive NUMBER, archive_name VARCHAR2(10), archive_string VARCHAR2(100) ); create table UTL_LOAD_FILES_MAIL ( mail_id NUMBER, mail VARCHAR2(100) ); create table UTL_LOAD_FILES_MAIL_NAME ( name_id NUMBER, mail_id NUMBER ); create table UTL_LOAD_FILES_LOG ( name_id NUMBER, status VARCHAR2(10), log_id NUMBER, start_time DATE, finish_time DATE, num_rows NUMBER, message VARCHAR2(4000), user_name VARCHAR2(1000), testingfileinput VARCHAR2(1000), testingdatepath DATE, testingdatefile DATE ); create table UTL_LOAD_FILES_MES ( log_id NUMBER, mes_time DATE, message VARCHAR2(4000), mes_id NUMBER ); create table UTL_LOAD_FILES_DATA ( log_id NUMBER, file_name VARCHAR2(1000), num_row NUMBER, load_date DATE, file_data CLOB ); create table UTL_LOAD_FILES_AFTER_LOAD ( loadtime DATE, loadername VARCHAR2(1000), path VARCHAR2(4000), pathtodump VARCHAR2(4000), procedure VARCHAR2(1000), num_rows NUMBER, log_id NUMBER, filename VARCHAR2(1000), guid VARCHAR2(1000), real_time DATE, file_err_mes VARCHAR2(1000), stored_proc_mes VARCHAR2(4000) );
create sequence UTL_LOAD_FILES_SEC minvalue 0 maxvalue 9999999999999999999999999999 start with 701 increment by 1 cache 20; create sequence UTL_LOAD_FILES_MAIL_SEC minvalue 0 maxvalue 9999999999999999999999999999 start with 228 increment by 1 cache 20; create sequence UTL_LOAD_FILES_LOG_SEC minvalue 0 maxvalue 9999999999999999999999999999 start with 5024 increment by 1 cache 20 cycle; create sequence UTL_LOAD_FILES_MES_SEC minvalue 0 maxvalue 9999999999999999999999999999 start with 8922 increment by 1 cache 20 cycle;
<appSettings> … <add key="conOracle" value="Data Source=your_db;Persist Security Info=True;User ID=your_user;Password=your_password"/> … </appSettings>
<appSettings> … <add key="ActiveDirectoryServer" value="your.server.where.ru"/> … </appSettings>
<appSettings> … <add key="PathToWorkProcess" value="с:\work\FilesToOracleUploader.exe"/> … </appSettings>
<appSettings> … <add key="PathToSQL" value="C:\FilesToOracleWeb\SQLCommands\SQLQueries.xml"/> … </appSettings>
<appSettings> … <add key="conOracle" value="Data Source=your_db;Persist Security Info=True;User ID=your_user;Password=your_password"/> … </appSettings>
<appSettings> … <add key="PathToArchivators" value="C:\FilesToOracleWeb\Archivators"/> … </appSettings>
<appSettings> … <add key="PathToDump" value="C:\Temp\FilesToOracleWeb\Dump"/> … </appSettings>
<appSettings> … <add key="PathToLog" value="C:\Temp\FilesToOracleWeb\Log"/> … </appSettings>
<appSettings> … <add key="MailServer" value="your_mail_server"/> … </appSettings>
<appSettings> … <add key="SupportUser" value="your_user"/> … </appSettings>
<appSettings> … <add key="SupportUserPassword" value="your_password"/> … </appSettings>
<appSettings> … <add key="SupportUserMail" value="your_mail@mail.server"/> … </appSettings>
<appSettings> … <add key="WorkDir" value="C:\Temp\FilesToOracleWeb\Work"/> … </appSettings>