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

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

[@more@]

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

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/8128313/viewspace-913928/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8128313/viewspace-913928/