Oracle - .CSV file to multiple tables at once

Question

I have to load .CSV file in to my Oracle Tables. but the thing is that the single CSV file will have data for multiple Tables. The trick is we need to identify the first column data to insert into specific table. i.e. if first column has value '16' than the whole row should be inserted in to TABLE_16 which will have 16 Columns, if the value is 21 the TABLE_21 will have 21 Columns and so on. One more thing to take care is, my CSV file will have millions of records, so I need to consider performance of the process too, So I think BULK COLLECT and FORALL will be the best approach to insert data speedy.

When I try to run the following block I am getting this error :

01403. 00000 - "no data found" *Cause: No data was found from the objects. *Action: There was no data from the objects which may be due to end of fetch.

---

Sample .CSV Data

16,"Laura","Bissot","LBISSOT","650.124.5234",20-08-05,"ST_CLERK",3300,,121,50,"aaa",234,"asdf","ssedf","wsdrftd"
21,"Mozhe","Atkinson","MATKINSO","650.124.6234",30-10-05,"ST_CLERK",2800,,121,50,"aaa",234,"asdf","ssedf","wsdrftd","aaa",234,"asdf","ssedf","wsdrftd"
11,"James","Marlow","JAMRLOW","650.124.7234",16-02-05,"ST_CLERK",2500,,121,50
16,"TJ","Olson","TJOLSON","650.124.8234",10-04-07,"ST_CLERK",2100,,121,50,"aaa",234,"asdf","ssedf","wsdrftd"
19,"Jason","Mallin","JMALLIN","650.127.1934",14-06-04,"ST_CLERK",3300,,122,50,"aaa",234,"asdf","ssedf","wsdrftd","aaa",234,"asdf",
12,"Michael","Rogers","MROGERS","650.127.1834",26-08-06,"ST_CLERK",2900,,122,50,"aaa"
14,"Ki","Gee","KGEE","650.127.1734",12-12-07,"ST_CLERK",2400,,122,50,"aaa",234,"asdf"
30,"Ki","Gee","KGEE","650.127.1734",12-12-07,"ST_CLERK",2400,,122,50,"aaa",234,"asdf",11,"dd",23,43,789,9086,"1DRFtf","PST","RTF%$",123,"dsda",5656,"dsed",123,4333,112

---

create or replace type 
T_CSV_DATA as object 
(c001 varchar2(50),c002 varchar2(150),c003 varchar2(150),c004 varchar2(150),c005 varchar2(150),c006 varchar2(150),c007 varchar2(150),c008 varchar2(150),c009 varchar2(150), c010 varchar2(150), 
c011 varchar2(150),c012 varchar2(150),c013 varchar2(150),c014 varchar2(150),c015 varchar2(150),c016 varchar2(150),c017 varchar2(150),c018 varchar2(150),c019 varchar2(150), c020 varchar2(150), 
c021 varchar2(150),c022 varchar2(150),c023 varchar2(150),c024 varchar2(150),c025 varchar2(150),c026 varchar2(150),c027 varchar2(150),c028 varchar2(150),c029 varchar2(150), c030 varchar2(150));

create or replace type T_CSV_VAL as table of T_CSV_DATA;

DECLARE
 --variables to do with the copying the blob into a clob
    v_blob              BLOB;
    v_clob              CLOB;
    v_dest_offset       INTEGER := 1;
    v_src_offset        INTEGER := 1;
    v_lang_context      INTEGER := dbms_lob.default_lang_ctx;
    v_warning           INTEGER;
 --variables to do with iterating over each row of the clob
    v_new_line_pos      NUMBER;
    v_start_pos         NUMBER := 1;
    v_current_line      VARCHAR2(4000);
    v_total_len         NUMBER;
    v_curr_row          apex_application_global.vc_arr2;
    V_DATA_ASSIGN       T_CSV_VAL :=T_CSV_VAL();
    V_BULK_DATA         T_CSV_VAL :=T_CSV_VAL();
BEGIN --t_csv_line
    SELECT FILE_BLOB INTO v_blob FROM  FILE_UPLOAD  WHERE ID=7;

    dbms_lob.createtemporary(v_clob,true);
    dbms_lob.converttoclob(dest_lob => v_clob,src_blob => v_blob,amount => dbms_lob.lobmaxsize,dest_offset => v_dest_offset,src_offset
    => v_src_offset,blob_csid => dbms_lob.default_csid,lang_context => v_lang_context,warning => v_warning);

    v_total_len := dbms_lob.getlength(v_clob);
    WHILE ( v_start_pos <= v_total_len ) LOOP
        v_new_line_pos := instr(v_clob,chr(10),v_start_pos);
        IF v_new_line_pos = 0 THEN
            v_new_line_pos := v_total_len + 1;
        END IF;
        v_current_line := substr(v_clob,v_start_pos,v_new_line_pos - v_start_pos);
        v_curr_row := apex_util.string_to_table(v_current_line,',');

            V_DATA_ASSIGN.EXTEND;
            V_DATA_ASSIGN(V_DATA_ASSIGN.count) := T_CSV_DATA(v_curr_row(1),v_curr_row(2),v_curr_row(3),v_curr_row(4),v_curr_row(5),
                                                             v_curr_row(6),v_curr_row(7),v_curr_row(8),v_curr_row(9),v_curr_row(10),
                                                             v_curr_row(11),v_curr_row(12),v_curr_row(13),v_curr_row(14),v_curr_row(15),
                                                             v_curr_row(16),v_curr_row(17),v_curr_row(18),v_curr_row(19),v_curr_row(20),
                                                             v_curr_row(21),v_curr_row(22),v_curr_row(23),v_curr_row(24),v_curr_row(25),
                                                             v_curr_row(26),v_curr_row(27),v_curr_row(28),v_curr_row(29),v_curr_row(30));
            v_start_pos := v_new_line_pos + 1;
    END LOOP;

    FOR rec IN V_DATA_ASSIGN.first..V_DATA_ASSIGN.last LOOP
        IF V_DATA_ASSIGN(rec).c001 = 16 THEN -- If first value is 16, then insert into TABLE_16(will have 16 columns).
            INSERT INTO TABLE_16.....
        ELSIF  V_DATA_ASSIGN(rec).c001 = 21 THEN -- If first value is 21, then insert into TABLE_21(will have 21 columns).
            INSERT INTO TABLE_21.....
        ELSIF  V_DATA_ASSIGN(rec).c001 = 11 THEN -- If first value is 11, then insert into TABLE_11(will have 11 columns).
            INSERT INTO TABLE_11.....
        ...
            ...
        ELSIF  V_DATA_ASSIGN(rec).c001 = 30 THEN -- If first value is 30, then insert into TABLE_30(will have 30 columns). and so on...
            INSERT INTO TABLE_30.....       
        END IF;
    END LOOP;
END;

Answer 1

In my opinion, you're on a wrong path. PL/SQL you're inclined to can't beat SQL*Loader, especially when you enable direct path and parallel execution.

Here's a simple example which shows how to do that.

Creating tables: I'm too lazy to create tables with 16 or 21 columns, so I use 4 (t1) and 5 (t1) instead.

SQL> create table t1 (id number, fname varchar2(20), lname varchar2(20), salary number);

Table created.

SQL> create table t2 (id number, fname varchar2(20), lname varchar2(20), salary number,hiredate date);

Table created.

Control file:

options (direct=true, parallel=true)
load data 
infile *

into table t1
  append
  when (1) = '4'
  fields terminated by ',' optionally enclosed by '"'
  trailing nullcols  
  (
  id position(1) integer external, 
  fname char,
  lname char,
  salary integer external
  )

into table t2
  append
  when (1) = '5'
  fields terminated by ',' optionally enclosed by '"'
  trailing nullcols
  (
  id position (1) integer external, 
  fname char,
  lname char,
  salary integer external,
  hiredate "to_date(:hiredate, 'dd-mm-rr')"
  )

begindata
4,"Laura","Bissot",6506,14-06-04
5,"Mozhe","Atkinson",1202,20-08-05
5,"James","Marlow",1244,30-10-05
4,"TJ","Olson",4345,16-02-05

Loading session & the result:

SQL> $sqlldr scott/tiger control=test05.ctl log=test05.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon Kol 27 15:21:17 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 4.

SQL> select * From t1;

        ID FNAME                LNAME                    SALARY
---------- -------------------- -------------------- ----------
         4 Laura                Bissot                     6506
         4 TJ                   Olson                      4345

SQL> select * From t2;

        ID FNAME                LNAME                    SALARY HIREDATE
---------- -------------------- -------------------- ---------- ----------
         5 Mozhe                Atkinson                   1202 20/08/2005
         5 James                Marlow                     1244 30/10/2005

SQL>

Answer 2

Simplest solution I think would be to first dump all the data into PRE-Staging table using Shell Script/SQL Loader. Once the data is dumped into pre-staging, then you can write a PLSQL block/package to insert the specific rows into required tables.

In the PLSQL block, you can use bulk collect to maximise your performance.

Thanks, Idrees

Answer 3

I agree with @Littlefoot SQL-Loader is the natural and fast way.
An alternative is using Oracle's "EXTERNAL TABLES".

With this you can use SELECT statement over files residing on the file system of your operating system(OS).
For example text files with CSV format.

Book : Database Utilities Chaepter: External Tables https://docs.oracle.com/cd/E11882_01/server.112/e22490/part_et.htm#i436567
The external tables feature is a complement to existing SQLLoader functionality. It enables you to access data in external sources as if it were in a table in the database.
Note that SQLLoader may be the better choice in data loading situations that require additional indexing of the staging table.

Basic steps:

1-Create a subdirectory in file system of the operating system (OS) where your Oracle instance is running.
For example: If OS is Windows, then create a subdirectory "IN_FILES" inside subdirectory "DATA" on unit "C:"

If OS is Linux/Unix flavour then create a subdirectory "in_files" inside subdirectory "data" on root.

2-On the OS grant read and write privileges for the subsirectory step (1) for the OS user who is running the Oracle instance.

3-On Oracle create a directory object
You use the full path according file system's rules.
If OS is Windows, then full path is something like "C:\DATA\IN_FILES"

Create directory external_info as 'C:\DATA\IN_FILES'

If OS is Linux/Unix flavour then full path is something like "/data/in_files"

Create directory external_info as '/data/in_files'

4-On Oracle grant read/write for the directory object of step 3 to the PUBLIC.

grant read, write on directory EXTERNAL_INFO to public;

5-Create an external table to access the CSV file:

In this example, the file is:
a.CSV format
b.End-of-record is CARRIAGE_RETURN following by LINE_FEED (records delimited by '\r\n')
c.First record is heading column names, (skip 1)
d.Fields separator is comma (fields terminated by ',')
e.Data could be enclosed by quotes (ASCII character 34) ( optionally enclosed by '"')
f.Missing fields will have null values (missing field values are null)
g.The file name is "data01.csv", this name is according OS rules
RemenberWINDOWS is case insensitive but LINUX/Unix is case sensitive.

create table data01_external
  (id       number,
   fname    varchar2(20),
   lname    varchar2(20),
   salary   number,
   hiredate date
  )
  organization external
  (type oracle_loader
   default directory external_info
   access parameters (records delimited by '\r\n'
                      badfile     'data01_%p.bad'
                      discardfile 'data01_%p.dis'
                      logfile     'data01_%p.log'
                      skip 1
                      fields terminated by ','
                             optionally enclosed by '"'
                             missing field values are null
                      (id        integer external,
                       fname     char,
                       lname     char,
                       salary    decimal external,
                       hiredate  char  date_format date mask 'dd-mm-rr'
                      )
                     )
   location ('data01.csv')
  )
  reject limit UNLIMITED;

6-Now you can write a PL/SQL package to insert data to every table
For this example, I use @Littlefoot's tables T1 and T2:

Create or replace package pk_load_info
  is
    procedure pr_load(p_isbFile_name     varchar2,
                      p_onuErrCode   out number,
                      p_osbErrDesc   out varchar2
                     );
End;
/


Create or replace package body pk_load_info
is
  procedure pr_load(p_isbFile_name     varchar2,
                    p_onuErrCode   out number,
                    p_osbErrDesc    out varchar2
                   )
  is
    sbEvent    varchar2(20);
    sbSentence varchar2(200);
  Begin
    p_onuErrCode:=0;
    p_osbErrDesc:=null;
    --
    if trim(p_isbFile_name) is null then
       p_onuErrCode:=101;
       p_osbErrDesc:='The name of the file is not to be null';
       return;
    End if;
    --
    -- you can use always the same file name of maybe use different file name in every run.
    sbEvent:='alter table';
    sbSentence:='alter table data01_external default directory EXTERNAL_INFO location ('||chr(39)||trim(p_isbFile_name)||chr(39)||')';
    Dbms_Output.Put_Line('sbSentence='||sbSentence);
    execute immediate sbSentence;
    --
    -- hint "append", in direct-path INSERT, data is appended to the end of the table
    sbEvent:='insert table T1';
    insert /*+ append */ into t1
    select a.id,
           a.fname,
           a.lname,
           a.salary
    from data01_external a
    where a.id=4;
    --
    sbEvent:='insert table T2';
    insert /*+ append */ into t2
    select a.id,
           a.fname,
           a.lname,
           a.salary,
           hiredate
    from data01_external a
    where a.id=5;
  Exception
    when others then
         p_onuErrCode:=sqlcode;
         p_osbErrDesc:='Event "'||sbEvent||'" '||sqlerrm;
  End pr_load;
End pk_load_info;
/

7-Copy file "data02.csv" to the subdirectory of step (1)
Data is:

ID,FNAME,LNAME,SALARY,HIREDATE
4,"Laura","Bissot",6506
5,"Mozhe","Atkinson",1202,20-08-05
5,"James","Marlow",1244,30-10-05
4,"TJ","Olson",4345

8-Test

Declare
  nuErrcode      number;
  sbErrdesc      varchar2(2000);
  Procedure print(p_isbTexto varchar2)
  is
  Begin
    if nvl(length(p_isbTexto),0)<=255 then
       dbms_output.put_line(p_isbTexto);
    Else
       dbms_output.put_line(substr(p_isbTexto,1,254)||'¬');
       dbms_output.put_line(substr(p_isbTexto,255,255));
    End if;
  End print;
Begin
  print(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff4')||'|Begin');
  dbms_application_info.set_module('SQL','Inicio');
  --
  pk_load_info.pr_load('data02.csv',
                       nuErrcode,
                       sbErrdesc
                      );
  print('nuErrcode='||nuErrcode);
  print('sbErrdesc='||sbErrdesc);
  --
  if nuErrcode=0 then
     commit;
  Else
     rollback;
  end if;
  print(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff4')||'|End');
  dbms_application_info.set_action('Fin');
End;
/

9-Review data

select *
from t1;
ID|FNAME|LNAME |SALARY|
 4|Laura|Bissot|6506  |
 4|TJ   |Olson |4345  |

select *
from t2;
ID|FNAME|LNAME   |SALARY|HIREDATE           |
5 |Mozhe|Atkinson|  1202|2005-08-20 00:00:00|
5 |James|Marlow  |  1244|2005-10-30 00:00:00|

bye