Oracle PL/SQL/Table/CSV Table

Create table based on csv file with "nobadfile"

   <source lang="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         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>

      </source>
   
  

Create table for csv file with badfile option

   <source lang="sql">

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>

      </source>
   
  

Create table from external csv file

   <source lang="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         fields terminated by ",")
11       location ("teacher.csv")
12     )
13     reject limit unlimited;

Table created. SQL> SQL>

      </source>
   
  

Grant all on directory then load the csv file

   <source lang="sql">

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;

</source>
   
  

Use CSV file as External table

   <source lang="sql">

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 /

</source>