Step # 1:
Create Database directory where you will put your CSV
file in Server.
CREATE OR REPLACE DIRECTORY MY_DIR AS '/U03/.......';
-------------------------------------------------------------------------------------------
Step # 2;
Create 3 Tables According to your CSV Columns:
1. CSV Data Table
2. Bad Log/Error Table
3. History Table
CREATE TABLE CSV_AR_INV_TABLE
(
LEGAL_ENTITY_NAME VARCHAR2(100),
INVOICE_TYPE VARCHAR2(100),
CUST_TRX_TYPE_NAME VARCHAR2(100),
CURRENCY_CODE VARCHAR2(100),
TRX_DATE DATE,
CONVERSION_RATE NUMBER,
PAYMENT_TERM VARCHAR2(100),
SALES_PERSON_NAME VARCHAR2(100),
BILL_TO_CUSTOMER_NUMBER VARCHAR2(100),
BILL_TO_CUSTOMER_NAME VARCHAR2(100),
BILL_TO_CUSTOMER_SITE VARCHAR2(100),
SHIP_TO_CUSTOMER_NUMBER VARCHAR2(100),
SHIP_TO_CUSTOMER_NAME VARCHAR2(100),
SHIP_TO_CUSTOMER_SITE VARCHAR2(100),
LINE_NO NUMBER,
LINE_TYPE VARCHAR2(100),
LINE_DESCRIPTION VARCHAR2(100),
QUANTITY NUMBER,
UNIT_SELLING_PRICE NUMBER,
AMOUNT NUMBER,
CRM_INVOICE_NO VARCHAR2(100),
GL_CODE VARCHAR2(100),
STATUS VARCHAR2(100)
);
CREATE TABLE CSV_AR_INV_LOADER_BADLOG(
TABLE_NAME VARCHAR2(200),
LINE_NUMBER NUMBER,
ERRM VARCHAR2(4000),
DATA VARCHAR2(4000),
ERROR_DATE TIMESTAMP(6)
);
CREATE TABLE CSV_AR_INV_LOADER_HIST
(
FILENAME VARCHAR2(200),
TABLE_NAME VARCHAR2(200),
NUM_OF_REC NUMBER,
IMPORT_DATE DATE
);
------------------------------------------------------------------------
Step # 3
Create Function to Read and Load CSV Data From Server File Location.
( P_TABLE IN VARCHAR2,
P_DIR IN VARCHAR2 DEFAULT 'MY_DIR',
P_FILENAME IN VARCHAR2,
P_IGNORE_HEADERLINES IN INTEGER DEFAULT 1,
P_DELIMITER IN VARCHAR2 DEFAULT ',',
P_OPTIONAL_ENCLOSED IN VARCHAR2 DEFAULT '"')
RETURN VARCHAR2 IS
/***************************************************************************
-- PROCEDURE LOAD_CSV
-- PURPOSE: This Procedure read the data from a CSV file.
-- And load it into the target oracle table.
-- Finally it renames the source file with date.
-- P_FILENAME -- The name of the flat file(a text file)
-- P_DIRECTORY -- Name of the directory where the file is been placed.
-- Note: The grant has to be given for the user to the directory
-- before executing the function
-- P_IGNORE_HEADERLINES: -- Pass the value as '1' to ignore importing headers.
-- P_DELIMITER -- By default the delimiter is used as ','
-- As we are using CSV file to load the data into oracle
-- P_OPTIONAL_ENCLOSED -- By default the optionally enclosed is used as '"'
-- As we are using CSV file to load the data into oracle
-- AUTHOR: M WAQAS KHAN
-- Date : 24 FEB, 2017
**************************************************************************/
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_lastLine varchar2(4000);
l_cnames varchar2(4000);
l_bindvars varchar2(4000);
l_status integer;
l_cnt number default 0;
l_rowCount number default 0;
--l_sep char(1) default NULL;
L_ERRMSG varchar2(4000);
V_EOF BOOLEAN := false;
L_RES VARCHAR2(100) := 'SUCCESS';
l_error exception;
begin
DELETE FROM CSV_AR_INV_TABLE;
commit;
delete from CSV_AR_INV_LOADER_BADLOG;
commit;
l_cnt := 1;
for TAB_COLUMNS in (select column_name, data_type
from user_tab_columns
where table_name = p_table
order by column_id) loop
l_cnames := l_cnames || tab_columns.column_name || ',';
l_bindvars := l_bindvars || case
when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then
--'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),'
'to_date(:b' || l_cnt || ',''MM-DD-YYYY HH24:MI:SS''),'
else
':b' || l_cnt || ','
end;
l_cnt := l_cnt + 1;
--dbms_output.put_line(l_bindvars );
end loop;
begin
l_cnames := rtrim(l_cnames, ',');
L_BINDVARS := RTRIM(L_BINDVARS, ',');
/* Modified By Muhamamd Waqas Khan
Date : 15 March 016
*/
BEGIN
L_INPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'r');
exception
WHEN UTL_FILE.INVALID_OPERATION THEN
raise l_error;
END;
---dbms_output.put_line(l_bindvars || ' - ' || l_cnames);
IF p_ignore_headerlines > 0 THEN
BEGIN
FOR i IN 1 .. p_ignore_headerlines LOOP
UTL_FILE.get_line(l_input, l_lastLine);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_eof := TRUE;
end;
END IF;
if not v_eof then
dbms_sql.parse(l_theCursor,
'insert into ' || p_table || '(' || l_cnames ||
') values (' || l_bindvars || ')',
dbms_sql.native);
loop
begin
utl_file.get_line(l_input, l_lastLine);
exception
when NO_DATA_FOUND then
exit;
end;
if length(l_lastLine) > 0 then
for i in 1 .. l_cnt - 1 LOOP
dbms_output.put_line(ltrim(REGEXP_SUBSTR(l_lastline,
'(^|,)("[^"]*"|[^",]*)',
1,
i),
p_delimiter));
dbms_sql.bind_variable(l_theCursor,
':b' || i,
rtrim(rtrim(ltrim(ltrim(REGEXP_SUBSTR(l_lastline,
'(^|,)("[^"]*"|[^",]*)',
1,
i),
','),
'"'),
','),
'"'));
end loop;
begin
l_status := dbms_sql.execute(l_theCursor);
-- dbms_output.put_line('Waqas:' ||l_theCursor);
l_rowCount := l_rowCount + 1;
exception
when OTHERS then
L_ERRMSG := SQLERRM;
insert into CSV_AR_INV_LOADER_BADLOG
(TABLE_NAME, line_number, ERRM, data, ERROR_DATE)
values
(p_table,
l_rowCount + 2,
l_errmsg,
l_lastLine,
systimestamp);
end;
end if;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose(l_input);
commit;
end if;
insert into CSV_AR_INV_LOADER_HIST
(FILENAME, TABLE_NAME, NUM_OF_REC, IMPORT_DATE)
values
(P_FILENAME, P_TABLE, l_rowCount, sysdate);
UTL_FILE.FRENAME(P_DIR,
P_FILENAME,
P_DIR,
REPLACE(P_FILENAME,
'.csv',
'_' ||
TO_CHAR(SYSDATE, 'DD_MON_RRRR_HH24_MI_SS_AM') ||
'.csv'));
commit;
exception
when l_error then
L_RES := 'Either Invalid File or File Not Exists';
end;
RETURN L_RES;
end CSV_AR_LOADER_FUNC;
-- PROCEDURE LOAD_CSV
-- PURPOSE: This Procedure read the data from a CSV file.
-- And load it into the target oracle table.
-- Finally it renames the source file with date.
-- P_FILENAME -- The name of the flat file(a text file)
-- P_DIRECTORY -- Name of the directory where the file is been placed.
-- Note: The grant has to be given for the user to the directory
-- before executing the function
-- P_IGNORE_HEADERLINES: -- Pass the value as '1' to ignore importing headers.
-- P_DELIMITER -- By default the delimiter is used as ','
-- As we are using CSV file to load the data into oracle
-- P_OPTIONAL_ENCLOSED -- By default the optionally enclosed is used as '"'
-- As we are using CSV file to load the data into oracle
-- AUTHOR: M WAQAS KHAN
-- Date : 24 FEB, 2017
**************************************************************************/
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_lastLine varchar2(4000);
l_cnames varchar2(4000);
l_bindvars varchar2(4000);
l_status integer;
l_cnt number default 0;
l_rowCount number default 0;
--l_sep char(1) default NULL;
L_ERRMSG varchar2(4000);
V_EOF BOOLEAN := false;
L_RES VARCHAR2(100) := 'SUCCESS';
l_error exception;
begin
DELETE FROM CSV_AR_INV_TABLE;
commit;
delete from CSV_AR_INV_LOADER_BADLOG;
commit;
l_cnt := 1;
for TAB_COLUMNS in (select column_name, data_type
from user_tab_columns
where table_name = p_table
order by column_id) loop
l_cnames := l_cnames || tab_columns.column_name || ',';
l_bindvars := l_bindvars || case
when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then
--'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),'
'to_date(:b' || l_cnt || ',''MM-DD-YYYY HH24:MI:SS''),'
else
':b' || l_cnt || ','
end;
l_cnt := l_cnt + 1;
--dbms_output.put_line(l_bindvars );
end loop;
begin
l_cnames := rtrim(l_cnames, ',');
L_BINDVARS := RTRIM(L_BINDVARS, ',');
/* Modified By Muhamamd Waqas Khan
Date : 15 March 016
*/
BEGIN
L_INPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'r');
exception
WHEN UTL_FILE.INVALID_OPERATION THEN
raise l_error;
END;
---dbms_output.put_line(l_bindvars || ' - ' || l_cnames);
IF p_ignore_headerlines > 0 THEN
BEGIN
FOR i IN 1 .. p_ignore_headerlines LOOP
UTL_FILE.get_line(l_input, l_lastLine);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_eof := TRUE;
end;
END IF;
if not v_eof then
dbms_sql.parse(l_theCursor,
'insert into ' || p_table || '(' || l_cnames ||
') values (' || l_bindvars || ')',
dbms_sql.native);
loop
begin
utl_file.get_line(l_input, l_lastLine);
exception
when NO_DATA_FOUND then
exit;
end;
if length(l_lastLine) > 0 then
for i in 1 .. l_cnt - 1 LOOP
dbms_output.put_line(ltrim(REGEXP_SUBSTR(l_lastline,
'(^|,)("[^"]*"|[^",]*)',
1,
i),
p_delimiter));
dbms_sql.bind_variable(l_theCursor,
':b' || i,
rtrim(rtrim(ltrim(ltrim(REGEXP_SUBSTR(l_lastline,
'(^|,)("[^"]*"|[^",]*)',
1,
i),
','),
'"'),
','),
'"'));
end loop;
begin
l_status := dbms_sql.execute(l_theCursor);
-- dbms_output.put_line('Waqas:' ||l_theCursor);
l_rowCount := l_rowCount + 1;
exception
when OTHERS then
L_ERRMSG := SQLERRM;
insert into CSV_AR_INV_LOADER_BADLOG
(TABLE_NAME, line_number, ERRM, data, ERROR_DATE)
values
(p_table,
l_rowCount + 2,
l_errmsg,
l_lastLine,
systimestamp);
end;
end if;
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose(l_input);
commit;
end if;
insert into CSV_AR_INV_LOADER_HIST
(FILENAME, TABLE_NAME, NUM_OF_REC, IMPORT_DATE)
values
(P_FILENAME, P_TABLE, l_rowCount, sysdate);
UTL_FILE.FRENAME(P_DIR,
P_FILENAME,
P_DIR,
REPLACE(P_FILENAME,
'.csv',
'_' ||
TO_CHAR(SYSDATE, 'DD_MON_RRRR_HH24_MI_SS_AM') ||
'.csv'));
commit;
exception
when l_error then
L_RES := 'Either Invalid File or File Not Exists';
end;
RETURN L_RES;
end CSV_AR_LOADER_FUNC;
----------------------------------------------------------------------------------------------------
Step # 4
Execute Function and Enjoy !!
