Loading CSV files into Oracle tables using PLSQL

There are various ways to load flat files into Oracle tables like using few popular GUI i.e. TOAD , PLSQL Developer, SQL Developer and many more..

But we can also use this by few other techniques such as using External tables or by using UTL_FILE package.

Here I am going to show how to use UTL_FILE package and read the CSV from the Oracle directory and load into the target table and finally rename the CSV by concatenating with sysdate.

By renaming the file it is always easier to track by file name with date or timestamp like when the source flat file was imported into the oracle table.

So to understand all the above mentioned we will have to go through a complete example.

Let us create a sample target table:

SQL> CREATE TABLE temp
 ( col1 NUMBER,
 col2 VARCHAR2(40),
 col3 DATE
 );

Table created.

SQL>

Create a CSV file with the below sample data:


COL1 COL2 COL3
1 AA 1-Jan-12
2 BB12 2-Feb-12
3 CCcc11 22-Aug-12
4 DD 22-Aug-12

Put the CSV file into the Oracle directory and if you do not have any directory then create it as like below:


SQL>CREATE OR REPLACE DIRECTORY ORA_DIR AS 'C:\temp'; -- Change the path as per your wish

SQL> GRANT READ, WRITE ON DIRECTORY ORA_DIR TO << Your User >>;

Now here is the Procedure to read the CSV file and load it into the temp table which we have created earlier and finally rename the source file with

<<File Name>><<_DD_MON_RR>>.csv  .

After so many comments to my older post , I thought of making it little better and generic way to load the CSV file into my tables. Earlier what I used to do is write the insert script for the target table and execute it to load the file.

Now if I have to load 3 files into 3 different tables then I have to write 3 different procedure to load the files but then thought why not just pass the table name and file name as a parameter and load it directly into my target table and then finally all my files would be renamed like concatenating with date .

Again it would be helpful if I have a table which will have the track of what files are been loaded and how many number of records are been passed along with that if I have a table to get the log like the discarded records then would be very helpful to do further analysis on why the records are getting failed.

Check if the user is having UTL_FILE privileges or not :


SQL> SELECT OWNER,
OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME = ‘UTL_FILE’
AND OWNER =<>;

 

 

If the user is not having the privileges then grant “UTL_FILE” to user from SYS user:

SQL> GRANT EXECUTE ON UTL_FILE TO <>;

To make use of the I have created two different tables as below: 

import_hist table and badlog table to track the history of the load and another to check the bad log if it occurs while doing the load .

Under the same user create an error log table to log the error out records while doing the import:

 

SQL> CREATE TABLE badlog
(
table_name VARCHAR2(200)
errm VARCHAR2(4000),
data VARCHAR2(4000) ,
error_date TIMESTAMP
);

 

 

Under the same user create Load history table to log the details of the file and tables that are imported with a track of records loaded:

 
SQL> create table IMPORT_HIST
(
FILENAME varchar2(200),
TABLE_NAME varchar2(200),
NUM_OF_REC number,
IMPORT_DATE DATE
);

 

 

One of my team member who is an intern ,took this as an challenge to make the correction on my blog and to make it very generic like what I mentioned for PIPE delimited files.

Thanks a ton Vani for your contribution…!!!

CREATE OR REPLACE FUNCTION LOAD_CSV ( p_table in varchar2,
 p_dir in varchar2 DEFAULT 'ORA_DIR' ,
 P_FILENAME in varchar2,
 p_ignore_headerlines IN INTEGER DEFAULT 1,
 p_delimiter in varchar2 default ',',
 p_optional_enclosed in varchar2 default '"' )
 return number
 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:
-- Sloba
-- Version 1.0
-- Vani (bobba.vani31@gmail.com)
-- Version 1.1
**************************************************************************/
 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;

 begin

 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"),' else ':b'|| l_cnt || ',' end;

 l_cnt := l_cnt + 1;
 end loop;
 l_cnames := rtrim(l_cnames,',');
 L_BINDVARS := RTRIM(L_BINDVARS,',');

 L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
 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_sql.bind_variable( l_theCursor, ':b'||i,
 rtrim(rtrim(ltrim(ltrim(
 REGEXP_SUBSTR(l_lastline,'(^|,)("[^"]*"|[^",]*)',1,i),p_delimiter),p_optional_enclosed),p_delimiter),p_optional_enclosed));
 end loop;
 begin
 l_status := dbms_sql.execute(l_theCursor);
 l_rowCount := l_rowCount + 1;
 exception
 when OTHERS then
 L_ERRMSG := SQLERRM;
 insert into BADLOG ( TABLE_NAME, ERRM, data, ERROR_DATE )
 values ( P_TABLE,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 IMPORT_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;
 RETURN L_ROWCOUNT;
 end LOAD_CSV;

Here is the sample PLSQL block which will call “LOAD_CSV” procedure to read the flat file (i.e. CSV file) from the directory which I have used as ‘ORA_DIR’ and rename the source file:

DECLARE
 p_table VARCHAR2(200) := <>;
 p_dir VARCHAR2(200) := <>;
 p_filename VARCHAR2(200) := <>;
 v_return NUMBER;
BEGIN
 v_return := LOAD_CSV(p_table => p_table
 ,p_dir => p_dir
 ,p_filename => p_filename
 ,p_ignore_headerlines => p_ignore_headerlines
 ,p_delimiter => p_delimiter
 ,p_optional_enclosed => p_optional_enclosed);

 dbms_output.put_line(‘v_return = ‘ || v_return);
END;

You can modify the and utilize it ….

Thanks ,

Sloba

Advertisements

90 thoughts on “Loading CSV files into Oracle tables using PLSQL

      • thanks first
        1-code version = Version 1.0
        2-Procedure created. (no errors)
        —-your code after modify —-
        CREATE OR REPLACE PROCEDURE load_slob_csv (
        p_filename IN VARCHAR2 DEFAULT ‘temp.csv’,
        p_directory IN VARCHAR2 DEFAULT ‘ORALOAD’,
        p_ignore_headerlines IN INTEGER DEFAULT 1,
        p_delimiter IN VARCHAR2 DEFAULT ‘,’)
        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

        — AUTHOR:
        — Sloba
        — Version 1.0
        **************************************************************************/
        v_filehandle UTL_FILE.file_type;
        v_text VARCHAR2(32767);
        v_eof BOOLEAN := FALSE;
        v_fields DBMS_SQL.varchar2a;
        v_field_index NUMBER;
        v_length NUMBER;
        v_start NUMBER;
        v_index NUMBER;
        v_enclosed_start NUMBER;
        v_enclosed_end NUMBER;
        BEGIN
        v_filehandle := UTL_FILE.fopen(p_directory, p_filename, ‘r’, 32767);
        IF p_ignore_headerlines > 0 THEN
        BEGIN
        FOR i IN 1 .. p_ignore_headerlines
        LOOP
        UTL_FILE.get_line(v_filehandle, v_text);
        END LOOP;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_eof := TRUE;
        END;
        END IF;

        WHILE NOT v_eof
        LOOP
        BEGIN
        DBMS_OUTPUT.put_line( ‘——————————————————————‘);
        UTL_FILE.get_line(v_filehandle, v_text);
        DBMS_OUTPUT.put_line(‘v_text=’ || v_text);
        v_fields.delete;
        v_field_index := 0;
        v_length := LENGTH(v_text);
        v_start := 1;
        v_enclosed_start := INSTR(v_text, ‘”‘, 1);
        v_enclosed_end := INSTR(v_text, ‘”‘, v_enclosed_start + 1);

        WHILE (v_start v_enclosed_start
        AND v_index ‘temp.csv’, P_DIRECTORY => ‘ORALOAD’, P_IGNORE_HEADERLINES =>’1′);
        the result:
        v_text=1, AA, 1-Jan-12
        v_text=1, AA, 1-Jan-12
        v_text=1, AA, 1-Jan-12
        v_text=1, AA, 1-Jan-12
        v_text=1, AA, 1-Jan-12
        v_text=1, AA, 1-Jan-12
        v_text=1, AA, 1-Jan-12
        PL/SQL procedure successfully completed.
        ——-
        select * from temp;
        no rows selected

        waiting your replay

  1. Dear Jamal ,

    Can you please follow the below steps and let me know if you are still facing any issues.
    1)Created the procedure. again.
    2) Keep the temp.CSV file on the directory that you have created like ‘ORALOAD’
    3) Grant : grant read,write, execute on directory ORALOAD to <>;
    4) Grant : grant execute on UTL_FILE to <>;
    4) Now just to test if you are able to read the file from the directory where you have place the file , run the below code:

    SQL> DECLARE
    read_file UTL_FILE.FILE_TYPE;
    BEGIN
    read_file := UTL_FILE.FOPEN(‘ORALOAD’,’temp.csv’,’r’);
    UTL_FILE.FCLOSE(out_file);
    END;
    /

    If you are successfully able to read the file then execute the procedure :
    DECLARE
    P_FILENAME VARCHAR2(200):=’temp.csv’;
    BEGIN
    LOAD_CSV(
    P_FILENAME => P_FILENAME
    );
    END;

    Then check the table .

    Thanks,
    Sloba

  2. I executed the same code in my local, but still the data does not got inserted into the table. Then i tried to debug, it is failing to insert. I think in the varibles v_fields (0),
    v_fields (1),
    TO_DATE (v_fields (3), ‘DD/MON/YYYY’) data is not getting populated.

    Can u help me to resolve the proble.

    Details:
    CSV contains

    1,AA,511
    2,BB,569
    1,AA,44
    2,BB,4984

    DIRECTORY NAME:UTL_DIR
    CSV NAME : BOOK1.CSV

  3. Hi,
    I am new to pl/sql and sorry if i am asking a basic question here. But any help is much appriciated in this regard, here is the issue.
    On a daily basis I will be getting flat files which are delimited ones. For example today I will be getting a employee file and tomorrow I will be getting a payroll file, the delimiter in the file will decide the data type.(delimiter for employee file is #, and that of payrol file is , (comma) and so on). I need to lookup on the file first and get the delimiter from it.
    Then there is a lookup table in the same DB, which stores the relation ship between the delimitera nd the target table name.
    Once the delimiter in the file has been identified, I will have to lookup on the lookup table to get the actual target table name to which I should load the flat file data.
    then read through the file and upload the data in the file to the target table.
    The table structure and the number of columns in the tables are not same.
    But I need one general PL/SQL code to read the files and load it to the target tables. It should be a generalized pl/sql code and I should not change it based on the input file.

    Please help in addressing this issue.

    Thanks,
    Bala

    • See for the process which you have mentioned I would go with one function which will load the file but based on the the input parameter which can be a PIPE symbol or # or ‘,'(comma) or anything. The I would use the same on a PLSQL block which would take care to check the filename only as I already know what kind of file type is going to come .

      And if the file type if not coming same or not been fixed then try to explain your team or lead clearly that this should be fixed.

      • I am sorry, but I didnt clearly understand the response, I am looking for a pl/sql code to read the file and load it to the Data base table, and the data base table name is based on the delimiter used in the input file. So my pl/sql code should get the delimiter from the file first and then look up on a table to get the target table name to which the data should go, and proceed with the insertion.

    • May be but I am not aware of such process where you can identify without looking the flat file or unless I have the information from the source that what kind of file type is coming for load.
      The process you are telling is not correct.
      The file load system should be based on the file name and the type it is coming and then you can decide where to load the file.
      For example :
      I have one flat file i.e emp.txt and which is coming everyday or daily or on certain intervals. Then I should know at first stage what kind of the file is coming to me like PIPE or # or what kind of delimiter the file is having. Then based on that I can write the function which will load the file only by passing the table name that could be EMP or EMPLOYEE table. And If the file name is coming like emp+timstamp then I need to truncate the timestamp dynamically while reading the name and then I will check for other file names which are been loaded into the database and then I will try to load it.

      See at any time you should have the information of what kind of data you are getting then you can write the same in PLSQL.

      Thanks,
      Sloba

      • Hi Thanks for the response, here is the correct requirement that I have, I am sorry for late response, hope the below information will help you,

        I have a config table with the name employee_config with the below values,

        delimiter targettable Sourceloaction sourcefile
        , emp_data C:\Temp employee.txt

        I have another config table – config_student with the below values
        delimiter targettable Sourceloaction sourcefile
        # student_data C:\Temp student.txt

        Below is the description of my target tables,

        desc emp_data
        Name Null Type
        ——– —- ————
        EMP_NO NUMBER
        EMP_NAME VARCHAR2(20)
        GENDER VARCHAR2(20)

        desc student_data
        Name Null Type
        ——– —- ————
        STUDENT_NO NUMBER
        FIRST_NAME VARCHAR2(20)
        LAST_NAME VARCHAR2(20)
        GENDER VARCHAR2(20)

        Here is the sample source files that i will receive,

        employee.txt
        —————
        155510,Sam,M
        117270,Jessica,F
        123456,Peter,M

        Student.txt
        ————-
        1#Sam#Anderson#M
        2#John#Cena#M
        3#Jessica#Hom#F

        Now I will be getting the source file with the names as student.txt, or employee.txt only, and the delimiter in the employee file will always be , and the delimiter in the student file will always be #.
        The number of fields in employee file will always be 3 and number of fields in student file will always be 4.
        Now i need a generalized pl/sql stored proc to read the above files and load it to target tables.
        No matter what the input file is, but my stored proc should be the same one to process the data from the incoming file.

        Now I have set up my util_file_dir and i am able to read the file and i have got the data from the source file to a collections data set.
        But I do not know the below two things,
        1. How to write an insert statement to load the data from my collections to my target table.
        2. In the below stored proc i have used forall to insert the data, but I am getting error there,

        Can you please take a look at the below stored proc and see what error that I have made,

        create or replace
        PROCEDURE LOAD_DATA_ARRAY
        (param IN varchar2)
        IS
        v_sql varchar2(32676);
        v_targettable varchar2(2000); — Target table name
        v_configtable varchar2(2000); — To hold the configuration table name
        v_delimiter varchar2(100); — Delimiter in the incoimg file
        v_filename varchar2(2000); — Source file name
        v_line varchar2(32676);
        v_dir varchar2(32676); — Source location
        v_file UTL_FILE.FILE_TYPE; — File handle
        v_start pls_integer;
        v_position pls_integer;
        v_sql_ins varchar2(32676);

        TYPE col_type IS TABLE OF varchar2(32767) index by pls_integer;
        my_tab col_Type;
        V_col_no pls_integer;
        V_count pls_integer :=0;

        BEGIN
        v_sql := ‘SELECT delimiter, targettable, sourcelocation, sourcefile FROM ‘|| param ||”;
        — SELECT targettable INTO v_targettable FROM &param;
        EXECUTE IMMEDIATE v_sql INTO v_delimiter, v_targettable, v_dir, v_filename;
        dbms_output.put_line(‘delimiter=’ ||v_delimiter);
        dbms_output.put_line(‘target table=’ ||v_targettable);
        dbms_output.put_line(‘Source location=’ ||v_dir);
        dbms_output.put_line(‘Source file name=’ ||v_filename);
        v_file := UTL_FILE.FOPEN(v_dir,v_filename,’r’);
        IF NOT UTL_FILE.IS_OPEN(v_file) THEN
        DBMS_OUTPUT.PUT_LINE(‘Unable to open the file’);
        RETURN;
        END IF;
        LOOP
        BEGIN
        UTL_FILE.GET_LINE(v_file,v_line);
        DBMS_OUTPUT.PUT_LINE(v_line);
        IF v_line IS NULL THEN
        EXIT;
        END IF;
        v_col_no :=1;
        v_start :=1;
        WHILE INSTR(v_line,v_delimiter,1,v_col_no) !=0
        LOOP
        v_position := INSTR(v_line,v_delimiter,1,v_col_no);
        my_tab(v_col_no) := SUBSTR(v_line,v_start, v_position-v_start);
        v_start := v_position+1;
        v_col_no := v_col_no+1;
        END LOOP;
        V_start := v_position+1;
        my_tab(v_col_no) := SUBSTR(v_line,v_start);
        dbms_output.put_line(‘just there to insert’);
        V_count := v_count+1;
        –INSERT INTO emp_data values (my_tab(‘1’),my_tab(‘2’),my_tab(‘3’));
        dbms_output.put_line(v_targettable);
        –v_sql_ins := ‘INSERT INTO emp_data values’;
        forall i in 1..my_tab.count
        insert into emp_data values my_tab(i);
        –EXECUTE IMMEDIATE v_sql_ins;
        EXCEPTION WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(‘Empty file’);
        EXIT;
        END;
        END LOOP;
        UTL_FILE.FCLOSE(v_file);
        END;

  4. Here are the steps:

    1) First create a directory and grant read , write , execute to the user from where you want to access the flat files and load it.

    2) Write a generic function to load PIPE delimited flat files:

    CREATE OR REPLACE FUNCTION TABLE_LOAD ( p_table in varchar2,
    p_dir in varchar2 DEFAULT ‘YOUR_DIRECTORY_NAME’,
    P_FILENAME in varchar2,
    p_ignore_headerlines IN INTEGER DEFAULT 1,
    p_delimiter in varchar2 default ‘|’,
    p_optional_enclosed in varchar2 default ‘”‘ )
    return number
    is
    /***************************************************************************
    — FUNCTION TABLE_LOAD
    — PURPOSE: Load the flat files i.e. only text files to Oracle
    — tables.
    — This is a generic function which can be used for
    — importing any text flat files to oracle database.
    — PARAMETERS:
    — P_TABLE
    — Pass name of the table for which import has to be done.

    — P_DIR
    — 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_FILENAME
    — The name of the flat file(a text file)

    — P_IGNORE_HEADERLINES
    — By default we are passing 1 to skip the first line of the file
    — which are headers on the Flat files.

    — P_DELIMITER
    — Dafault “|” pipe is been passed.

    — P_OPTIONAL_ENCLOSED
    — Optionally enclosed by ‘ ” ‘ are been ignored.

    — AUTHOR:
    — Slobaray
    **************************************************************************/
    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;

    begin

    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”),’ else ‘:b’|| l_cnt || ‘,’ end;

    l_cnt := l_cnt + 1;
    end loop;
    l_cnames := rtrim(l_cnames,’,’);
    L_BINDVARS := RTRIM(L_BINDVARS,’,’);

    L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, ‘r’ );
    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_sql.bind_variable( l_theCursor, ‘:b’||i,
    ltrim(rtrim(rtrim(
    regexp_substr(l_lastLine,'([^|]*)(\||$)’,1,i),p_delimiter),p_optional_enclosed),p_optional_enclosed));
    end loop;

    begin
    l_status := dbms_sql.execute(l_theCursor);
    l_rowCount := l_rowCount + 1;
    exception
    when OTHERS then
    L_ERRMSG := SQLERRM;
    insert into BADLOG ( TABLE_NAME, ERRM, data, ERROR_DATE )
    values ( P_TABLE,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 IMPORT_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,
    ‘.txt’,
    ‘_’ || TO_CHAR(SYSDATE, ‘DD_MON_RRRR_HH24_MI_SS_AM’) || ‘.txt’
    ));
    commit;
    RETURN L_ROWCOUNT;
    end TABLE_LOAD;

    /

    Note: when you run the function then it will also modify the source flat file with timestamp , so that we can have the track like which file was loaded .

    3) Check if the user is having UTL_FILE privileges or not :

    SQL> SELECT OWNER,
    OBJECT_TYPE
    FROM ALL_OBJECTS
    WHERE OBJECT_NAME = ‘UTL_FILE’
    AND OWNER =<>;

    If the user is not having the privileges then grant “UTL_FILE” to user from SYS user:

    SQL> GRANT EXECUTE ON UTL_FILE TO <>;

    4) In the function I have used two tables like:
    import_hist table and badlog table to track the history of the load and another to check the bad log if it occurs while doing the load .

    Under the same user create an error log table to log the error out records while doing the import:

    SQL> CREATE TABLE badlog
    (
    errm VARCHAR2(4000),
    data VARCHAR2(4000) ,
    error_date TIMESTAMP
    );

    Under the same user create Load history table to log the details of the file and tables that are imported with a track of records loaded:
    SQL> create table IMPORT_HIST
    (
    FILENAME varchar2(200),
    TABLE_NAME varchar2(200),
    NUM_OF_REC number,
    IMPORT_DATE DATE
    );

    5) Finally run the PLSQL block and check if it is loading properly or not if not then check the badlog:

    Execute the PLSQL block to import the data from the USER:

    SQL> declare
    P_TABLE varchar2(200):=<>;
    P_DIR varchar2(200):=<>;
    P_FILENAME VARCHAR2(200):=<>;
    v_Return NUMBER;
    BEGIN
    v_Return := TABLE_LOAD(
    P_TABLE => P_TABLE,
    P_DIR => P_DIR,
    P_FILENAME => P_FILENAME,
    P_IGNORE_HEADERLINES => P_IGNORE_HEADERLINES,
    P_DELIMITER => P_DELIMITER,
    P_OPTIONAL_ENCLOSED => P_OPTIONAL_ENCLOSED
    );

    DBMS_OUTPUT.PUT_LINE(‘v_Return = ‘ || v_Return);
    end;

    6) Once the PLSQL block is been executed then check for any error log table and also the target table if the records are been successfully imported or not.

  5. Can my CSV files storing in client machine rather than create directory on server?
    I don’t have permission to do for directory creation.

    • See if you do not have the permission then you can ask you DBA to do that as the file should always present on the same server and a directory has to be created on the same path. Then you can use the function which is on UTL_FILE concept else you can only use the GUI based tools to load the file into a object in oracle database.

      Thanks,
      Sloba

  6. Thanks for sharing your program, I was able to use it for loading csv files. But I have 2 questions: 1. All utl_file functions (fcopy, get_line, fclose,etc) are executed and working except for the utl_file.frename which gives me error ORA-29283: invalid file operation
    ORA-06512: at “SYS.UTL_FILE”, line 348
    ORA-06512: at “SYS.UTL_FILE”, line 1290
    ORA-06512: at line 2
    Would you happen to know the reason behind? Is there any rights/privileges that needs to be created?
    2. My .csv file has a format of number(1),date(01-JAN-2013),time(05:01:30 AM); I believe that oracle column type for time data should be date thus how should I format it for the load_csv program insert portion?

    Thanks again.

    • The errors that you have mentioned “ORA-06512” is only provides the line number of the error inside your package.
      You can also check the data type of the parameters that you have declared , as you mentioned for the datetime.

      You can also try to changing the time to varacha2 and try it …Boz most of the time if we use time with date then we use timstamp or date. If only time then go with varchar2 data type .

      Also check all the privelages for UTL_FIL package for given to the user from where you created the procedure or package to load the data or should be given to PUBLIC like READ< WRITE and Execute etc…

  7. Hi

    procedure are executed without any error but no record is loaded.i had applied allur chnages from the forum threads

    • Did you changed the code from :

      v_field_index := v_field_index + 1;
      END LOOP;
      INSERT
      INTO temp(col1,col2, col3)
      VALUES (
      v_fields(0),
      v_fields(1),
      TO_DATE(v_fields(3), ‘MM/DD/YYYY’));

      TO :

      v_field_index := v_field_index + 1;
      END LOOP;
      INSERT
      INTO temp(col1,col2, col3)
      VALUES (
      v_fields(0),
      v_fields(1),
      TO_DATE(v_fields(2), ‘MM/DD/YYYY’));

      And did you checked if the column no 3 of your CSV file is in date format if not then select the entire column and change it to date format and then try executing it.

  8. I’m glad you liked my csv reader from experts-exchange enough to repost it on your site
    It would have been nice to note your source and give credit for it though.

  9. Hi, Sloba.
    Thank you for sharing your procedure. It’s work nice in my case, with appropriate modifications, of course.
    Now, I need to do the same but with an excel file (.xls), but I do not know where to start.
    I need to automate the process, so that does not help me transform. xls. into .cvs files.
    Can anybody help me, please?

    • You want to load an XLS file to your oracle table..is this what you are looking for ? If yes then ..
      Please send me your table structure and few data on your XLS file to my email address.
      I will look into that and reply you..

      • Hi Sloba,

        I’m a big fan of you. I’m a beginner of plsql. and this site is helping me a lot. I need to load data from an excel file to oracle table and I have required grant privileges on the folder where the excel file is located in the server. Can you please help me to how proceed next.

        Here is the sample data of excel file.

        email_address,first_name,last_name,date_of_birth
        aa@aa.com,ff,ll,11/24/1980
        bb@bb.com,ff2,ll2,5/5/1985
        cc@cc.com,ff3,ll3,3/29/1975

        Please any help is really helpful.
        Thanks in advance…

  10. Hi Slobha,
    I m very new to PL/SQL and my requirement is to load a csv file onto a Oracle table using a PL/SQL procedure.

    The LOAD_CSV procedure that you mentioned is very helpful but can you please clearly explain it step by step(if possible) so that I can understand(as I new) and implement it accordingly to the fields(row data) of my csv file.

    Thanks a lot in Advance

    • I’ll be happy to help. The procedure above was actually written by me originally, I’ve posted several variations of it on experts-exchange as noted above.

      First it opens the file – this just gives a handle to use later on.
      Next, it skips the first few lines if you want them to. This is because you might have column headers at the start of the file that you don’t want to load with the real data.
      The real work then happens in the two loops that follow.

      First is a loop that pulls one line at a time from the file
      Inside that loop is a second loop that iterates through each field of each line.

      The inner loop finds each field by looking for the next delimiter and when it runs out of delimiters whatever is left is the last field of that line.

      The procedure, as implemented above assumes you have only 3 fields to read
      but is easily extensible to any number.

      When it read the three fields of one line it writes them to a table
      When all of the lines have been processed the file is closed and then renamed – that last part is an addition by slobarray to the base functionality.

      I hope that helps

    • As mentioned Sir Sean D.Stuber was the original creator of this procedure to load CSV file into Oracle. But in this if there is a CLOB column or other datatype in your table then you need to modify the code slightly as like below:

      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”),’ else ‘:b’|| l_cnt || ‘,’ end;

      l_cnt := l_cnt + 1;

      So that the loading of the CSV would be more generic than putting the column handler inside the code..

      I hope modifying the code will not be an issue for the original creator. As he has already mentioned all the steps very clearly and nicely .

      I am big fan of Sir Sean D.Stuber (http://www.experts-exchange.com/M_1388151.html). I would also like you to take a look to his other posts and articles on Experts-Exchange.com:

      Thanks,
      Sloba

  11. Hello,

    I’m trying to reproduce this example. After having fixed all the privileges problems, the execution of the procedure says to have finish with success, but I have no data in the TEMP table. Please, could you help? for the CSV I added ‘,’. I thought that this might be the error…

    COL1,COL2,COL3
    1,AA,1-Jan-12
    2,BB12,2-Feb-12
    3,CCcc11,22-Aug-12
    4,DD,22-Aug-12

    And in for the procedure (I changed the column 3 to 2):
    CREATE OR REPLACE PROCEDURE load_csv (
    p_filename IN VARCHAR2,
    p_directory IN VARCHAR2 DEFAULT ‘ORA_DIR’,
    p_ignore_headerlines IN INTEGER DEFAULT 1,
    p_delimiter IN VARCHAR2 DEFAULT ‘,’)
    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

    — AUTHOR:
    — Sloba
    — Version 1.0
    **************************************************************************/
    v_filehandle UTL_FILE.file_type;
    v_text VARCHAR2(32767);
    v_eof BOOLEAN := FALSE;
    v_fields DBMS_SQL.varchar2a;
    v_field_index INTEGER;
    v_length INTEGER;
    v_start INTEGER;
    v_index INTEGER;
    v_enclosed_start INTEGER;
    v_enclosed_end INTEGER;
    BEGIN
    v_filehandle := UTL_FILE.fopen(p_directory, p_filename, ‘r’, 32767);

    IF p_ignore_headerlines > 0
    THEN
    BEGIN
    FOR i IN 1 .. p_ignore_headerlines
    LOOP
    UTL_FILE.get_line(v_filehandle, v_text);
    END LOOP;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    v_eof := TRUE;
    END;
    END IF;

    WHILE NOT v_eof
    LOOP
    BEGIN
    DBMS_OUTPUT.put_line(
    ‘——————————————————————‘);
    UTL_FILE.get_line(v_filehandle, v_text);
    DBMS_OUTPUT.put_line(‘v_text=’ || v_text);
    v_fields.delete;
    v_field_index := 0;
    v_length := LENGTH(v_text);
    v_start := 1;
    v_enclosed_start := INSTR(v_text, ‘”‘, 1);
    v_enclosed_end := INSTR(v_text, ‘”‘, v_enclosed_start + 1);

    WHILE (v_start v_enclosed_start
    AND v_index < v_enclosed_end
    THEN
    v_index := INSTR(v_text, p_delimiter, v_enclosed_end);
    v_enclosed_start := INSTR(v_text, '"', v_enclosed_end + 1);

    IF v_enclosed_start != 0
    THEN
    v_enclosed_end :=
    INSTR(v_text, '"', v_enclosed_start + 1);
    END IF;
    END IF;

    IF v_index = 0
    THEN
    v_fields(v_field_index) :=
    TRIM(LTRIM(RTRIM(SUBSTR(v_text, v_start), '"'), '"'));
    v_start := v_length + LENGTH(p_delimiter);
    ELSE
    v_fields(v_field_index) :=
    TRIM(
    LTRIM(
    RTRIM(
    SUBSTR(v_text, v_start, v_index – v_start),
    '"'),
    '"'));
    v_start := v_index + LENGTH(p_delimiter);
    END IF;

    v_field_index := v_field_index + 1;
    END LOOP;
    INSERT
    INTO temp(col1,col2, col3)
    VALUES (
    v_fields(0),
    v_fields(1),
    TO_DATE(v_fields(2), ‘MM/DD/YYYY’));
    DBMS_OUTPUT.put_line(
    '——————————————————————');
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    v_eof := TRUE;
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line(
    '*************************************************************');
    DBMS_OUTPUT.put_line(SQLERRM);
    DBMS_OUTPUT.put_line(v_text);
    DBMS_OUTPUT.put_line(
    '*************************************************************');
    END;
    END LOOP;

    UTL_FILE.fclose(v_filehandle);
    UTL_FILE.FRENAME(
    'ORA_DIR',
    p_filename,
    'ORA_DIR',
    REPLACE(p_filename,
    '.csv',
    '_' || TO_CHAR(SYSDATE, 'DD_MON_RR') || '.csv'
    ));
    EXCEPTION
    WHEN OTHERS
    THEN
    IF UTL_FILE.is_open(v_filehandle)
    THEN
    UTL_FILE.fclose(v_filehandle);
    END IF;

    RAISE;
    END load_csv;

    /

    Did I miss something? I execute this with "myUser".

    Thanks

  12. Hi Sloba, I have tried this and it is working fine for me.. make following changes in the procedure.
    1. Remove “Raise” keyword given in the second last line of the procedure.
    2. In line 114 “3″ should be a “2″
    3. Use commit after line number 114.
    4. Date format used in line 114 should be same as the record there in the .csv file.

  13. Hi,

    Thanks for sharing the code. I’m new to PL/SQL and found this program very useful for my work.
    However I have a question. How can I map col1 from csv to col1 of targtet table as I need, for example, col2 data to go to col3 and so on.

    Appreciate your help!

    • From the example what I have shared it just to map the table columns order wise to the CSV.

      If you want to change the order or the mapping then you can write another stand alone procedure to pick the data from csv and directly insert into your table and in that you can change the order.

  14. Line 98
    REGEXP_SUBSTR(l_lastline,'(^|,)(“[^”]*”|[^”,]*)’,1,i)

    Here in the regular expression is not used variable p_delimiter.
    Therefore, when the use of a separator other than “,” – get the error

    • This targeted of the procedure was to read the data from a CSV file, which is a “,” comma separated file. For which only comma is working currently but you can modify as per your need and change the expression to use other delimiter too.

  15. Hi Sloba,

    I am getting the below error for the function LOAD_CSV

    Error report –
    ORA-00984: column not allowed here
    ORA-06512: at “SYS.DBMS_SQL”, line 1199
    ORA-06512: at “RMS13.LOAD_CSV”, line 52
    ORA-06512: at line 10
    00984. 00000 – “column not allowed here”
    *Cause:
    *Action:

    Error is being generated in the below line

    dbms_sql.parse( l_theCursor, ‘insert into ‘ || p_table || ‘(‘ || l_cnames || ‘) values (‘ || l_bindvars || ‘)’, dbms_sql.native );

    I tried changing it to below

    dbms_sql.parse( l_theCursor, ‘insert into ‘ || p_table || ‘(‘ || l_cnames || ‘) values (‘ || ‘l_bindvars’ || ‘)’, dbms_sql.native );

    i.e I have enclosed l_bindvars within quotes as ‘l_bindvars’.
    But then, I am getting the below error

    Error report –
    ORA-00947: not enough values
    ORA-06512: at “SYS.DBMS_SQL”, line 1199
    ORA-06512: at “RMS13.LOAD_CSV”, line 52
    ORA-06512: at line 10
    00947. 00000 – “not enough values”
    *Cause:
    *Action:

    Can u please suggest what is going wrong here?

    Thanks in advance

    • So are you having any problem during the following this process which is been told ?
      If followed all the steps then it should not be a problem , but still feel free to reach out to me

  16. To everyone,

    Check if you are able to write the file into the directory or not .
    Sample code to check is as below:

    DECLARE
    vsfile UTL_FILE.file_type;
    BEGIN
    vsfile := UTL_FILE.fopen(‘MY_DIR’, ‘sample.csv’, ‘r’);
    END;

  17. Hi,
    Does this process work form loading large csv valued files? For example, would this process work for loading a csv file with 6 columns, two of which > 32767 characters?

    Thanks.

    • Sorry I personally didn’t tried it but give a try and let me know what kind of issues you are facing during load, so that we can check if we really need to modify similarly for date and timestamp what we are doing.

      Thanks,
      Sloba

      • I believe that we have to play around :

        The below codes to support clob columns similarly what is done for date and timestamp :
        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”),’ else ‘:b’|| l_cnt || ‘,’ end;

        l_cnt := l_cnt + 1;
        end loop;

    • If you can share some sample file with one table structure to my email address then I can check the same and revert back with an approach. Not less Sir Sean D. Stuber can also provide his feedback for the same.

  18. Hi Solba ,

    Today I encountered a problem where I asked to do insert the record from a flat file which is semi colon “;” formatted . I made a plsql procedure for this but problem is that while running procedure if insertion get failed for 3rd line in the file to data base then remaining records get skipped . But I need to insert the records for whole file and have to make a report with data how many records have been failed and how many have been succeed.

    The code of my program is below and tell me how to insert the records even if any failures occurs in mid of the file .

    Thanks in advance .
    Saurabh

    CREATE OR REPLACE PROCEDURE FNSONLF.BOSD_INSERT IS
    SM UTL_FILE.FILE_TYPE;
    WA_LINE VARCHAR2 (32767);
    WA_SOC_NO BOSD.SOC_NO%TYPE;
    WA_TRAN_DATE BOSD.TRAN_DATE%TYPE;
    WA_ID_TYPE BOSD.ID_TYPE%TYPE;
    WA_ID_NUMBER BOSD.ID_NUMBER%TYPE;
    WA_ACCT_NO BOSD.ACCT_NO%TYPE;
    WA_JRN_NO BOSD.JRN_NO%TYPE;
    WA_TIP_REG BOSD.TIP_REG%TYPE;
    WA_SISTEMA BOSD.SISTEMA%TYPE;
    WA_BRANCH BOSD.BRANCH%TYPE;
    WA_SUB_CTA_CONT BOSD.SUB_CTA_CONT%TYPE;
    WA_CTACON BOSD.CTACON%TYPE;
    WA_TIP_CTA BOSD.TIP_CTA%TYPE;
    WA_TYPE BOSD.TYPE%TYPE;
    WA_INT_CAT BOSD.INT_CAT%TYPE;
    WA_OPEN_DT BOSD.OPEN_DT%TYPE;
    WA_ACT_STAT BOSD.ACT_STAT%TYPE;
    WA_PLAZO BOSD.PLAZO%TYPE;
    WA_TYPE_AUT BOSD.TYPE_AUT%TYPE;
    WA_AUT_NO BOSD.AUT_NO%TYPE;
    WA_AUT_DT BOSD.AUT_DT%TYPE;
    WA_ID_FID BOSD.ID_FID%TYPE;
    WA_AMT_EFECZ BOSD.AMT_EFECZ%TYPE;
    WA_AMT_RETEN BOSD.AMT_RETEN%TYPE;
    WA_AMT_CHEQ BOSD.AMT_CHEQ%TYPE;
    WA_AMT_TOTAL BOSD.AMT_TOTAL%TYPE;
    WA_STATUS_REG BOSD.STATUS_REG%TYPE;
    WA_OBSERVACION BOSD.OBSERVACION%TYPE;
    WA_TELL_NO BOSD.TELL_NO%TYPE;
    WA_TELL_UPD_NO BOSD.TELL_UPD_NO%TYPE;
    WA_DATE_IN BOSD.DATE_IN%TYPE;
    WA_DATE_UPD BOSD.DATE_UPD%TYPE;
    WA_STATUS_CONCIL BOSD.STATUS_CONCIL%TYPE;

    WS_OPEN_DT VARCHAR2(10);
    WS_TRAN_DATE VARCHAR2(10);
    –WA_COUNT NUMBER := 0;

    BEGIN
    —-RUTAS /fns/fd/r/spool/Interfaces/ORGANIS-EXTRACTS
    SM := UTL_FILE.FOPEN (‘RUTAS’,’BOSD.TXT’,’R’,32767);
    IF UTL_FILE.IS_OPEN(SM) THEN
    LOOP
    BEGIN
    — SAVEPOINT startTransaction;
    UTL_FILE.GET_LINE(SM, WA_LINE, 32767);
    IF WA_LINE IS NULL THEN
    EXIT;
    END IF;
    WA_SOC_NO := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,1) ;
    WS_TRAN_DATE := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,2) ;
    WA_TRAN_DATE := TO_NUMBER(TO_CHAR(TO_DATE(WS_TRAN_DATE,’DDMMYYYY’),’J’) – 2415020);
    WA_ID_TYPE := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,3) ;
    WA_ID_NUMBER := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,4) ;
    WA_ACCT_NO := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,5) ;
    WA_JRN_NO := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,6) ;
    WA_TIP_REG := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,7) ;
    WA_SISTEMA := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,8) ;
    WA_BRANCH := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,9) ;
    WA_CTACON := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,10);
    WA_SUB_CTA_CONT := SUBSTR(WA_CTACON,1,6);
    WA_TIP_CTA := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,11);
    WA_TYPE := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,12);
    WA_INT_CAT := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,13);
    WS_OPEN_DT := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,14);
    WA_OPEN_DT := TO_NUMBER(TO_CHAR(TO_DATE(WS_OPEN_DT,’DDMMYYYY’),’J’) – 2415020);
    WA_ACT_STAT := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,15);
    WA_PLAZO := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,16);
    WA_TYPE_AUT := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,17);
    WA_AUT_NO := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,18);
    WA_AUT_DT := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,19);
    WA_ID_FID := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,20);
    WA_AMT_EFECZ := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,21);
    WA_AMT_RETEN := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,22);
    WA_AMT_CHEQ := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,23);
    WA_AMT_TOTAL := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,24);
    WA_STATUS_REG := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,25);
    WA_OBSERVACION := ‘ ‘;
    WA_TELL_NO := ‘BOSD_INSERT’;
    WA_TELL_UPD_NO := ‘ ‘;
    WA_DATE_IN := TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,’DDMMYYYY’),’DDMMYYYY’),’J’)-2415020 ;
    WA_DATE_UPD := 0;
    WA_STATUS_CONCIL := ’00’;
    –WA_OBSERVACION := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,27);
    –WA_TELL_NO := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,28);
    –WA_TELL_UPD_NO := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,29);
    –WA_DATE_IN := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,30);
    –WA_DATE_UPD := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’, 1,31);
    –WA_STATUS_CONCIL := REGEXP_SUBSTR(WA_LINE, ‘[^;]+’,1,32);
    INSERT INTO BOSD_SAN VALUES( WA_SOC_NO ,
    WA_TRAN_DATE ,
    WA_ID_TYPE ,
    WA_ID_NUMBER ,
    WA_ACCT_NO ,
    WA_JRN_NO ,
    WA_TIP_REG ,
    WA_SISTEMA ,
    WA_BRANCH ,
    WA_SUB_CTA_CONT ,
    WA_CTACON ,
    WA_TIP_CTA ,
    WA_TYPE ,
    WA_INT_CAT ,
    WA_OPEN_DT ,
    WA_ACT_STAT ,
    WA_PLAZO ,
    WA_TYPE_AUT ,
    WA_AUT_NO ,
    WA_AUT_DT ,
    WA_ID_FID ,
    WA_AMT_EFECZ ,
    WA_AMT_RETEN ,
    WA_AMT_CHEQ ,
    WA_AMT_TOTAL ,
    WA_STATUS_REG ,
    WA_OBSERVACION ,
    WA_TELL_NO ,
    WA_TELL_UPD_NO ,
    WA_DATE_IN ,
    WA_DATE_UPD ,
    WA_STATUS_CONCIL );
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    — ROLLBACK TO startTransaction;
    exit;
    END;
    END LOOP;
    END IF;
    UTL_FILE.FCLOSE(SM);
    END BOSD_INSERT;
    /

  19. Hi Sloba

    I am having an issue. This is similar to the issue which Bala has posted in the comments
    But in Bala post he mentioned that the CSV will be inserting into the target table(All the columns with the csv file matches with taget table columns) but for me it’s not the case. My CSV file columns are different from target table, only two columns are same those are needed to be inserted into it based on business rules.
    can anyone of you guys please help me out

    • CSV FILE:

      CONTRACT # |UNIT # | SO# |STS| CREATE DT |ORIG SCHDT|FROM TIM|TO TIME| CURR SCHDT
      ————————————————————————————————————
      190857332/863092 |0004766|40515850|AT|2014-09-30|2014-10-06|01:00 PM|05:00 PM|2014-11-06
      234703742/946261 |0004766|40516256|CO|2014-10-01|2014-10-29|08:00 AM|05:00 PM|2014-11-20
      237750152.942581 |0004766|40517579|WA|2014-10-06|2014-10-24|08:00 AM|05:00 PM|2014-11-08
      241321692/946451 |0004766|40518852|WS|2014-10-08|2014-10-10|10:00 AM|02:00 PM|2014-11-07
      241857122\804591 |0004766|40520931|PA|2014-10-14|2014-10-18|03:00 PM|07:00 PM|2014-11-13
      ,242259512/862912 |0004766|40520951|CA|2014-10-14|2014-11-06|01:00 PM|05:00 PM|2014-11-06
      243634072/927191 |0004766|40522035|AT|2014-10-16|2014-10-22|08:00 AM|12:00 PM|2014-11-05
      243624732/946021 |0004766|40522595|ED|2014-10-17|2014-10-30|08:00 AM|12:00 PM|2014-11-06
      243744882/804511 |0004766|40522596|AT|2014-10-17|2014-10-30|08:00 AM|12:00 PM|2014-11-06
      243730262/946241 |0004766|40523117|NH|2014-10-20|2014-11-04|01:00 PM|05:00 PM|2014-11-10
      243731082/946241 |0004766|40524296|CA|2014-10-22|2014-10-27|10:00 AM|02:00 PM|2014-11-06
      243731642\946271 |0004766|40524641|O |2014-10-22|2014-11-01|10:00 AM|02:00 PM|2014-11-12
      243731842/946271 |0004766|40524856|CA|2014-10-23|2014-10-30|08:00 AM|12:00 PM|2014-11-12
      243730272/946271 |0004766|40525126|CA|2014-10-24|2014-10-29|08:00 AM|12:00 PM|2014-11-05

      This is the CSV file which we will be getting everyday
      AS you can see in this file the first column CONTRACT # has a value which is seperated by some delimiters
      for example (190857332/863092) this value is combination of dispatch_id(190857332) and vendor_id(863092)
      and for this the STS(status) is given as AT.

      And my taget table structure is
      dispatch_status_id NUMBER(20) not null,
      dispatch_id NUMBER,
      status_code VARCHAR2(10),
      status_prefix VARCHAR2(10),
      status_timestamp DATE,
      application_code VARCHAR2(10),
      application_prefix VARCHAR2(10),
      last_modified DATE not null,
      last_modified_by VARCHAR2(31)

      Here dispatch_status_id is generated automatically

      And there is one more table called work_order where i need to check the condition like the dispacth id and vendor id from CSV file are present in this table are not

      WORK_ORDER table structure
      dispatch_id NUMBER(20) not null,
      work_order_id NUMBER(20),
      vendor_id NUMBER(20),
      commercial_property_id NUMBER(20),
      user_id NUMBER(20) not null,
      svc_fee_amount NUMBER(10,2),
      stated_svc_fee_collected NUMBER(10,2),
      svc_fee_collected NUMBER(10,2),
      svc_fee_paid_by_type_cd VARCHAR2(10),
      svc_fee_paid_by_type_prefix VARCHAR2(10),
      svc_fee_paid_by_nbr VARCHAR2(20),
      svc_fee_source_code VARCHAR2(10),
      svc_fee_source_prefix VARCHAR2(10),
      dispatch_by_type_cd VARCHAR2(10),
      dispatch_by_type_prefix VARCHAR2(10),
      dispatch_type_cd VARCHAR2(10),
      dispatch_type_prefix VARCHAR2(10),
      reason_type_code VARCHAR2(10),
      reason_type_prefix VARCHAR2(10),
      preferred_vendor_ind CHAR(1),
      dispatch_delay_counter NUMBER(3),
      dispatch_timestamp DATE,
      appointment_timestamp DATE,
      authorized_grand_total NUMBER(7,2),
      authorized_labor_to_date NUMBER(7,2),
      total_work_time VARCHAR2(10),
      service_date DATE,
      work_description VARCHAR2(80),
      invoice_gross_amount NUMBER(10,2),
      last_modified DATE not null,
      last_modified_by VARCHAR2(31),
      checkout_ind CHAR(1) default ( ‘N’ ) not null,
      hold_until_timestamp DATE,
      originating_dispatch_id NUMBER(20),
      dispatch_user_id NUMBER(20)

      Now the business rule says that
      If the Dispatch id and vendor id (Contract #) is present in the work_order then insert that record in target table
      If it is not then put in a log file

      • Your data file is not CSV format at first 🙂 … this is a PIPE delimited file which should be saved as .txt file.

        Better you can use external file query to access the flat file on your server and later you can write any business logic to load the data into the master table.

        Thanks,
        Sloba

    • While doing any integration on flat files we should always consider the business logic that might come in future. The post which I mentioned was just an example to load the file into the staging table not directly to the master table where any business logic can be implemeted.

      So if you are using the CSV load function which I have posted then I would suggest you to load the flat file into a staging table first and while moving the data into the master table from staging table you can implement all business logic you want to.

      Hope I cleared your doubt.

      Thanks,
      Sloba

  20. Connected.
    SQL> DECLARE
    2 p_table VARCHAR2(200) := ‘TEMP’ ;
    3 p_dir VARCHAR2(200) := ‘ORA_DIR’;
    4 p_filename VARCHAR2(200) := ‘sej.csv’;
    5 v_return NUMBER;
    6 BEGIN
    7 v_return := LOAD_CSV(‘TEMP’,’ORA_DIR’,’sej.csv’,’1′,””);
    8
    9 dbms_output.put_line(‘v_return =’ || v_return);
    10 END;
    11 /
    DECLARE
    *
    ERROR at line 1:
    ORA-00984: column not allowed here
    ORA-06512: at “SYS.DBMS_SQL”, line 1199
    ORA-06512: at “SPACC.LOAD_CSV”, line 82
    ORA-06512: at line 7

    I am getting above error while executing.

    • Try like this and check :

      DECLARE
      p_table VARCHAR2(200) := ‘TEMP’;
      p_dir VARCHAR2(200) := ‘ORA_DIR’;
      p_filename VARCHAR2(200) := ‘sej.csv’;
      v_return NUMBER;
      BEGIN
      v_return := LOAD_CSV(p_table => p_table
      ,p_dir => p_dir
      ,p_filename => p_filename
      );

      dbms_output.put_line(‘v_return = ‘ || v_return);
      END;

  21. I used your example, table ‘temp.csv’
    COL1 COL2 COL3
    1 AA 1-Jan-12
    2 BB12 2-Feb-12
    3 CCcc11 22-Aug-12
    4 DD 22-Aug-12

  22. I think I know what the mistake is.
    We have: l_cnames := rtrim(l_cnames,’,’);
    So, when we use the insert we have a comma at the end of the last column name. I guess that’s why we have the error at the parse. Because the syntax seems to be right.

  23. Hi Sloba,
    I am new to the PL/SQL coding and my requirement is to dump 7 CSV files from remote location in to the 7 oracle tables and before that I need to truncate those tables and load the data everyday .Can you please help me on this .
    Apprciate your help

  24. Hi Sloba,

    if a csv file size is over 30GB, how to import it to Oracle database? for the technologies that you mentioned, is that any bottleneck on each of process

  25. hi frnds,

    I want to load a data from excel file to oracle table by using utl_file in procedure…how can i do it..?
    Thanks in advance….

  26. Hi,

    I am pretty new to PLSQL but unfortunately i have to do data load from .csv to table. I have gone through the above blog and discussions. look like it can be possible. however my requirement is bit different. much appreciate if could help me.
    I will be having 2 tables as follows.
    Table1.: have source and target details like — Id, source file name , Target table name

    id sourcefilename targettable
    — ——————— —————–
    1 epm.csv salestable
    2 currency.csv regiontable

    Table2 : coulmn mappings details like Id, sourcecolumn (from file), target column (from table).

    id sourccol trgtcol
    — ———— ———-
    1 ename name
    1 esal pay
    1 eid id
    2 country Place
    2 currency money

    Package should be like while executing package i will supply parameter (ID). based on ID it should pick source filename and target table from Table1 and should be mapped based on Table2 mppings and load into table.
    NOTE: In Table 2 mapping table my source coulumn structure is not same as my target structure and column names were different.

  27. Hi,

    I am very new to PLSQL unfortunately i need to fulfill requirement of loading .csv file into table using plsql. I have gone through above blog and discussions look like it can be possible. however my requirement is bit different.
    Much appreciate if someone help on it
    I will be having 2 tables as follows.
    Table1.: have source and target details like — Id, source file name , Target table name

    id sourcefilename targettable
    — ——————— —————–
    1 epm.csv salestable
    2 currency.csv regiontable

    Table2 : coulmn mappings details like Id, sourcecolumn (from file), target column (from table).

    id sourccol trgtcol
    — ———— ———-
    1 ename name
    1 esal pay
    1 eid id
    2 country Place
    2 currency money

    Package should be like while executing package i will supply parameter (ID). based on ID it should pick source filename and target table from Table1 and should be mapped based on Table2 mppings and load into table.
    NOTE: In Table 2 mapping table my source coulumn structure is not same as my target structure and column names were different.

    • My apologies for the delayed response.
      First to read the file names and mapping can be done by writing a JAVA program and calling it from PLSQL.
      Once you are able to read the file name you know what should be the target table where you want to insert the rows. Now if the mapping is different then you should make use of staging table.
      First read the file load it into the staging table , and finally you load to the target table by selecting specific columns. It normally doesn’t matter if the columns are not in order or name. But make sure the data type and the format should match else you have to handle in your code.

      Thanks,
      Swadhin (Sloba)

  28. then ‘to_date(:b’ || l_cnt || ‘,”YYYY-MM-DD HH24:MI:SS”)

    THEN ‘TO_DATE(:b’ || l_cnt || q'[,’YYYY-MM-DD HH24:MI:SS’),]’

  29. hi sloba,

    When i try to run the procedure, I am getting error as “ORA-14551: cannot perform a DML operation inside a query”.

    Please suggest.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s