Oracle PL/SQL/Table/CSV Table — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 12:54, 26 мая 2010
Содержание
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>