Oracle External Tables

Here we discuss how to use Oracle external tables.

Read the Oracle Documentation of External Tables here

A sample practice example is given below:

Create a table name testetl with the fields

empid varchar2
empname varchar2
phone varchar2

Now create a directory pointing a folder . Example d:\oracle\loadftp

create directory testload as 'D:\oracle\loadftp\';

Create the external table sample_ext as follows:


CREATE TABLE SAMPLE_EXT (
empid varchar(10),
empname varchar(30),
phone varchar(20))

ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TXTLOAD
ACCESS PARAMETERS
(
records delimited by newline
badfile TXTLOAD:'SAMPLE_EXT%a_%p.bad'
logfile TXTLOAD:'SAMPLE_EXT%a_%p.log'
SKIP 1
fields terminated by ',' optionally enclosed by '"' LRTRIM
MISSING FIELD VALUES ARE NULL
)

LOCATION ('testload.txt') )
--PARALLEL 4
REJECT LIMIT 1000;

Now we need to create a function which will take a filename to loaded from the directory testload and use the external table to load the data in destination table testetl.

create or replace FUNCTION SAMPLE_ETL_FNC( P_FILE_PATH VARCHAR2 ) RETURN NUMBER AS
l_filename varchar2(100);
l_rowcount number;
l_sqlerrm varchar(2000);
l_filesize number;
BEGIN
DBMS_OUTPUT.PUT_LINE(to_char(sysdate, 'YYYY-MON-DD HH:MI:SS') ' - BEGIN ETL PROCEDURE - Path :' p_file_path);
--Flexible to allow for file name or file path i
f instr(p_file_path, '/') > 0 then
select substr(p_file_path, instr(p_file_path,'/', -1)+1, length(p_file_path)-instr(p_file_path,'/', -1)) INTO l_filename from dual;
else
l_filename := p_file_path; end if;
--Get file size if required
--l_filesize := flength ('DAT_DIR',l_filename);
DBMS_OUTPUT.PUT_LINE('Filename: ' l_filename);
BEGIN --Alter the Log and Bad file names for debugging
--Can modify the bad and log file names if required
DBMS_OUTPUT.PUT_LINE('ALTER EXTERNAL TABLE DATA SOURCE');
execute immediate 'alter table SAMPLE_EXT location('''l_filename''')';
--Prepare Insert SQL
DBMS_OUTPUT.PUT_LINE('BEGIN INSERT FROM EXTERNAL TO DATA TABLE'); execute immediate 'Insert into testetl select * from sample_ext A';
l_rowcount := SQL%rowcount;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') ': INSERT SUCCESSFUL... IN SERTED ' l_rowcount ' ROWS'); COMMIT;
RETURN 0;
exception
when others then
DBMS_OUTPUT.PUT_LINE('INSERT FAILED');
l_sqlerrm:=sqlerrm;
DBMS_OUTPUT.PUT_LINE(l_sqlerrm);
ROLLBACK;
commit;
RETURN 1;
END;
exception
when others then
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN 1;
END;

The testload.txt file looks like

empid,empname,phone
12345,sathi,9900593881
12346,sathi,9900593881
12347,sathi,9900593882
12348,sathi,9900593883
12349,sathi,9900593885


Call the function is the following manner from a sql file runetl.sql

declare l_return number;
begin l_return := SAMPLE_ETL_FNC('testload.txt');
dbms_output.put_line('RETURN STATUS: ' l_return);
end;


run the file as @runetl from sql prompt.