Loading data from a file into the database quickly

Below is a small procedure I created to load the contents of a file quickly into a database.



create or replace directory ext as 'C:\Nirmala\OracleLearning\ext';

create or replace procedure search_file(l_file_name varchar2
, search_string varchar2 default null ) as
l_table_count number;
begin

select count(1)
into l_table_count
from all_tables
where table_name = upper(trim(replace(l_file_name,'.','')));
if(l_table_count=1) then
execute immediate ' drop table '||trim(replace(l_file_name,'.',''));
end if;

execute immediate ' create table '||trim(replace(l_file_name,'.',''))||' (text clob ) organization external (type oracle_loader default directory ext location ('''||l_file_name||'''))';

execute immediate ' create table '||trim(replace(l_file_name,'.',''))||'_perm as select text , rownum line_number from '||trim(replace(l_file_name,'.',''));

end ;