ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle学习系列—数据仓库部分—目录管理和外部表

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

原创 Linux操作系统 作者:bq_wang 时间:2008-02-13 17:26:39 0 删除 编辑

目录和外部表是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博客 ” ,链接:http://blog.itpub.net/6517/viewspace-145530/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    412
  • 访问量
    1106877