目录和外部表是Oracle数据仓库中获取外部文件数据一个比较重要的管理.

创建外部目录并赋予相应的读写权限

SQL>CREATE OR REPLACE DIRECTORY source_dir as 'C:/UserDefine/oracle/oradata/source';

Directory created.

SQL>CREATE OR REPLACE DIRECTORY target_dir as 'C:/UserDefine/oracle/oradata/target';

Directory created.

SQL>CREATE OR REPLACE DIRECTORY log_dir as 'C:/UserDefine/oracle/oradata/log';

Directory created.

SQL>grant read on directory source_dir to wbq;

Grant succeeded.

SQL>grant write on directory target_dir to wbq;

Grant succeeded.

SQL>grant write on directory log_dir to wbq;

Grant succeeded.

在源目录中创建并写入一个文件

declare
fhandle utl_file.file_type;
begin
fhandle := utl_file.fopen('SOURCE_DIR', 'example.txt', 'w');
utl_file.put_line(fhandle , 'wbq test write one');
utl_file.put_line(fhandle , 'wbq test write two');
utl_file.fclose(fhandle);
end;

从源目录中读取一个文件

declare
fhandle utl_file.file_type;
fp_buffer varchar2(4000);
begin
fhandle := utl_file.fopen ('SOURCE_DIR','example.txt', 'R');
utl_file.get_line (fhandle , fp_buffer );
dbms_output.put_line(fp_buffer );
utl_file.get_line (fhandle , fp_buffer );
dbms_output.put_line(fp_buffer );
utl_file.fclose(fhandle);
end;

通过数据字典查看目录

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

------------------------------ ------------------------------ -------------

SYS MEDIA_DIR C:/UserDefine/oracle/ora92/demo/schema/product_media/

SYS LOG_FILE_DIR C:/UserDefine/oracle/ora92/demo/schema/log/

SYS DATA_FILE_DIR C:/UserDefine/oracle/ora92/demo/schema/sales_history/

SYS SOURCE_DIR C:/UserDefine/oracle/oradata/source

SYS TARGET_DIR C:/UserDefine/oracle/oradata/target

SYS LOG_DIR C:/UserDefine/oracle/oradata/log

6 rows selected

在源目录中创建一个Student.txt的文件

Create a Text File, C:/UserDefine/oracle/oradata/source/student.txt, Content is below

wbq English 70

wbq Maths 75

wbq History 90

定义外部表并读取相应的数据

SQL> CREATE TABLE Student

2 (

3 StudName VARCHAR2(20),

4 ExamName VARCHAR2(20),

5 Score INTEGER

6 )

7 ORGANIZATION EXTERNAL

8 (

9 TYPE ORACLE_LOADER

10 DEFAULT DIRECTORY source_dir

11 ACCESS PARAMETERS

12 (

13 records delimited by newline

14 badfile log_dir:'bad_student.dat'

15 logfile log_dir:'student.log'

16 fields terminated by ','

17 missing field values are null

18 (

19 StudName, ExamName, Score )

20 )

21 LOCATION ('student.txt')

22 )

23 REJECT LIMIT UNLIMITED;

Table created

SQL> select * from student;

STUDNAME EXAMNAME SCORE

-------------------- -------------------- ---------------------------------------

wbq English 70

wbq Maths 75

wbq History 90

SQL>

ITPub博客

Oracle学习系列—数据仓库部分—目录管理和外部表

最新文章