Oracle PL/SQL/Table/CSV Table

Create table based on csv file with "nobadfile"

SQL> create table teachers_ext (
  2        first_name     varchar2(15),
  3        last_name      varchar2(15),
  4        phone_number   varchar2(12)
  5      )
  6      organization external (
  7        type oracle_loader
  8        default directory ext_data_files
  9        access parameters (
 10         nobadfile
 11         fields terminated by ",")
 12       location ("teacher.csv")
 13     )
 14     reject limit unlimited
 15     /
Table created.
SQL>
SQL>
SQL> drop table teachers_ext;
Table dropped.
SQL>

Create table for csv file with badfile option

SQL>
SQL>
SQL>  create table teachers_ext (
  2        first_name     varchar2(15),
  3        last_name      varchar2(15),
  4        phone_number   varchar2(12)
  5      )
  6      organization external (
  7        type oracle_loader
  8        default directory ext_data_files
  9         access parameters (
 10         records delimited by newlines
 11         badfile ext_data_files:"teacher.bad"
 12         fields terminated by ",")
 13       location ("teacher.csv")
 14     )
 15     reject limit unlimited;
Table created.
SQL>
SQL> drop table teachers_ext;
Table dropped.
SQL>

Create table from external csv file

SQL>       create table teachers_ext (
  2        first_name     varchar2(15),
  3        last_name      varchar2(15),
  4        phone_number   varchar2(12)
  5      )
  6      organization external (
  7        type oracle_loader
  8        default directory ext_data_files
  9        access parameters (
 10         fields terminated by ",")
 11       location ("teacher.csv")
 12     )
 13     reject limit unlimited;
Table created.
SQL>
SQL>

Grant all on directory then load the csv file

 
grant all on directory ext_data_files to scott;
create table teachers_ext (
  first_name     varchar2(15),
  last_name      varchar2(15),
  phone_number   varchar2(12)
)
organization external (
  type oracle_loader
  default directory ext_data_files
   access parameters (
    records delimited by newlines
    badfile ext_data_files:"teacher.bad"
    fields terminated by ",")
  location ("teacher.csv")
)
reject limit unlimited;

Use CSV file as External table

 
create directory ext_data_files as "C:\";
create table teachers_ext (
      first_name     varchar2(15),
      last_name      varchar2(15),
      phone_number   varchar2(12)
)
organization external (
  type oracle_loader
  default directory ext_data_files
  access parameters (fields terminated by "," )
  location ("teacher.csv")
)
reject limit unlimited
/

select first_name ||" "|| last_name "Name", phone_number "Phone"
from teachers_ext
order by last_name
/