Ola a todos,
Depois de um tempo testando, cheguei a uma solução por completo. Qualquer duvida é só postar...
No meu caso, com todo o tratamento e validações necessarias, o procedimento com external tables rendeu 35% mais em comparação com o UTL_file nas 125.000 linhas tratadas.
O procedimento final:
Selecionar tudo
DECLARE
----------------------------------------------------------------------------
/* É necessario as GRANTS para CREATE ANY DIRECTORY, DROP ANY DIRECTORY,
** CREATE TABLE e DROP TABLE */
FOLDER VARCHAR2(90) := '/tmp';
READ_FILE VARCHAR2(90) := 'teste_external.txt';
----------------------------------------------------------------------------
DIR VARCHAR2(90) := 'CREATE OR REPLACE DIRECTORY external_dir AS '''||FOLDER||'''' ;
DROP_TABELA VARCHAR2(90) := 'DROP TABLE TESTE_EXTERNAL';
SQL_TABELA VARCHAR2(4500) := 'CREATE TABLE TESTE_EXTERNAL (
CD_IDENTIFICACAO VARCHAR2(10 BYTE),
CONTEUDO_CAMPO_1 VARCHAR2(10 BYTE),
CONTEUDO_CAMPO_2 VARCHAR2(10 BYTE),
CONTEUDO_CAMPO_3 VARCHAR2(10 BYTE),
CONTEUDO_CAMPO_4 VARCHAR2(10 BYTE),
CONTEUDO_CAMPO_5 VARCHAR2(10 BYTE),
CONTEUDO_CAMPO_6 VARCHAR2(10 BYTE),
CONTEUDO_CAMPO_7 VARCHAR2(10 BYTE)
) organization external (
type oracle_loader
default directory external_dir
access parameters (
records delimited by newline
fields (
CD_IDENTIFICACAO POSITION(01:10) CHAR(10),
CONTEUDO_CAMPO_1 POSITION(11:20) CHAR(10),
CONTEUDO_CAMPO_2 POSITION(21:30) CHAR(10),
CONTEUDO_CAMPO_3 POSITION(31:40) CHAR(10),
CONTEUDO_CAMPO_4 POSITION(41:50) CHAR(10),
CONTEUDO_CAMPO_5 POSITION(51:60) CHAR(10),
CONTEUDO_CAMPO_6 POSITION(61:70) CHAR(10),
CONTEUDO_CAMPO_7 POSITION(71:80) CHAR(10)
))
location ('''||READ_FILE||'''))';
----------------------------------------------------------------------------
TYPE CUR_TYPE IS REF CURSOR;
C_TESTE_EXTERNAL CUR_TYPE;
CD_IDENTIFICACAO VARCHAR2(10 BYTE);
CONTEUDO_CAMPO_1 VARCHAR2(10 BYTE);
CONTEUDO_CAMPO_2 VARCHAR2(10 BYTE);
CONTEUDO_CAMPO_3 VARCHAR2(10 BYTE);
CONTEUDO_CAMPO_4 VARCHAR2(10 BYTE);
CONTEUDO_CAMPO_5 VARCHAR2(10 BYTE);
CONTEUDO_CAMPO_6 VARCHAR2(10 BYTE);
CONTEUDO_CAMPO_7 VARCHAR2(10 BYTE);
SQL_STR VARCHAR2(90);
----------------------------------------------------------------------------
CONTADOR NUMBER := 0;
C_NOTFOUND BOOLEAN := FALSE;
----------------------------------------------------------------------------
BEGIN
----------------------------------------------------------------------------
/* Execução dos comandos necessarios para a criação da tabela e do diretório */
EXECUTE IMMEDIATE(DIR);
DBMS_OUTPUT.PUT_LINE('Criou diretório');
EXECUTE IMMEDIATE(SQL_TABELA);
DBMS_OUTPUT.PUT_LINE('Criou tabela');
----------------------------------------------------------------------------
/* Definindo os parametros necessarios para as consultas nas tabelas geradas */
SQL_STR := 'SELECT * FROM TESTE_EXTERNAL';
OPEN C_TESTE_EXTERNAL FOR SQL_STR;
LOOP
-------------------------------------------------------------------------
FETCH C_TESTE_EXTERNAL INTO CD_IDENTIFICACAO,
CONTEUDO_CAMPO_1,
CONTEUDO_CAMPO_2,
CONTEUDO_CAMPO_3,
CONTEUDO_CAMPO_4,
CONTEUDO_CAMPO_5,
CONTEUDO_CAMPO_6,
CONTEUDO_CAMPO_7;
EXIT WHEN C_TESTE_EXTERNAL%NOTFOUND;
-------------------------------------------------------------------------
/* Tratamento do arquivo texto como for necessario. */
CONTADOR := CONTADOR + 1;
-------------------------------------------------------------------------
END LOOP;
CLOSE C_TESTE_EXTERNAL;
DBMS_OUTPUT.PUT_LINE('Terminou');
EXECUTE IMMEDIATE(DROP_TABELA);
DBMS_OUTPUT.PUT_LINE('Deletou a tabela');
DBMS_OUTPUT.PUT_LINE(CONTADOR);
----------------------------------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Erro.');
END;
Exemplo do arquiv texto (teste_external.txt):
Selecionar tudo
0000000001AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG
0000000002AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG
0000000003AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDDEEEEEEEEEEFFFFFFFFFFGGGGGGGGGG
Obrigado a todos pela ajuda...